《POWERBI数据处理和分析》课件 第4章 POWER QUERY中M语言基础.pdf

第4章POWER QUERY中M语言基础章节内容 4.1 M语言概述 4.2 M语言语法的基本结构 4.2.1 M语言程序基本结构 4.2.2 M语言词法 4.2.3 M语言数据类型 4.2.4.M语言运算符 4.2.5 M语言表达式 4.2.6 M语言程序控制结构 427 M语言函数 4.3M语言的库函数介绍24.1 M语言概述 M语言是微软在EXCEL和POWER Bl的POWER QUERY组件内嵌的公 式语言，可以将来自于各种数据源中数据通过过滤、组合、计算、转换等方式自动化的提取整理表格形式。通过POWER BI的菜单和按钮可以完成数据获取和处理操作，这些 操作的每一步都对应一条M语言的语句。对于通过菜单和按钮难以完成的，面向复杂数据结构的数据源，以及难度较高的数据处理过程的任务，可以通过使用M语言编写 规模不大的程序来实现。34.2.1 M语言程序基本结构 Power Query中一个查询对应一个M语言程序M语言程序的基本结构是一个let.in.表达式 let和in之间是若干条使用逗号作为分隔符的赋值表达式语句，每 一个赋值表达式都代表了从某个数据源获取数据，或者对已经保 存为某种类型的内存中数据进行转换和处理的过程赋值表达式的书写次序表示了数据处理执行的次序 in后面的表达式表示最终这个查询程序保存的数据对象44.2.1 M语言程序基本结构M语言程序实例letsource=#table(“ID，UNAME，”BIRTHDAY，SALARY，”BONUS“,(lOOOl,“李明”,#date(199573,1),10000,3000,10002,ffl t,#date(1998/8,8),8000,2000),income=Table.AddColumnfsource,INCOME,each SALARY+BONUS,typenumber)inincome54.2.1 M语言程序基本结构可以通过点击POWER BI查询编辑器窗口中“视图”选项卡的“高 级编辑器”按钮来查看和编辑查询所对应的M语言源程序。文件主页 转换 添加列 视图 工具 帮助10询苴 _背谢编辑栏 等宽字体 列分发5显示空白 列配置文件-1旬匡 转到列质h 列始终允许高级编查询依 辑器 赖项布局数据预览列 参数 高级 依赖项查询5巧表一直询1 由ffiW2 一查询3 E查询4j高级编辑器查询1显示选项二.letsource=#table(ID,NAME”BIRTHDAY”SALARY”BONUS”),(10001,“李明”,#date(1995,3,1),10000,3000),(10002,MFH E,#date(1998,8,8),8000,2000),income=Table.AddColumn(source,INCOME,each SALARYBONUS,type number)inincome未检测到语法错误.X和；取消|64.2.2 M语言词法1.M语言词法简述 M语言是基于UNICODE字符集的，并且大小写字母敏感的语言，因此 Sourc e和sourc e是两个不同的标识符名。M语言程序由空白字符、注释和基本词法元素的构成，而基本词法元素 又包括代表各种数据对象和函数的标识符名、有特殊含义的关键字、常 量、运算符、分隔符等。2.空白字符空白字符包括空格、制表符和回车换行符等。74.2.2 M语言词法 3.注释 M造言的注释。和“/*let sl=1.23 in siL 下面是一个单行注释的例子。let sl=1.23insl 这是一个简单的M语言程序 下面是一个多行注释的例子。/这是一个简单的M语言程序。*最终生成的数据是一个数值型的数据，它的值是1.23。84.2.2 M语言词法 4.标识符 M语言中的标识符有两种，一种是规则标识符，另一种是引用标识符。规则标识符是由字母或下划线开头，可以由字母、下划线、数字字符、点字符等构成的字符庠列，椀如sourc e、_sourc e si、s.l等都是合法 的M语言规则标识符。引用标识符以“#开头，后跟使用双引号作为定界符括起来的可以由 任意UNICODE字符所构成的字符序列，例如#源”、#2019 Sales等者B 是合法的M语言引用标识符。4.2.2 M语言词法 5.关键字关键字就是被M语言赋予特殊含义，不能用来作为自定义对象名 字的标识符，主要用于表示M语言中的数据类型、数据类型构造 函数、运算符、控制结构等。and as each else error false if in is let meta not otherwise or section shared then true try type#binary#date#datetime#datetimezone#duration#infinity#nan/sections#shared Stable#time104.2.3 M语言数据类型学习M语言的数据类型主要是希望为学习者带来以下好处：对于获取的数据在POWER QUERY中的表示有进一步深刻的理解，从而能 够建立所看到的数据可视化形式与其内部数据类型之间的联系；现实世界中的数据往往形式比较复杂，对数据类型的理解可以帮助通过数类型之间的转换将数据转换为有利于进一步处理和最终进行分析的的运算，从而对于数据的数婚类型的理解可以让学习者明白可以在这些数据之上进行 的运算，从而有利于对数据处理思路的启发；有利于学习者理解M语言库函数中参数和返回值类型的含义，从而快速 的学习和掌握自身数据处理相关的库函数；对记录、列表和表格等复杂数据类型的掌握，有利于从细节理解M语言 中在无显式循环结构的条件下如何实现对数据集合的处理的。114.2.3 M语言数据类型 M语言中的数据类型可以分为原子类数据类型、集合类数据类型和特 殊数据类型三大类。L原子类数据类型原子数据类型主要用于描述等待处理和分析的数据集中的一个数据值,通常用于表示现实世界中一个对象的一个具体属性的值，例如某人的姓名、年龄、出生日期、性别等信息。我们对于原子类数据类型的、握和理解有助于帮助我们掌握对于数据集合中具体某个属性值的处理。旱 M语言中的原子类数据类型包括逻辑类型、数值类型、时间类型、日 期类型、日期时间类型、时区类型、持续时间类型、文本类型和二进 制类型等。124.2.3 M语言数据类型一一原子类数据类型(1)逻辑类型 M语言中逻辑类型的类型名是logic al,用于表达逻辑运算的结果，逻辑类型 的值只有true和false两种，分别表示真和假。(2)数值类型 M语言中数值类型的类型名是number,用于表达数值类型的数据，包括整数 和实数Number类型值解释123整型数据，十进制形式-1.23实型数据1.23e2指数形式的实型数据，表示1.23X102Oxff整形数据，十六进制形式#infinity正无穷-#infinity负无穷#nanNot-a-Number,不是数值，因为除以0等错误运算 而产生134.2.3 M语言数据类型一一原子类数据类型(3)时间类型 M语言中时间类型的类型名是time,用于表达由小时、分钟和秒3个分量综合而成的以24小 时计的一天中某个时刻的数据。Time类型数据可以由#节1710(11011川而心生8向)1/1语言构造 函数来生成，例如#time(15,30,00),表示15：30：00o Time本质上是记录的自牟夜零时以来 以100纳秒为一个滴答单位流逝的滴答计数。(4)日期类型 M语言中日期类型的类型名是date,用于表达由年、月和日3个分量综合而成的具体某天的 数据。date类型数据可以由#(1316(丫631；1710廿%1日丫)1/1语言构造函数来生成，例如#date(2020,l,25),表示2020年1月25日。date本质上是记录的是自公元1年1月1日以来流逝 的天数，最大值是公元9999年12月31日。144.2.3 M语言数据类型一一原子类数据类型(5)日期时间类型 M语言中日期类型的类型名是datetime,用于表达由年、月、日、时、分、秒6个分量综合而成的以当 前时区为准的某个具体时刻的数据。datetime类型数据可以由#datetime(yeal；month,day,hour,minute,second)M语言构造函数来生成，例如#date(2020,L25,15,30,00),表示2020-1-25 15：30：00 o(6)时区日期时间类型 M语言中时区日期类型的类型名是datetimezone,用于表达由年、月、日、时、分、秒、小时偏移量 和秒偏移量8个分量综合而成的在某个时区中的具体时刻的数据，具体的时区由离世界标准时间UTC时 间的小时和秒偏移量表达。datetimezone类型数据可以由#datetimezone(year,month,day,hour,minute,second,offset-hours,offset-minutes)M 语言构造 函数来生成，例如#date(2020,1,25,15,30,00,8,0),表示齿UTC时差8小时的2020-1-25 15：30：00,实际上即北京时间 2020-1-25 15：30：00o154.2.3 M语言数据类型一一原子类数据类型(7)持续时间数据类型 M语言中时长类型的类型名是duration,用于表示由日、时、分、秒4个分量综合而成的在两个具体时间之间时长 的数据。Duration类型数据可以由井孔同心口何3丫,hour,minute,second)M语言构造函数来生成，每个分量都可正可负，如果时正值表示往后流逝的时间，负值表示往前回溯的时间。例如#duration(0,l,30,0)表示往后流逝的1小时30分钟 时长，#duration卜1,0。0)表示往前回溯的1天时长。(8)文本数据类型 M语言中时长类型的类型名是text,用于表示由Unicode字符构成文本数据。text类型数据使用双引号作为定界符，例如“Nanjing university”。(9)二进制类型数据 M语言中二进制类型数据的类型名是binary,用于表示一个二进制字节序列构成的数据，这些数据的含义由具体的应用解释，例如可以是图像的像素信息，或者音频的数据等。Binary类型的数据可以由粕inary()构造函数来生成,例如#bi nary(Oxa 1,Oxff,0 x8c,0 xe9)。164.2.3 M语言数据类型 2.集合类数据类型集合数据类型用于表示若干个数据构成的集合，这里的数据可以 是前述的原子类型的数据，也可以是集合类型的数据。M语言中的集合类数据类型包括列表类型、记录类型和表格类型空寸O174.2.3 M语言数据类型一一集合类数据类型（1）列表数据类型 列表类型用于表示有限个数据元素的集合，M语言中结构类型的类型名为list。M语言语法上规定列表是由一对花括号作为定界符括起来的若干个数据元素，数据元素之间使用逗号作为分隔符。列表中的元素的值的类型可以相同，也可以不同；可以是原子类型的值，例 如数值型、文本型等，也可以是集合类型的值，即列表的元素又可以是一个 列表，_ _列表示例解释(1,2,3数值型列表red,blue,green文本型列表l/redtrue由不同类型数据元素构成的列表L2,3,red,blue,green列表的列表184.2.3 M语言数据类型一一集合类数据类型(2)记录数据类型 列表类型用于表示有名字的有限个数据元素的集合，M语言中结构类型的类 型名为rec ord。M语言语法上规定记录是由一对方括号作为定界符括起来的若干个名称值对 数据元素，名称值对之间使用逗号作为分隔符；名称是用户自定义标识符，表示记录元素的名字，通过“二将值赋予相应的 名字；在定义记录元素的过程中，后续元素的值可以通过前面元素的值计算得到。194.2.3 M语言数据类型一一集合类数据类型(3)表格数据类型 表格数据类型用于表示由行和列构成的数据集合，M语言中的表 格类型名是table。M语言语法规定表格数据由枇3|3但构造函数加上由表格结构类型定 射喳赞数据应成的列表砚宝，琴格的结构类型定义主要定义表 格的结构，即每列的列名和类型。眼据烈电类型的确定方式，可以将表格对象分成隐式字段表和显 式子段表。204.2.3 M语言数据类型一一集合类数据类型隐式字段表的定义中表格的结构部分由字段名构成的列表说明,而每个字段的类型根据每列的数据值由POWER BI自动确定。letsource=#table(,ID,；,NAME,，BIRTHDAY,；,SALARY,；,BONUS,/(“10001,”李明，#date(1995,3,l),10000,3000,10002,ffl#date(1998,8,8)z8000/2000),income=Table.AddColumn(source,INCOME,each SALARY+BONUS,typenurr)ber)inincome214.2.3 M语言数据类型一一集合类数据类型显式字段表明确给出每一列的列名和数据类型。二 ABc id3J Abc name臼函 BIRTHDAY|三 L2 SALARYW 12 BONUS1 10001李明1995/3/11000030002 10002田E1998/8/880002000向高级编辑器 X查询3显示选项二letsource=#table(type table ID=textNAME=text,BIRTHDAY=date,SALARY=numberBONUS=number(10001,李明，#date(1995,3,1),10000,3000),10002,“田飞，#date(1998,8,8),8000,2000 sourceV未检测到语法错误。3取消224.2.3 M语言数据类型一一特殊的值和特殊的数据类型特殊的值和特殊数据类型主要是M语言为了方便应对数据的处理 的特殊it况和方便进行数据处理而设置的。null表示空值。特殊的数据类型包括任意数据类型和可空数据类型等。任意类型：M语言引入任意类型any的目的主要是一种抽象方式，任何值 都可以和any类型兼容，主要用于表示函数的参数的数据类型。可空数据类型：M语言引入任意类型nullable的主要目的是将任何一种数 据类型扩展为可以接受空值null的数据类型，即假设T是一种数据类型，贝UnusableT表示即可以接受T类型数据，也可以为null值的数据类型。234.2.4.M语言运算符M语言提供了丰富的运算符来处理各种类型的数据,与数据访问有关的主运算符 算术运算符 关系运算符 逻辑运算符 类型判断运算符244.2.4.M语言运算符 i.元素访问运算符运算符的语法形式如xy所示，其中x是列表或表格数据，y是基 于0为起始值的下标，用于提取列表或表格中指定下标的元素，如果配合“?可以实现容错。下面是通过下标访问列表元素的示例。214八/3函7为1返回列表中下标为1的元素”wang；zhang,wang,li3导致错误,因为不存在下标为3的元素；zhang,wang,li3?返回n ull,通过?实现容错,当不存在指定的元素 时，返回空值null。254.2.4.M语言运算符 2.字段访问运算符口 运算符口的第一种语法形式如xy所示，其中x是一个记录，y是一个属 性名，此时根据属性名y从记录x中选择对应的值。例如N AM E=zha ng,SALARY=8000 SALARY得至U8000。运算符口的第二种语法形式如xylUy2,所示，其中x是一个记录或表 格，yl、y2等是属性名，此时根据给定的属性名进行投影，JA记录和 表格x中返回具宥更少属性南一个记某或装箱，下面是一些不4例j。NAME=zhang,SALARY=8000SALARY得到SALARY=8000；NAME=zhang,AGE=25,SALARY=8000NAMEUSALARY得至 U:NAME=zhang,SALARY=8000；264.2.4.M语言运算符 3.算术运算符对于数值类型的数据应用算术运算符和其他语言类似算术运算符也可以应用于日期类型、时间类型、日期时间类型和持续时间类型的数两个持续时间类型可以进行加法和减法，结果得到一个持续时间类型数据，例如#duration(0450)+#duration(0,1,10,0)得到#duration(0225,0)藕飕类褊时间类型、日期时间类型数据可以和一个持续时间类型数据进行加#date(202(U,l)+#duration(5。得至 U#datetime(202(U,6。#time(18Q0)+#duration 2。)得至 U#time(20Q0)274.2.4.M语言运算符 3.算术运算符*躺楠1%时豳举型、日期时间类型数据相减，可以得到一个持#date(2O2O,L31)-#date(2Ol(U,2O)得至 U#duration(llQQO)田类矍落舞琼去例如#duration(8M0/0)/#duration(4,0。)得至!)2#duration(lMO/12 得到#duration(02。,。)284.2.4.M语言运算符 4.连接和合并运算符&连接和合并运算符&可以用于合并两个相同类型的数据，支持的 羹箫类型包括文本类型、日期类型、列表类型、记录类型和表格 下面是一些示例。,Nanjing&university”得至Nanjing university77；1,2&3得到1,2,3；NAME=wzhangw&SALARY=8000得到：NAME=,/zhang,；SALARY=8000；#date(2020,l,8)&#time(08,30,00)得至U#datetime(2020,l,8,08,30,00)。294.2.4.M语言运算符 5.类型运算符is和as 类型兼容性运算符is的语法形式如xis y,如果值x的类型和类型y兼容,血表色式求值结集为true,否则为false。类型断言运算符as的语法形式如x as y,如果值x的类型与类型y兼容，则求值结果为x自身，否则会触发一个错误。下面是一些示例。8000 is number求值结果为true 8000 is list求值结果为false 8000 as number求值结果为8000 8000 as list导致一个错误304.2.4.M语言运算符优先级运算符类别优先级运算符及相关表达式解释主运算符1i,i标识符名引用对象表达式(x)圆括号表达式xi查询xy元素访问f(-)函数调用表达式xy,列表构造name=value,.记录构造单目运算符2+x,-x单目加减（正负号）not x逻辑非元数据运算符3x meta y关联元数据双目算术乘除运算符4X*y,x/y双目乘法、双目除法双目加减运算符5x+y,x-y双目加和双目减法关系运算6xy,x=y小于、大于、小于等于、大于等于相等关系7x=y,xoy等于和不等于关系运算类型断言8x as y类型兼容性判断类型一致性9x is y逻辑与10 x and y逻辑与运算逻辑或运算11x or y逻辑或运算314.2.5 M语言表达式 M语言作为一个公式语言，其基本语法单位是表达式，每个表达 式计算结果会得到一个值。每个单独的值可以看作一个表达式 运算符及其操作数按照M语言语法构成一个表达式 用于表达控制结构的if构造也是表达式 函数定义和函数调用也是表达式 程序的总体结构let.in也是一个表达式。因此M语言的执行是通过按照给定的顺序逐步进行表达式求值实 现的。32426 M语言程序控制结构 条件分支控制 条件分支控制结构用于表达根据一个条件是否成立采取不同的处 理的情况。M语言中的的条件分支控制结构是通过if表达式实现的。if表达式的语法如下所示：,If条件then为真分支表达式else为假分支表达式 If表达式的求值规则是，如果“条件”为真，则if表达式的值是“为真分支表达式”求值的结果，否则的话，if表达式的值是“为假分支表达式”求值的结果。例如if 80005000 then 8000 else 5000的求值结果为800033426 M语言程序控制结构循环结构通常用于表达在循环条件为真的情况下重复进行循环体 计算，从而实现问题求解的方法。M语言没有直接的循环控制结构，通过集合类型数据相关的库函 数来实现对集合中每个数据元素的遍历，在遍历过程中还可以指 定对每个元素的处理函数。letsource=1.5,s2=List.Transform(source,(x)=x+10)ins2344.2.6 M语言程序控制结构 循环处理中的eac h和“语法糖的形式，eac h表示对每个元素都需要 进行的处理，而表示当前元素的值。例如，下面两个表达式的效果是等价的。s2=List.Transform(sourc e,(x)=x+10)s2=List.Transform(sourc e,eac h _+10)上面+10表示使用当前元素的值+10的值来替换当前元素的值，而eac h 表示这个处理被定义成为一个针对每个集合元素的函数。35427 M语言函数当一个数据处理功能需要多次使用时，可以将其定义为一个函数。魅嘉外电甯楚军圣信变过表达式实现的，在完成函数定义后，对函 M语言的函数定义表达式语法如下：（可选的形参列表）可选的函数返回类型=函数体参返 达 形中 表 则体 用，数 使 数函 然 参由 仍。要则 敢付 需，。爸 不话果标 数的结 函略的名一 个省终今 一 果最一值 果如到藤 如，得黑，型算曾 式类计窜 形；的何费 移据如需定 数数数 的函 3 壬来回了曹 物节返.，义第定函，工/e勒寸式樊叩 出堀达/给，刑前士表 奎米类个，现 舞寓一时实 责反是曼 需骏体能 罢爵数需 苴利 0函、望364.2.7 M语言函数 M语言函数定义案例letgetjist=(start as number,end as number)二start.end,result=get_list(l,100)inresult374.3 M语言的库函数介绍 M语言提供了700多个的库函数来帮助数据处理，覆盖了数据获取、各种不同数据类型的数据的处理、类型处理、错误处理等功能。专关M语言库函数的具体帮助，可以参见微软M语言的官方文档 主页：https:/doc s.mic rosoft.c om/en-us/powerquery-m/%Filter by titlePower Query M func tions overviewUnderstanding Power Query M func tions Ac c essing data func tions Binary func tions Combiner func tions Comparer func tions Date func tions DateTime func tions DateTimeZone func tions Duration func tions Error handling Expression func tions Func tion values Lines func tions List func tions Logic al func tions Number func tions Rec ord func tions Replac er func tions So litter func tions国 Download PDFFunc tions by c ategory Ac c essing data func tions Binary func tions Combiner func tions Comparer func tions Date func tions DateTime func tions DateTimeZone func tions Duration func tions Error handling Expression func tions Func tion values List func tions Lines func tions Logic al func tions Number func tions Rec ord func tions Replac er func tions Splitter func tions Table func tions Text func tions Time func tions Type func tions Uri func tions Value func tions384.3 M语言的库函数介绍学习M语言库函数的基础方法：理解和掌握M语言的数据类型，并理解这些数据类型如何用于表达现实 世界中的各种不同类型的数据；对数据处理任务有较为深入的理解，知道数据处理任务可以分解为很多 不同的常用的子功能，每个子功能通常可以由一个对应的M语言库函数 实现，这些子功能可以从用途或者处理的对象的数据类型进行分类，当 希望实现某个自己暂时还不会的子功能时，可以清楚的知道如何查询M 语言的官方文档，快速的定位到相应的库函数；能够看懂一个具体的M语言库函数的原型，理解每个参数的类型和作用,理解返回值的类型和作用，从而帮助理解对该函数的使用。394.3 M语言的库函数介绍 M语言库函数解释案例1 用于从一个文本最开始位置截取指定数量字符构成子串的text.start。函数 Text.Start(text as nullable text,c ount as number)as nullable text 从语法上说，函数名Text.Start说明这是处理文本数据的一个序函数，具 肴两个不可省略的参数；第一个参数中 as nullable text表土这是一个可 以为null值的text类型而数聪，第二个参数中 as number表示这是一个 number类型的数搪；放回值类型是“as nullable text7,表示迨回的是一 个text类型的文本数据，可以为null值。例如Text.Start(hello,world!”,5)返回“hello”。404.3 M语言的库函数介绍 M语言库函数解释案例2从一个列表中选取满足条件的元素构成子列表的List.Selec t。函数List.Selec t(list as list,selec tion as func tion)as list从语法上说，函数名List.Selec t说明这是处理列表数据的一个库函数，具 有两个不可省略的参数；第一个参数中 as list表示这是一个list类型的 数据，不能为null值，第二个参数中 as func tion表示函数类型的参数，例如List.Selec t(L234,5,eac h _ x3)将 实现同样的效果，返回12345这个列表中小于3的元素构成的子列表 1,2O41