M 语言的函数体系非常庞大,包含了大约 90个函数类别,总共涉及超过600个函数!(官方文档)
let源 = Excel.CurrentWorkbook(){[Name="表 1"]}[Content], 删除的副本 = Table.Distinct(源, {"品名"})in 删除的副本 //输出结果
步烧骤
描述
表示一个查询的开始
Let
创建输入一个查询表
变量名称,这个变通过两Tabl.Distinct删除上一步骤中品名]字段的重复项
删除的副本
示一个查询的结来,查询结束后,输出到([查询缩据器)中的结果使用删除的副本这个步的结果
a) 值 (number, text, date, time, datetime)b) 记录 (Record)c) 列表 (List)d) 表格 (Table)
删除的副本 = Table.Distinct(源, {"品名"})
说明
参数
删除的副本
变量名
函效类别
Table
函数名称
Distinct
输入一个查询表
源
指定一个列字段名称,可以同时选择多个字段
出品名"
语句必须小写
HasErrorFALSE
try表达式正术
Value2
try判断后产生一个记录Record
TRUE
Hasfnror
try麦达式信保
FnrorRecord
try...otherwise...
HasErrorFALSE
try麦达式正鞋,直接返回value
Value
加上otherwise的完整语句
HasErTorTRUE
try泰达式档溪,直接返回otherwise后的表达式
EntorRecord
可以进行嵌套一层层判断
以 /* 开头,以 */ 结尾。
比如交待度量值的业务背景,可以在前几行添加注释,
在DAX编辑框中,注释的文本自动以绿色显示。
单行注释又有两种方式,分别以 // 或 -- 开头,
这两种方式效果一样,你可以选中喜欢的注释方式,不过尽量做到风格统一。
单行注释可以放在代码后面,也可以另起一行注释,
以上几种方式效果完全一样,只要是在 // 或者 -- 之后的本行字符都不会被执行。
除了手动输入注释字符,还可以用快捷键来快速注释。
快捷键有两种方式:
1, Ctrl+/
将光标移到需要注释的行,按住Ctrl+/,即可注释,再按一次,为取消注释。
还可以选中多行,按住Ctrl+/,同时对对行注释,
2, Ctrl+K+C 和 Ctrl+K+U
Ctrl+K+C 是添加注释,重复快捷键会继续套一层注释
Ctrl+K+U 是取消注释,重复快捷键会连续取消注释
平时使用时记住一种方式就行了,Ctrl+/ 简单实用。
替换值
在所选列中,将其中的某值用另一个值替换.
要查找的值
#(cr送()
替换为
高级选项
单元格匹配
使用特殊字符替换
插入特殊字符
Tab
回车
换行
回车和换行
(1)组合运算符(适用于文本、列表、记录、表格等连接)
="四川" & "成都" & "大熊猫基地" 输出 "四川成都大熊猫基地"={1,2,3} & {4,5} & {6,7,8,9} 输出 {1,2,3,4,5,6,7,8,9}=[职务="园长"] & [年龄=36] 输出 [职务="园长" , 年龄=36]=#table({"姓名"},{{"张飞"}})&#table({"姓名"},{{"李奎"}}) //输出 #table({"姓名"},{{"张飞"},{"李奎"}})
(2)比较运算符(适用于逻辑值数字、时间、日期、日期时间时区、文本、二进制)
含义
运算符
大于
大于或等于
小于
小于或等于
等于
不等于
(3)逻辑运算符
含义
运算符
或
or
与
and
非
(4)算术运算符
含义
运算符
加平
除
(5)表级运算符
含义
运算符
记录单
列表
函数 说明Number.From 从value 转换成数值Number.FromText 从文本数值转换成数值Number.ToText 从数值转换成指定格式的文本数值Text.From 返回文本表示的数值、时间、日期、二进制值等Logical.FromTex 从文本逻辑值转换成逻辑值Logical.ToText 从逻辑值转换成文本逻辑值Logical.From 从 value 转换成逻辑值Date.FromText 从文本日期转换成日期Date.From 从 value 转换成日期Date.ToText 返回指定文本格式的日期Date.ToRecord 返回年、月、日记录单
函数
函数
函数
函数
Record
Hdts
Table
Access
Null
Any
Compression
Time
Culture
BinaryFormat
SapBusinessobjects
Error
DateTime
ExtraValues
Int8
Variable
Odbc
Character
Function
Lines
MissingField
PostgreSoL
Splitter
Double
Replacer
Int16
Teradata
ActiveDirectory
AZureStorage
Number
Type
Csy
BinaryOccurrence
SharePoint
Excel
Currency
JoinAlgorithm
DateTimeZone
Web
OleDb
GroupKind
Combiner
List
MySOL
Precision
Sql
Duration
AdoDotNet
RoundingMode
Int32
Text
Uri
CsvStyle
Binary
OData
Exchange
Single
Byte
DB2
Day
JoinKind
File
Xml
HdInsight
Oracle
Logical
Comparer
None
QuoteStyle
Sybase
Embedded
Salesforce
TextEncoding
AnalysisServices
Int64
Value
BinaryEncoding
Cube
Occurrence
Date
ByteOrder
Soda
Folder
Decimal
Json
Order
Marketplace
roundingMode as nullable RoundingMode.Type) as nullable number 为例来介绍函数
即表,这个好理解,有行有列即为table。
在excel中,要指定一个单元格我们用比如A5,而在PQ中则略有不同。每一个table都有字段名,即使你没有给字段命名,也会有默认的如"Column1""Column2"这样的命名;也有索引号,即使你没有添加索引列,也可以根据索引号找到对应的唯一行。比如源[产品]{4}
指定在源这张表的"产品"列的第5行的唯一位置,注意索引是从0开始。
大多数情况下我们的table都是从外部导入,而如果我们需要在PQ内部构建一张表,格式为=#table({"产品","数量"},{{"a",10},{"b",20}})
,注意{}的数量和位置。
第一个{}内为字段名,我们有两个字段分别命名为"产品"和"数量"。第二个{}内为每一行,共有两行所以中间用一个逗号隔开,而每一行则再用一对{}包起来,表示每个字段对应的值。
注意,第一个{}内共有2个字段名,那么第二个{}中的每一行的顺序和数量也必须与之对应。不能是=#table({"产品","数量"},{{10,"a"},{"b",20}})
也不能是=#table({"产品","数量"},{{"a",10},{"b"}})
即记录,简单点理解,record就是相当于table中的一行,凡是带[]的都和record有关。
我们对上面构建的table深化出第1行,可以看到record是长成这样的:
一个字段名,一个值,一一对应,有点像别的语言中的dict。
如果要构建一个record,格式为=[产品="a",数量=10]
,注意record中的字段名没有引号,而构建table中的字段名是要加引号的。
即列表,简单点理解,list就是相当于table中的不带字段名的一列,凡是带{}的都和list有关。
我们对上面构建的table深化出"产品"字段,可以看到list长这样:
如果要构建一个list,格式为={"a","b"}
。和table一样,list是有序的,根据索引号找到的值是唯一的,比如={"a","b"}{1}
,结果为"b"。
在M中,可以用..
来构建一个连续列表,..
前后分别表示list的起和止,且必须起<=止,比如{1..100}
,表示一个number类型1-100的list。但是不能写成{100..1}
,如果要构建逆序list,可以=List.Reverse({1..100})
。
除了number型,我们也可以构建text型的list,比如{"0".."9"}
,但是不能写成{"1".."100"}
,因为这种写法只能使用单字节的字符串,而"100"显然不符合要求,如果要得到一个text型的1-100,可以先构建number型,然后使用转换函数将其转成text型=List.Transform({1..100},Text.From)
。
常见的文本list除了{"0".."9"}代表所有数字,还有{"a".."z"}表示所有小写英文字母,{"A".."Z"}表示所有大写英文字母,{"A".."z"}表示所有英文字母和部分标点,{"一".."龥"}表示绝大多数汉字等等。这个是根据Unicode编码来的,中文的范围大概在19968-40891之间,你可以用=List.Transform({1..50000},Character.FromNumber)
来获取所有编码列表。
上面已经提到过,深化就是对table record list提取其中的部分内容。[] 定位列、{} 定位行。
对record深化只能用[](一条记录只能定位列)
对list深化只能用{}(一个列表只能定位行)
对table深化可以同时使用[]和{},但是对table使用[]后得到的是一个list,而使用{}得到的是一个record,这个一定要搞清楚不要混淆。比如表{0},表示表中第一行的所有列,显然是个record。同时获取行还有一个表示方式为表{[产品="a"]}
,但必须保证该字段下的命名唯一,也就是产品这一列中只能有一个"a"。
如果按照在excel中的概念,对table同时使用[]和{}深化,就可以精确定位到一个位置并获取到具体的值,已经是最小单位不可再分割了。但是在PQ中,list和record中每一个元素的类型可以各不相同,而且类型可以是any,什么概念呢?来看这样一个list:
= {1,"a",#date(2017,6,28),false,null,Text.From,{1},[产品=1],#table({"产品","数量"},{{"a",10},{"b",20}})}
我们发现这个list里面有各种不同类型的数据,如果用{8}对其深化,又会得到一个table,又可以用{}和[]继续深化下去。这就是嵌套,理论上可以无限套下去,record也同理。
list record和table三者之间可以互相转换,有专门的转换函数,比如Table.ToColumns
,Record.FromList
等等,这个以后会讲到。搞清楚三者之间的区别和联系,是学好M语言的重中之重。
在excel中我们可以用&将文本连接合并,在PQ中&不仅能连接文本,对于三大容器同样适用:
如={1,2}&{3,4}
得到{1,2,3,4},作用相当于List.Combine
;=[A=1,B=2]&[B=3,C=4]
得到[A=1,B=3,C=4],相当于Record.Combine
,因为一个字段只能对应一个值,所以不同字段会追加,相同字段会覆盖只保留最后一个值;=表1&表2
则相当于Table.Combine
,也就是功能界面的追加查询。
上面说过三大容器中的类型可以为any,回忆一下在高级编辑器中的语法结构,比如:
将三个变量赋给三个值,最后返回c的结果。而record的结构也同样为一个字段对应一个值,且各元素之间可以相互引用计算,所以我们完全可以将三个步骤写成一个步骤放进record里,然后深化出最后一个步骤,写成:
理论上所有分步的代码全部可以用record写成一步,当然在这里没有必要这么做,但是在很多情况下,使用record构建中间步骤然后重复调用,可以减少公式重复次数,使代码更加简洁。举个简单的例子:
我现在要每个名称下拆分后的第一个和第二个的合并,那么可以写成= Table.AddColumn(源, "结果", each [a=Text.Split([名称],"/"),b=a{0}&a{1}][b])
而如果不这么写,Text.Split
就需要写两次,是不是简洁了许多?
#shared
关键字可以加载M中所有内置函数,可以用来查看内置函数的定义及使用方法。
PowerBI—M语言函数语法手册.pdf (3.5 MB)
Excel.Workbook(从指定路径导入表格)
从Excel工作簿返回工作表的记录。
Excel.Workbook( workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table
语法:
若导入的文件为*.csv或*.txt,请参照另一篇《Csv.Document》。
第一参数为binary,通常是先使用File.Contents
根据指定路径获取Excel文件,返回类型为binary,然后再用Excel.Workbook
将binary解析出来,比如= Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"), null, true)
。
第二参数为是否使用标题,类型为布尔值。true表示使用第一行作为标题,不填或null或false都表示不使用。
第三参数字面意思为延迟类型,同样为布尔值。据说在合并文件夹的时候填true效率更高,填false在每合并一次后会有延迟。但是经过本人测试填不填并没有什么区别,所以一般省略不填,如果大家测试后有发现区别欢迎反馈。
如果是导入单个文件,我们一般点击数据-新建查询-从文件-从工作簿即可,能界面操作的也没必要去手写公式。但是系统自动生成的公式第二参数默认为null,第三参数为ture,然后会再加一个提升标题的步骤,看起来很不科学。第三参数又没什么用,我们完全可以把 ,null 删掉,让true变成第二参数使用标题,然后把"提升的标题"删掉。"导航"这个步骤是从表记录中深化出[Data]列中数据所在区域,如果只需要其中的一个sheet那可以直接使用[Data]{0}的形式深化出来,写成= Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"),true){0}[Data]
。
如果要的是所有sheet的合并数据那么就使用Table.Combine
进行合并,然后"导航"就可以删掉了,写成= Table.Combine(Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"),true)[Data])
。
最终只是改了几个字符,就让原来三个步骤做的事现在一个步骤就能完成,看上去清爽很多。
我们使用这个函数最多的场景是合并文件夹下的所有excel文件,如果我们导入文件夹后直接点击合并-合并并编辑,会看到如下的效果:
一下多出这么多的查询和步骤,不知道你感觉如何,反正身为重度强迫症患者的我表示接受不了。那怎么办?
可能其他大多数教程都会教你添加一列=Excel.Workbook([Content])
,然后展开再删除列。相比自动生成的公式已经简洁很多了,但是既然有强迫症,就必须把强迫症发挥到极致。
首先导入文件夹,不要点合并直接点编辑,看到的应该是如下界面:
思路同解析单个文件一样,只不过现在是要对[Content]字段下的多个binary同时转换,于是要用到List.Transform
,最后再将所有表格合并起来。
= Table.Combine(List.Transform(Folder.Files("C:\Users\rages\Desktop\excel")[Content],each Table.Combine(Excel.Workbook(_,true)[Data])))
看到这你会发现,如果懂一点M语言,原本系统自动生成N个查询N个步骤实现的效果,现在也许一行代码就能搞定。
有些时候可能需要保留文件名,比如说文件以日期命名而表中没有日期。这时候就不能用上面极简的写法了,只能老老实实的先用File.Contents
获取路径下所有文件,然后把其他列删掉只保留[Name]和[Content],再添加列使用= Table.Combine(Excel.Workbook([Content],true)[Data])
,最后再展开。
Csv.Document (从文本文件导入表格)
返回表形式的CSV文档内容。columns:如果指定了一个记录,且delimiter、extraValues、encoding为Null,则所有参数Delimiter、Columns、Encoding、CsvStyle和QuoteStyle都将从该记录中获取。delimiter可以采用单个字符或列表;如果未指定或为Null,则使用逗号。有关可选extraValues的支持值,请参阅ExtraValues.Type。encoding指定文本编码类型。
Csv.Document( source as any, optional columns as any, optional delimiter as any, optional extraValues as nullable number, optional encoding as nullable number) as table
若导入的文件为*.xlsx,*.xls等,请参照另一篇《Excel.Workbook》。
本函数不仅适用于csv,同样适用于txt,csv和txt本质上都是一样的,同属文本文件。
第一参数为必填参数,类型为binary,通常是先使用File.Contents将csv文件解析为binary,然后再用Csv.Document
将binary解析出来,比如= Csv.Document(File.Contents("C:\Users\rages\Desktop\csv\test1.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
第2-5参数为可选参数,其中第二参数为列数,一般情况下不填即返回所有列,但是有些情况如不指定列数可能会只返回一两列,所以先不填试试看,如显示不完整再数下源数据共有几列,填到第二参数。
第三参数为分隔符,常见的分隔符有逗号、空格、制表符等等,同样可以先不填看看结果,不填或null默认使用逗号,如果不正确再加上分隔符。
第四参数为多出列的处理,有3个枚举常量:ExtraValues.List
,ExtraValues.Error
,ExtraValues.Ignore
,分别可以用0,1,2代替,意思就是如果源数据的列数超过指定列数,是返回列表还是报错还是忽略。比如源数据有很多列,但是我写= Csv.Document(File.Contents("C:\Users\rages\Desktop\csv\test1.csv"),1,",",0)
第二参数只指定了1列,就会返回一个列表,超出的列就会全部放在这个list中。而如果填1就会因为超出列数而报错,填2就会忽略错误。这个参数知道下有它的存在就好了,实际上并没有什么卵用,有兴趣的话可以自己测试下。
第五参数为编码,同样可以先不填看结果,如果出现乱码再更改此参数,中文编码一般为936。
此函数特殊的地方是,当第三四五参数不填时,第二参数可以为一个recod,格式为[Columns= ,Encoding= ],如上面介绍的第一参数中的写法,大多数情况我们都会采用这种写法。
别看参数这么多,又是这种写法又是那种写法的,实际上也没那么复杂,完全可以通过菜单栏的可视化界面操作自动生成公式,然后自己再改改就好了。自动生成的公式一般已经没什么问题了,只是最后一个QuoteStyle=QuoteStyle.None也基本没什么用,看不惯的可以干掉他,能接受的话就不用管。其他也没什么好说的,很简单。
用到该函数最多的还是在合并文件夹下的所有csv文件,在较旧的版本中直接点合并自动生成的公式尚能接受,但是在某个版本更新后,直接点击合并会多出示例文件等一大坨多余的查询和步骤,要逼死强迫症。
思路同Excel.Workbook
一样,深化出[Content]列,使用List.Transform
批量转换,最后再合并。但是该函数中并没有提升标题的参数,所以还需要在合并之前加一个提升标题的操作。
= Table.Combine(List.Transform(Folder.Files("C:\Users\rages\Desktop\csv")[Content],each Table.PromoteHeaders(Csv.Document(_,[Column=2,Encoding=936]))))
联系客服