POWERBI数据处理和分析-使用POWER QUERY之M语言进行自动化数据处理

使用POWERQUERY之M语言进行数据处理1章节内容5.1数据类型之间互相转换基础5.2表格数据的行列处理转换基础5.3常见数据源数据的获取5.4文本数据的处理5.5数值数据的处理5.6日期时间数据的处理5.7JSON数据的处理5.8数据处理综合案例——半结构化EXCEL数据的处理5.9数据处理过程中M语言的灵活应用2第5章使用POWERQUERY之M语言进行数据处理M语言可以解决通过界面操作方式无法解决的问题（1）使用M语言编程，可以清晰的理解数据处理过程中每一步的数据表示形式，以及各个步骤之间数据表示形式是如何转换的，因此可以顺利的设计数据处理过程中从源数据形式到最终目的数据形式的转换途径；（2）本来人力无法胜任的很多需要重复进行的繁琐工作，可以由计算机自动化的来进行求解。35.1.1原子类型数据之间的类型转换1.文本和数值数据之间的转换可以使用Number.ToText()函数将数值数据转换为文本数据，该函数的语法形式如下：Number.ToText(numberasnullablenumber,optionalformatasnullabletext,optionalcultureasnullabletext)asnullabletext例：Number.ToText(123)得到文本“123”，而Number.ToText(123,”e”)得到文本“"123.000000e+000"”。可以使用Number.FromText()函数将文本数据转换为数值数据，该函数的语法形式如下：Number.FromText(textasnullabletext,optionalcultureasnullabletext)asnullablenumber例：Number.FromText("567")得到数值567。45.1.1原子类型数据之间的类型转换2.将其他类型数据转换为文本类型的数据可以使用Text.From()函数将number、date、time、datetime、datetimezone、logical、duration或binary类型的数据转换为文本类型，该函数的语法形式如下：Text.From(valueasany,optionalcultureasnullabletext)asnullabletext例：Text.From(1)将数值数据1转换为文本“1”，Text.From(true)将逻辑值true转换为文本“true”55.1.1原子类型数据之间的类型转换3.将其他类型数据转换为数值类型的数据可以使用Number.From()函数将text、date、time、datetime、datetimezone、logical、duration类型的数据转换为文本类型，该函数的语法形式如下：Number.From(valueasany,optionalcultureasnullabletext)asnullablenumber例：Number.From(#date(2019,10,28))得到43766，Number.From(true)将逻辑值true转换为数值1。65.1.1原子类型数据之间的类型转换4.将文本类型数据转换为日期类型可以使用Date.FromText()函数将符合某种文化的日期格式的文本转换为日期类型的数据，该函数的语法形式如下：Date.FromText(textasnullabletext,optionalcultureasnullabletext)asnullabledate例：Date.FromText("2019-10-28")得到代表2019年10月28日的date类型数据。75.1.2表格和记录数据之间互相转换1.将记录数据转换为表格可以使用Record.ToTable()或Table.FromRecords()函数将记录数据转换为表格。Table.FromRecords()函数的语法形式如下：Table.FromRecords(recordsaslist,optionalcolumnsasany,optionalmissingFieldasnullablenumber)astable85.1.2表格和记录数据之间互相转换【例5-1

】通过手工构造记录数据，转换为表格，效果如图所示。let//通过手工构造的列表创建一个表格保存到t1中

t1=Table.FromRecords({[Name="Name",Value="Zhou"],[Name="Age",Value=25],[Name="Country",Value="CHINA"]}),res=t1inres95.1.2表格和记录数据之间互相转换Record.ToTable()函数的语法形式如下：Record.ToTable(recordasrecord)astable该函数将一个记录对象转换为表格，表格只有两列，分别代表名称和值，而记录对象中的每个名称-值对会作为转换后的表格中的一行。105.1.2表格和记录数据之间互相转换2.表格数据转换为记录可以使用Table.ToRecords()和Record.FromTable()函数完成将表格数据转换为记录的任务。Table.ToRecords()函数的语法形式如下：Table.ToRecords(tableastable)aslist该函数将作为参数的表格转换为一个记录的列表返回，表格的列名和每一行的值分别构成记录列表中每个记录的名称-值对。Record.FromTable()函数的语法形式如下：Record.FromTable(tableastable)asrecord115.1.3表格和列表数据之间互相转换1.将列表数据转换为表格可以使用Table.FromRows()函数按照行方向构造表格，该函数语法形式为：Table.FromRows(rowsaslist,optionalcolumnsasany)astable参数rows是代表每行数据的列表，一般而言该列表的每个元素也是一个列表，代表一行数据，所以每个元素列表的长度应该是一样的；可选的参数columns用来给定每一列的名称，因此是一个文本列表，返回值是得到的表格125.1.3表格和列表数据之间互相转换【例5-2】假设原有代表表格数据的列表的列表{{“18101”,“张强”,“男”},{“18102”,“李云”,“女”},{“18103”,“赵飞”,“男”}}，则使用如下代码替换【例5-1】中的t1定义可以得到如图所示的表格。t1=Table.FromRows({{"18101","张强","男"},{"18102","李云","女"},{"18103","赵飞","男"}},{"学号","姓名","性别"})135.1.3表格和列表数据之间互相转换可以使用Table.FromColumns()函数按照列方向构造表格，该函数语法形式为：Table.FromColumns(listsaslist,optionalcolumnsasany)astable参数lists是代表每列数据的列表的列表，每个子列表代表一列，通常情况下每个子列表的长度应该一致，否则会导致有些表格单元数据为空值；可选的参数columns用来给定每一列的名称，因此是一个文本列表。返回值是得到的表格。145.1.3表格和列表数据之间互相转换【例5-3】假设原有代表表格数据的列表的列表{{“18101”,“18102”,“18103”},{“张强”,“李云”,“赵飞”},{“男”,“女”,“男”}}，则使用如下t1定义代码可以得到如图所示的表格。t1=Table.FromColumns({{"18101","18102","18103"},{"张强","李云","赵飞"},{"男","女","男"}},{"学号","姓名","性别"})155.1.3表格和列表数据之间互相转换2.将表格数据转换为列表将表格数据转换为列表时，可以有选择按行方向或者列方向进行转换。可以使用Table.ToList()函数将每一行的数组按照某种合并方式组合后生成一个数据，然后将整个表格的所有行转换为一个列表，该函数语法形式为：Table.ToList(tableastable,optionalcombinerasnullablefunction)aslist参数table是等待转换的表格；可选的参数combiner表示合并每行数组所使用的合并函数。返回值是一个列表，列表的每个元素对应表格中一行数据使用合并函数合并后的值。165.1.3表格和列表数据之间互相转换【例5-4】如例5-2中所示的表格t1，将每行数据都转换为文本类型，使用文本合并函数以逗号为分隔符合并成一个文本，最终构成一个如图所示的文本列表。t2=Table.ToList(t1,Combiner.CombineTextByDelimiter(",")),175.1.3表格和列表数据之间互相转换可以使用Table.ToColumns()函数将表格按照列方向转换为列表，表格的每一列被转换为一个列表，而整个表格被转换为列表的列表。例如例5-2中所示的表格t1可以使用如下代码按照列方向转换，转换后效果如图所示。t2=Table.ToColumns(t1)185.2.1表格行列转换在很多场合下，需要进行表格的行列转换，例如进行矩阵计算或者对外部读入的数据进行整理时，可以通过Table.Transpose()函数完成表格的行列互换任务，该函数的语法形式为：Table.Transpose(tableastable,optionalcolumnsasany)astable参数table是希望处理的表格；返回值是行列转置以后的表格。195.2.1表格行列转换【例5-5】可以将例5-1中的表格使用Table.Transpose()函数完成行列转置，效果如图所示，相关代码如下：

let//通过手工构造的列表创建一个表格保存到t1中

t1=Table.FromRecords({[Name="Name",Value="Zhou"],[Name="Age",Value=25],[Name="Country",Value="CHINA"]}),//进行行列转置，结果保存到t2中

t2=Table.Transpose(t1),res=t2inres205.2.2提升表格数据第一行为列标题很多时候从外部导入PowerBI的数据构成表格后，第一行不是数据本身，而是代表列标题，此时可以通过Table.PromoteHeaders()函数将表格的第一行数据提升为列标题。该函数的语法形式为：Table.PromoteHeaders(tableastable,optionaloptionsasnullablerecord)astable参数table是等待处理的表格，通常要求第一行数据是文本或数值类型的，否则的话，需要给出第二个参数；可选的参数options是一个记录，用来说明是否转换第一行的所有标量值为列标题，以及转换时使用的文化背景格式；返回值是转换得到的表格。215.2.3表格中列的数据类型的转换可以使用Table.TransformColumnTypes()在需要是将表格中某列数据的类型转换为期望的类型，当然前提要求是这种转换是可行的。该函数的语法形式是：Table.TransformColumnTypes(tableastable,typeTransformationsaslist,optionalcultureasnullabletext)astable参数table是等待处理的表格；参数ransformations是一个用来说明需要转换类型的列及相应的数据类型的列表；返回值是转换列类型的表格。225.2.3表格中列的数据类型的转换例如上图中的表格t3，导入数据后年龄列暂时被识别为文本类型，可以使用Table.TransformColumnTypes()将其设置为整数类型，代码如下，转换后得到的表格如下图5所示。t2=Table.TransformColumnTypes(t3,{"Age",Int32.Type})235.2.4将表格中值为记录类型的数据进行扩展可以使用Table.ExpandRecordColumn()函数将单元值为记录类型的数据进行扩展，该函数的语法形式如下：Table.ExpandRecordColumn(tableastable,columnastext,fieldNamesaslist,optionalnewColumnNamesasnullablelist)astable参数table是等待处理的表格;参数column是待扩展的列名，该列的每个元素都是一个记录数据;参数fieldNames是一个列表，列表的元素对应等待扩展的记录类型中的名称，作为需要扩展到列;参数newColumnNames给出扩展生成的新列的列名。当扩展表格时，记录数据会生成新的列，而非记录数据的原始表格中的数据会根据当前行中记录扩展的新行复制到每个新行中;返回值是扩展后的表格。245.2.5将表格中值为列表类型的数据进行扩展可以使用Table.ExpandListColumn()函数将单元值为列表类型的数据进行扩展，该函数的语法形式如下：Table.ExpandListColumn(tableastable,columnastext)astable参数table是等待处理的表格；参数column是待扩展的列名，该列的每个元素都是一个列表类型的数据，当扩展表格时，列表数据会生成新的列，而非列表数据的原始表格中的数据会根据当前行中记录扩展的新行复制到每个新行中；返回值是扩展后的表格。255.2.6删除表格中的列当表格中的某列不再需要时，可是使用Table.RemoveColumns()函数删除该列，函数的语法形式是：Table.RemoveColumns(tableastable,columnsasany,optionalmissingFieldasnullablenumber)astable参数table是等待处理的表格；参数columns是待删除的列名；返回值是删除指定列后的表格。265.2.7拆分表格中的列可以使用Table.SplitColumn()函数将表格中的指定列按照给定的拆分函数拆分为若干个新列。该函数的语法形式为：Table.SplitColumn(tableastable,sourceColumnastext,splitterasfunction,optionalcolumnNamesOrNumberasany,optionaldefaultasany,optionalextraColumnsasany)astable参数table是等待拆分的表格；参数sourceColumn是等待拆分的列名；参数splitter是拆分指定列数据的拆分函数；返回值是拆分列后的表格。275.2.8获取表格中的列名在处理过程中，可以获取表格各列的列名，以方便后续的数据处理。可以使用Table.ColumnNames()函数获取作为参数的表格的各列列名，组织为一个文本列表返回，该函数的语法形式如下：Table.ColumnNames(tableastable)aslist285.3.1文本数据的获取可以使用Csv.Document()从文本文件或CSV文件中获取文本数据，该函数的语法形式如下：Csv.Document(sourceasany,optionalcolumnsasany,optionaldelimiterasany,optionalextraValuesasnullablenumber,optionalencodingasnullablenumber)astable参数source表示数据源文件名称；参数columns给定想要获取的列的信息，可以是列的数量或者列名等信息；参数delimiter给定同一行数据之间的分隔符号，默认分隔符号是逗号；参数extraValues给出额外值类型；参数encoding给出文本文件的字符集编码。除了第一个参数外的其他信息可以组织成一个记录类型的参数。函数将获取的数据以表格形式返回。295.3.1文本数据的获取【例5-6】获取一个以空格作为分隔符号，第一行数据表示标题的文本文件数据，其内容是2020年度中国大陆各省的农作物产量信息。letsource=Csv.Document(File.Contents("D:\RESOURCES\agri.txt"),4,"",ExtraValues.Ignore,936),t1=Table.PromoteHeaders(source,[PromoteAllScalars=true]),t2=Table.TransformColumnTypes(t1,{{"地区",typetext},{"播种面积(千公顷)",Int64.Type},{"总产量(万吨)",Int64.Type},{"单位面积产量(公斤/公顷)",Int64.Type}})int2305.3.2EXCEL数据的获取可以使用File.Contents()和Excel.Workbook()配合从EXCEL文件中获取文本数据，File.Contents()函数的语法形式如下：File.Contents(pathastext,optionaloptionsasnullablerecord)asbinary参数path表示带获取的数据的文件路径，将文件内容以二进制形式读取后返回。315.3.2EXCEL数据的获取Excel.Workbook()函数的语法形式如下：Excel.Workbook(workbookasbinary,optionaluseHeadersasnullablelogical,optionaldelayTypesasnullablelogical)astable参数workbook表示数据源EXCEL文件内容的二进制映像；参数useHeaders说明是否使用标题行；参数delayTypes表示是否延迟类型指定。函数的返回值是一个表格，EXCEL工作簿文件的每个工作表作为一个表格中的行被识别。325.3.2EXCEL数据的获取【例5-7】获取EXCEL文件中的产品销售数据letsource=Excel.Workbook(File.Contents("D:\RESOURCES\sales.xlsx"),null,true),Sheet1_Sheet=source{[Item="Sheet1",Kind="Sheet"]}[Data],t1=Table.PromoteHeaders(Sheet1_Sheet,[PromoteAllScalars=true]),t2=Table.TransformColumnTypes(t1,{{"产品",typetext},{"销售量",Int64.Type},{"销售额",Int64.Type}})int2335.3.3网页数据的获取可以使用File.Contents()和Web.Page()函数配合从网页文档中获取文本数据，Web.Page()函数的语法形式如下：Web.Page(htmlasany)astable函数唯一的参数表示数据源网页文件的HTML源代码。函数的返回值是一个表格，网页文档本身以及网页文档中所包含的表格属性及数据被识别分别作为结果表格的一行。当需要后续处理时，通过指名提取该函数返回结果表格中的哪一行来获取相应的实际表格数据。345.3.3网页数据的获取【例5-8】获取网页文档中包含的美元对一些国家货币的汇率信息。letsource=Web.Page(File.Contents("file://D:\RESOURCES\part3.html")),Data0=source{0}[Data],t1=Table.TransformColumnTypes(Data0,{{"USDollar",typetext},{"1.00USD",typenumber},{"inv.1.00USD",typenumber}})int1355.4.1文本数据的提取1. 提取文本中指定位置的一个字符可以使用Text.At()实现提取文本中指定位置的一个字符，该函数的语法形式如下：Text.At(textasnullabletext,indexasnumber)asnullabletext参数text是原文本；参数index是从0开始的下标位置；返回值是单个字符构成的文本数据。例如Text.At("china",2)返回”I”，Text.At("中国南京",2)返回“南”。365.4.1文本数据的提取2. 从文本开始位置向后提取指定数量的字符构成子文本可以使用Text.Start()实现从文本开始位置向后提取指定数量的字符构成子文本，该函数的语法形式如下：Text.Start(textasnullabletext,countasnumber)asnullabletext参数text是原文本；参数count是提取的字符数量。例如Text.Start("中国南京",2)返回“中国”。375.4.1文本数据的提取3. 从文本结尾位置向前提取指定数量的字符构成子文本可以使用Text.End()实现3. 从文本结尾位置向前提取指定数量的字符构成子文本，该函数的语法形式如下：Text.End(textasnullabletext,countasnumber)asnullabletexttext参数是原文本；参数count是提取的字符数量。例如Text.End("中国南京",2)返回“南京”。385.4.1文本数据的提取4.从文本中间某个位置开始提取指定数量的字符构成子文本可以使用Text.Middle()实现从文本中间某个位置开始提取指定数量的字符构成子文本，该函数的语法形式如下：Text.Middle(textasnullabletext,startasnumber,optionalcountasnullablenumber)asnullabletext参数text是原文本；参数start提取的起始位置，通过从0开始的下标表示；可选的参数count是提取的字符数量，如果缺省的话表示一直提取到文本结束。例如Text.Middle("中国江苏南京",2,2)返回“江苏”，而Text.Middle("中国江苏南京",2)则返回“江苏南京”。395.4.2文本拆分可以使用Text.Split()函数将子文本通过拆分的方式从原文本中提取出来，该函数的语法形式如下：Text.Split(textastext,separatorastext)aslist参数text是待拆分的原文本；参数separator是作为分隔符号的文本；返回值是拆分出来的子文本构成的列表。在表格处理中，将一列文本数据根据分隔符号拆分为多列数据时需要使用Text.Split()函数处理每一行上的原始文本数据。该函数的一个示例可以是：通过使用Text.Split("86,"-")实现将完整的电话号码文本拆分为国家代码“86”、地区编码“025”和座机号码“80008888”三个部分，返回值是一个列表{“86”,”025”,”80008888”}。405.4.3文本合并有时候需要将多个文本合并成一段完整的文本，此时可以使用Text.Combine()函数完成文本的合并任务。在表格数据处理中，如果需要将多列数据合并为一列，可以根据情况将非文本数据转换为文本类型，然后使用Text.Combine()函数完成每一行上数据的合并任务。该函数的语法形式如下：Text.Combine(textsaslist,optionalseparatorasnullabletext)astext参数texts是待合并的多个文本构成的列表；可选的参数separator是合并时用作分隔各个子文本的分隔符号，通过分隔符号在需要时可以将数据再拆分出来，当第二个参数缺省时，所有子文本无分隔的被合并为完整的文本。例如使用Text.Combine({"86","025","80008888"},"-")可以得到文本"86，而使用Text.Combine({"86","025","80008888"})可以得到文本"8602580008888"。415.4.4文本处理案例—身份证号码解析身份证号码的解析。中国大陆地区的身份证号码是典型的具有编码格式的文本。下面尝试从“数据处理素材.xlsx”中导入“身份证号码”信息，在此查询基础上，根据身份证号码，生成“地区编码”、“生日”和“性别”信息。在身份证文本中，前6个字符构成代表地区的编码文本；中间6到8位代表生日，需要注意15位老身份证和18位新身份证的区别，15位老身份证7到12位是生日，需加年代“19”，18位身份证7到14位是生日；可以根据15位老身份证最后一位和18位身份证倒数第二位确定性别，奇数为男性，偶数为女性。425.4.4文本处理案例—身份证号码解析解决思路：（1）问题的求解方案，是根据数据表格中“身份证号”数据列，设计算法产生“地区编码”、“生日”和“性别”三个新的数据列。因此可以考虑设计三个函数，以每一行中“身份证号”数据为输入，分别产生“地区编码”、“生日”和“性别”三列所对应的数据。（2）对于“地区编码”信息：算法思想是从左其获取“身份证号”文本的六个字符构成“地区编码”文本子串即可，因此可以应用文本函数中的TEXT.START()处理。（3）对于“生日”信息：可以使用文本函数中的TEXT.MIDDLE()获取处于“身份证号”文本中间的生日子串，但是需要根据“身份证号”文本的长度判断是18位新身份证号还是15位老身份证号，新老身份证号需要获取的文本子串起始位置和长度都不同，而且对于老身份证号还需要增补“19”年代子串在生日子串的前面；当生成生日文本子串后，再使用类型转换函数Date.FromText()将生日子串转换为日期类型的数据。这是一个包含分支判断的操作序列，单独一个M语言函数无法完成任务，因此可以考虑编写一个自定义函数来完成“生日”信息的生成。435.4.4文本处理案例—身份证号码解析解决思路：（4）对于“性别”信息：可以由“身份证号”文本中的一个字符来进行判断，因此可以考虑使用Text.At()函数实现单个字符的提取，但是新老身份证性别字符的位置是不同的，因此需要分情况进行判断处理；当提取到性别字符后，需要根据其奇偶性判断，因此需要将文本转换为数值，可以考虑使用Number.FromText()进行数据类型转换，再使用数值函数Number.Mod()进行奇偶性判断。这些任务也需要编写一个自定义函数来完成。445.4.4文本处理案例—身份证号码解析455.5.1数值处理常用函数1. 数值的舍入运算可以使用Number.Round()函数实现实数的舍入运算，该函数的语法形式如下：Number.Round(numberasnullablenumber,optionaldigitsasnullablenumber,optionalroundingModeasnullablenumber)asnullablenumber参数number是需要舍入的实数；可选的参数是舍入发生的小数点位置digits，如果省略的话number被舍入到最近的整数；可选的参数是舍入的方向roundingMode，当其值为RoundingMode.Up时表示向上舍入，当其值为RoundingMode.Down时表示向上舍入，注意该参数只在待舍入小数位置的下一位为5时起作用，当不等于5时还是按照小于5向下舍入，大于5向上进位处理；函数的返回值是根据参数要求舍入后的数值，如果传入的第一个参数number的值是空值，则返回值也是空值。465.5.1数值处理常用函数例Number.Round(1.23)的值为1，Number.Round(1.53)的值为2Number.Round(-1.53)的值为-2Number.Round(1.27,1)的值为1.3Number.Round(1.675,2,RoundingMode.Down)的值为1.67Number.Round(1.675,2,RoundingMode.Up)的值为1.68Number.Round(1.673,2,RoundingMode.Down)和Number.Round(1.673,2,RoundingMode.Up)的值均为1.67Number.Round(1.676,2,RoundingMode.Down)和Number.Round(1.676,2,RoundingMode.Up)的值均为1.68。475.5.1数值处理常用函数2. 乘幂和对数运算可以使用Number.Power()函数实现乘幂运算，该函数的语法形式如下：Number.Power(numberasnullablenumber,powerasnullablenumber)asnullablenumber函数的返回值是number的power次方，如果两个参数中有一个为空值，则函数返回空值。例如Number.Power(10,3)返回1000，Number.Power(1.5,2)返回2.25。注意求自然常数e的乘幂可以使用Number.Exp()函数。485.5.1数值处理常用函数可以使用Number.Log()函数实现求对数运算，，该函数的语法形式如下：Number.Log(numberasnullablenumber,optionalbaseasnullablenumber)asnullablenumber函数的返回值是以base为底的number的对数，如果第二个参数缺省，则缺省值为自然常数e，如果第一个参数number的值为空值，则函数返回空值。例如Number.Log(100,10)返回2，Number.Log(256,2)返回8，Number.Log(2.718)的值为0.999896315728952。也可以使用Number.Ln()求自然对数，使用Number.Log10()求10为底的对数。495.5.2数值处理案例——计算销售业绩奖金在“员工销售数据.xlsx”文件中的“员工数据”表中提供了某企业员工的个人情况和销售情况，现在需要计算每个员工的基础工资、销售奖金和总收入，并根据如下规则产生三个新列，基础工资列根据下图的规则由文化程度列生成，奖金列根据下图的规则由销售业绩列生成，实际收入列根据“基础工资+奖金”的规则生成。505.5.2数值处理案例——计算销售业绩奖金解决思路：可以为基础工资的计算和销售奖金的计算各设计一个函数来实现。具体实现代码：letbasesalary=(diplomaastext)=>let salary=ifdiploma="博士"then5200 elseifdiploma="硕士"then4700 elseifdiploma="本科"then4100 else3200in salary,515.5.2数值处理案例——计算销售业绩奖金compute_bonus=(salesasnumber)=>let bonus=ifsales<40000thensales*0.05 elseifsales>=40000andsales<50000thensales*0.08 elsesales*0.1in bonus,525.5.2数值处理案例——计算销售业绩奖金Source=Excel.Workbook(File.Contents("D:\RESOURCES\员工销售数据.xlsx"),null,true),

员工数据_Sheet=Source{[Item="员工数据",Kind="Sheet"]}[Data],#"PromotedHeaders"=Table.PromoteHeaders(员工数据_Sheet,[PromoteAllScalars=true]),#"ChangedType"=Table.TransformColumnTypes(#"PromotedHeaders",{{"工号",Int64.Type},{"姓名",typetext},{"身份证号码",typetext},{"文化程度",typetext},{"销售业绩",Int64.Type}}),table1=Table.AddColumn(#"ChangedType","基础工资",eachbasesalary([文化程度])),table2=Table.AddColumn(table1,"奖金",eachcompute_bonus([销售业绩])),table3=Table.AddColumn(table2,"实际收入",each([基础工资]+[奖金]))intable3535.6.1日期和时间的生成用于日期和时间生成的M语言函数及其说明如表所示54函数DateTime.LocalNow()asdatetime作用返回由系统时间得到的调用时刻的日期时间示例DateTime.LocalNow()函数#date(yearasnumber,monthasnumber,dayasnumber)asdate作用由给定的年、月、日分量构造一个日期数据示例#date(2019,10,28)返回日期"2019-10-28"函数#time(hourasnumber,minuteasnumber,secondasnumber)astime作用由给定的时、分、秒分量构造一个时间数据示例#time(9,30,0)返回时间"9:30:00"函数#datetime(yearasnumber,monthasnumber,dayasnumber,hourasnumber,minuteasnumber,secondasnumber)asany作用由给定的年、月、日、时、分、秒分量构造一个日期时间型数据示例#datetime(2019,10,28,9,30,0)返回日期时间数据"2019-10-289:30:00"函数DateTime.Date(dateTimeasany)asnullabledate作用根据给出的参数得到其日期部分示例DateTime.Date(#datetime(2019,10,28,9,30,0))返回日期"2019-10-28"函数DateTime.Time(dateTimeasany)asnullabletime作用根据给出的参数得到其时间部分示例DateTime.Time(#datetime(2019,10,28,9,30,0))返回时间"9:30:00"5.6.2取日期和时间分量能够完成取日期和时间分量的函数及其说明如表所示55函数Date.Year(dateTimeasany)asnullablenumber作用取年份分量示例Date.Year(Date.FromText("2019-10-28"))返回年份2019函数Date.Month(dateTimeasany)asnullablenumber作用取月份分量示例Date.Month(Date.FromText("2019-10-28"))返回月份10函数Date.Day(dateTimeasany)asnullablenumber作用取日子分量示例Date.Day(Date.FromText("2019-10-28"))返回具体日子28函数Time.Hour(dateTimeasany)asnullablenumber作用取小时分量示例Time.Hour(DateTime.FromText("2019-10-28T09:30:00"))返回小时分量9函数Time.Minute(dateTimeasany)asnullablenumber作用取分钟分量示例Time.Minute(DateTime.FromText("2019-10-28T09:30:00"))返回小时分量30函数Time.Second(dateTimeasany)asnullablenumber`作用取秒分量示例Time.Second(DateTime.FromText("2019-10-28T09:30:00"))返回小时分量05.6.3日期时间的计算1. 日期偏移的计算56函数Date.AddDays(dateTimeasany,numberOfDaysasnumber)asany作用以第一个参数为基准，偏移第二个参数给定的天数后的日期。示例Date.AddDays(Date.FromText("2019-10-28"),5)返回日期"2019-11-2"Date.AddDays(Date.FromText("2019-10-28"),-2)返回日期"2019-10-26"函数Date.AddMonths(dateTimeasany,numberOfMonthsasnumber)asany作用以第一个参数为基准，偏移第二个参数给定的月份数后的日期。示例Date.AddMonths(Date.FromText("2019-10-28"),1)返回日期"2019-11-28"函数Date.AddQuarters(dateTimeasany,numberOfQuartersasnumber)asany作用以第一个参数为基准，偏移第二个参数给定的季度数后的日期。示例Date.AddQuarters(Date.FromText("2019-10-28"),1)返回日期"2020-1-28"函数Date.AddWeeks(dateTimeasany,numberOfWeeksasnumber)asany作用以第一个参数为基准，偏移第二个参数给定的星期数后的日期。示例Date.AddWeeks(Date.FromText("2019-10-28"),2)返回日期"2019-11-11"函数Date.AddYears(dateTimeasany,numberOfYearsasnumber)asany作用以第一个参数为基准，偏移第二个参数给定的年份数后的日期。示例Date.AddYears(Date.FromText("2019-10-28"),1)返回日期"2020-10-28"5.6.3日期时间的计算2. 日期定位的计算57函数Date.DayOfWeek(dateTimeasany,optionalfirstDayOfWeekasnullablenumber)asnullablenumber作用计算第一个参数给定的日期是所在星期的第几天，返回值在0到6之间，第二个可选参数表示第一天的基准，可以使用Day.Sunday,Day.Monday等，如果不给的话，缺省值取决于系统所在国家的文化习惯。示例在中国大陆地区，Date.DayOfWeek(Date.FromText("2019-10-28"))返回0Date.DayOfWeek(Date.FromText("2019-10-28"),Day.Sunday)返回1函数Date.DayOfWeekName(dateasany,optionalcultureasnullabletext)作用计算第一个参数给定的日期是星期几，第二个可选参数是表示文化习惯的文本示例Date.DayOfWeekName(Date.FromText("2019-10-28"))返回”星期一”Date.DayOfWeekName(Date.FromText("2019-10-28"),"en-us")返回”Monday”函数Date.DayOfYear(dateTimeasany)asnullablenumber作用计算第一个参数给定的日期是所在年份的第几天示例Date.DayOfYear(Date.FromText("2019-10-28"))返回301函数Date.WeekOfYear(dateTimeasany,optionalfirstDayOfWeekasnullablenumber)asnullablenumber作用计算第一个参数给定的日期是所在年份的第几个星期，第二个可选的参数表示以星期几作为一个星期的开始，可以使用Day.Sunday,Day.Monday等，如果不给的话，缺省值取决于系统所在国家的文化习惯。示例Date.WeekOfYear(Date.FromText("2019-10-28"))返回44函数Date.QuarterOfYear(dateTimeasany)asnullablenumber作用计算第一个参数给定的日期是所在年份的第几个季度示例Date.QuarterOfYear(Date.FromText("2019-10-28"))返回4函数Date.IsLeapYear(dateTimeasany)asnullablelogical作用判断第一个参数给定的日期所在年份是否是闰年示例Date.IsLeapYear(Date.FromText("2019-10-28"))返回FALSE5.6.4日期时间数据处理案例有时候需要生成指定起止时间段内的包含年、月、日、星期、季度等信息的日期表，例如在财务数据分析中需要借助日期表来进行统计分析。生成日期表的关键在于首先根据起止时间段生成之间每一天的日期数据构成的列表，然后转换为表格，接着使用日期函数来根据日期数据列生成其他各个信息列。下面给出生成一个简易日期表的相关代码及其注释，生成的日期表如图所示。585.6.4日期时间数据处理案例let//qi'起始和截至日期，这里使用构造方式演示，实际处理中可以从其他数据源得到

start=#date(2019,2,28),end=#date(2020,2,5),/*使用列表生成函数构造包含每一天日期的列表，这里的用法是以start为起始值，在生成的列表元素值小于等于end值时，继续使用Data.AddDays以1天为偏移量生成下一个列表元素。*/workdays=List.Generate(()=>start,each_<=end,eachDate.AddDays(_,1)),//转换成只有一列“日期”的表格

cal1=Table.FromColumns({workdays},{"日期"}),//设置类型为datecal2=Table.TransformColumnTypes(cal1,{"日期",typedate}),595.6.4日期时间数据处理案例

//使用Date.Year()函数生成年份列

cal3=Table.AddColumn(cal2,"年",eachDate.Year([日期]),typenumber),//使用Date.Month()函数生成月份列

cal4=Table.AddColumn(cal3,"月",eachDate.Month([日期]),typenumber),//使用Date.Day()函数生成日子列

cal5=Table.AddColumn(cal4,"日",eachDate.Day([日期]),typenumber),//使用Date.DayOfWeek()函数生成星期列，表示该日期是星期几

cal6=Table.AddColumn(cal5,"星期",eachDate.DayOfWeek([日期]),typenumber),//使用Date.QuarterOfYear()函数生成季度列

cal7=Table.AddColumn(cal6,"季度",eachDate.QuarterOfYear([日期]),typenumber),

res=cal7inres605.7.1JSON数据的转换和读取JSON数据的转换可以使用Json.FromValue()函数将不同类型的数据转换为JSON对象，函数语法形式如下：Json.FromValue(valueasany,optionalencodingasnullablenumber)asbinary该函数将参数value给出的某种类型的值转换为JSON表示，具体值的类型和转换后得到的JSON表示如下所述：NULL，文本和逻辑值被表示为相应的JSON类型；数字被表示为JSON中的数字，特殊常量##infinity,-#infinity和#nan会被转换为空值；列表被表示为JSON数组；记录被表示为JSON对象；表格被表示为对象数组；Dates,times,datetimes,datetimezones和durations类型的数据被表示为ISO-8601格式的文本；二进制值被表示为基于64编码的文本；如果传入的不是数据，而是数据类型或函数，则会产生一个错误。参数encoding表示使用的文本编码，缺省值为UTF8。615.7.1JSON数据的转换和读取JSON数据的获取可以使用Json.Document()函数读取外部的JSON数据并转换为相应的类型，函数语法形式为：Json.Document(jsonTextasany,optionalencodingasnullablenumber)asany该函数将参数jsonText代表的JSON文本转换为相应的M语言内部数据类型，对应关系和Json.FromValue()函数说明中类似，例如JSON对象转换为记录，JSON数组转换为列表。参数encoding表示使用的文本编码，缺省值为UTF8。如果读取JSON数据处理时出现乱码，则需要考虑修改数据源中的字符集编码设置。625.7.2JSON数据的整理1. 层次型JSON数据的转换层次型JSON数据是具有树形层次结构的数据的JSON表达。635.7.2JSON数据的整理letstep1=Json.Document(File.Contents("D:\RESOURCES\city.json"),936),//第一步，读取JSON数据，注意指定文本编码，否则可能出现乱码step2=Record.ToTable(step1),//第二步，将得到的记录数据转换为表格数据step3=Table.Transpose(step2),//第三步，表格行列转置，理顺标题于数据之间的关系step4=Table.PromoteHeaders(step3,[PromoteAllScalars=true]),//第四步，将第一行数据提升为标题，这里的标题就是最顶层JSON对象中的名称step5=Table.ExpandListColumn(step4,"province"),//第五步，扩展第一层结点所属的JSON数组对应的列表，展开后的行数由原JSON数组元素个数决定//第六步，原数组中每个元素都是JSON对象，现在就是记录，再次扩展，根据记录的名称-值对情况扩展为多列

step6=Table.ExpandRecordColumn(step5,"province",{"name","cities"},{"province","cities"}),step7=Table.ExpandRecordColumn(step6,"cities",{"city"},{"city"}),//第七步，扩展后还是JSON对象，虽然只有一个名称-值对，继续扩展step8=Table.ExpandListColumn(step7,"city"),//第八步，现在是列表，因为之前的值是文本数组，继续将列表扩展result=step8//处理完毕inresult645.7.2JSON数据的整理表格型JSON数据的转换有些JSON数据中，包含多个名称值对，每个名称的值是一个属性名，而对应的值是一个数据序列，而每个名称值对中值所对应的数据序列的长度是相同的，因此这其实是一张表格数据的JSON表示，每个名称值对代表的是表格中的一列。655.7.2JSON数据的整理let//读取表格型JSON数据，得到一个记录

step1=Json.Document(File.Contents("D:\RESOURCES\gdp.json"),936),//从记录中得到所有的名称，构成一个列表

tmpnames=Record.FieldNames(step1),//从记录中得到所有值，这里每个值都是一个列表，且元素个数相同，最终构成一个列表的列表

step2=Record.ToList(step1),//使用列表的列表转换得到一个表格，表格的列名由之前得到名称列表指定

step3=Table.FromColumns(step2,tmpnames),//得到最终结果

result=step3inresult665.8数据处理综合案例——半结构化EXCEL数据的处理有的时候从外部数据源导入的数据并非完全结构化的，这包括EXCEL文件、网页文档、JSON对象等。但是这些数据本身却是可以通过业务逻辑结合数据表示格式进行解析，并通过数据处理手段将其结构化并存储为PowerBI的数据表格对象。675.8数据处理综合案例——半结构化EXCEL数据的处理基本处理思路：（1）这里出现的数据是半结构化的，体现在数据并非严格按照行列排列，每列代表一个属性的数据，并具有相同的类型；（2）EXCEL工作表中的每个原子数据本质上是存储在一个单元格内的，图中所示杜邦分析效果是通过合并单元格、设置居中等单元格格式设置手段实现的，当消除所有单元格格式设置后，可以看到最原始的每个数据所在的单元格位置。例如财务指标名称“净资产收益率”位于A2单元格，也就是行列位置为第1列第2行，而该指标的值7.63%位于A3单元格，也就是行列位置为第1列第3行。（3）EXCEL工作表被导入PowerBI后，转换为表格对象后，其行列与EXCEL工作表中的行列是对应的，因此这里的半结构化EXCEL工作表转换成PowerBI表格对象后，因为标题和数据没有明显的划分结构，因此所有数据都作为文本类型被识别，而没有值的EXCEL工作表单元格对应的PowerBI表格对象的表格对象单元格的值是空值。685.8数据处理综合案例——半结构化EXCEL数据的处理基本处理思路：（4）因此，对于一个EXCEL文件，可以通过在程序中提供原EXCEL工作表中各数据标题和具体数值的位置，来在转换后的PowerBI表格中提取相应单元格的值，从而构造标题列表和数据列表，再转换为PowerBI表格，实现一张半结构化EXCEL表的结构化处理，构成最终表格数据中的一行。（5）为了使得能够自动化的获取需要提取的数据及其标题的位置，可以构造一个模板EXCEL表，用于说明每个标题、其对应的数据以及提取后在PowerBI表格中的次序。这样在将EXCEL表导入为PowerBI表后，可以沿着行方向表格拆分成一行一行，然后按照从首行到最后一行的次序将所有行合并成一行数据，就可以使用PowerQuery的List.PositionOf()函数来自动提取所有标题和数据的位置，进而使用得到的位置，在真正的财务数据表格中提取相关数据及其对应的标题。（6）可以通过对文件夹内文件的逐个处理，获取所有的企业数据表格，然后合并这些表格，再对每一列数据进行合适的处理并指定正确的数据类型，即可得到最终结构化后的所有企业的财务指标数据表格。695.8数据处理综合案例——半结构化EXCEL数据的处理705.8数据处理综合案例——半结构化EXCEL数据的处理当需要对一个文件夹里的文件逐个进行处理时，可以将根据模板提取标题和数据的单元格位置封装为一个模块，然后将处理一个数据文件的代码也封装为模块，根据文件、标题和数据的位置来实现数据的提取和转换。像净利润这些数据的值包含中文的数量单位“亿”，因此并不是数值数据，而是文本数据，这是无法应用到后续的数据分析中的，因此需要按照业务逻辑含义对得到的表格对象的每一列数据进行合适的处理，再设置最终的数据类型，得到转换成功的数据表。715.8数据处理综合案例——半结构化EXCEL数据的处理let get_title_data_pos=(modelfilebinaryasbinary,posctasnumber)=>let modelfile=Excel.Workbook(modelfilebinary,null,true), model_Sheet1_Sheet=modelfile{[Item="Sheet1",Kind="Sheet"]}[Data], model_t1=Table.TransformColumnTypes(model_Sheet1_Sheet,{{"Column1",typetext},{"Column2",typetext},{"Column3",typetext},{"Column4",typetext},{"Column5",typetext},{"Column6",typetext},{"Column7",typetext},{"Column8",typetext},{"Column9",typetext},{"Column10",typetext},{"Column11",typetext},{"Column12",typetext}}), model_t2=Table.ToColumns(model_t1), model_t3=List.Combine(model_t2), titletext=List.Transform({1..posct},each"标题"&Number.ToText(_)), title_pos=List.Combine(List.Transform(titletext,eachList.PositionOf(model_t3,_,List.Count(model_t3)))), datatext=List.Transform({1..posct},each"数据"&Number.ToText(_)), data_pos=List.Combine(List.Transform(datatext,eachList.PositionOf(model_t3,_,List.Count(model_t3)))), res={title_pos,data_pos} in res,725.8数据处理综合案例——半结构化EXCEL数据的处理 get_data=(datafilebinaryasbinary,data_posaslist)=>let datafile=Excel.Workbook(datafilebinary,null,true), data_Sheet1_Sheet=datafile{[Item="Sheet1",Kind="Sheet"]}[Data], data_t1=Table.TransformColumnTypes(data_Sheet1_Sheet,{{"Column1",typetext},{"Column2",typetext},{"Column3",typetext},{"Column4",typetext},{"Column5",typetext},{"Column6",typetext},{"Column7",typetext},{"Column8",typetext},{"Column9",typetext},{"Column10",typetext},{"Column11",typetext},{"Column12",typetext}}), data_t2=Table.ToColumns(data_t1), data_l3=List.Combine(data_t2), data=List.Transform(data_pos,eachdata_l3{_}) in data,735.8数据处理综合案例——半结构化EXCEL数据的处理 get_title=(datafilebinaryasbinary,title_posaslist)=>let datafile=Excel.Workbook(datafilebinary,null,true), data_Sheet1_Sheet=datafile{[Item="Sheet1",Kind="Sheet"]}[Data], data_t1=Table.TransformColumnTypes(data_Sheet1_Sheet,{{"Column1",typetext},{"Column2",typetext},{"Colu