POWERBI數(shù)據(jù)處理和分析-使用POWER QUERY之M語言進(jìn)行自動(dòng)化數(shù)據(jù)處理

使用POWERQUERY之M語言進(jìn)行數(shù)據(jù)處理1章節(jié)內(nèi)容5.1數(shù)據(jù)類型之間互相轉(zhuǎn)換基礎(chǔ)5.2表格數(shù)據(jù)的行列處理轉(zhuǎn)換基礎(chǔ)5.3常見數(shù)據(jù)源數(shù)據(jù)的獲取5.4文本數(shù)據(jù)的處理5.5數(shù)值數(shù)據(jù)的處理5.6日期時(shí)間數(shù)據(jù)的處理5.7JSON數(shù)據(jù)的處理5.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理5.9數(shù)據(jù)處理過程中M語言的靈活應(yīng)用2第5章使用POWERQUERY之M語言進(jìn)行數(shù)據(jù)處理M語言可以解決通過界面操作方式無法解決的問題（1）使用M語言編程，可以清晰的理解數(shù)據(jù)處理過程中每一步的數(shù)據(jù)表示形式，以及各個(gè)步驟之間數(shù)據(jù)表示形式是如何轉(zhuǎn)換的，因此可以順利的設(shè)計(jì)數(shù)據(jù)處理過程中從源數(shù)據(jù)形式到最終目的數(shù)據(jù)形式的轉(zhuǎn)換途徑；（2）本來人力無法勝任的很多需要重復(fù)進(jìn)行的繁瑣工作，可以由計(jì)算機(jī)自動(dòng)化的來進(jìn)行求解。35.1.1原子類型數(shù)據(jù)之間的類型轉(zhuǎn)換1.文本和數(shù)值數(shù)據(jù)之間的轉(zhuǎn)換可以使用Number.ToText()函數(shù)將數(shù)值數(shù)據(jù)轉(zhuǎn)換為文本數(shù)據(jù)，該函數(shù)的語法形式如下：Number.ToText(numberasnullablenumber,optionalformatasnullabletext,optionalcultureasnullabletext)asnullabletext例：Number.ToText(123)得到文本“123”，而Number.ToText(123,”e”)得到文本“"123.000000e+000"”?？梢允褂肗umber.FromText()函數(shù)將文本數(shù)據(jù)轉(zhuǎn)換為數(shù)值數(shù)據(jù)，該函數(shù)的語法形式如下：Number.FromText(textasnullabletext,optionalcultureasnullabletext)asnullablenumber例：Number.FromText("567")得到數(shù)值567。45.1.1原子類型數(shù)據(jù)之間的類型轉(zhuǎn)換2.將其他類型數(shù)據(jù)轉(zhuǎn)換為文本類型的數(shù)據(jù)可以使用Text.From()函數(shù)將number、date、time、datetime、datetimezone、logical、duration或binary類型的數(shù)據(jù)轉(zhuǎn)換為文本類型，該函數(shù)的語法形式如下：Text.From(valueasany,optionalcultureasnullabletext)asnullabletext例：Text.From(1)將數(shù)值數(shù)據(jù)1轉(zhuǎn)換為文本“1”，Text.From(true)將邏輯值true轉(zhuǎn)換為文本“true”55.1.1原子類型數(shù)據(jù)之間的類型轉(zhuǎn)換3.將其他類型數(shù)據(jù)轉(zhuǎn)換為數(shù)值類型的數(shù)據(jù)可以使用Number.From()函數(shù)將text、date、time、datetime、datetimezone、logical、duration類型的數(shù)據(jù)轉(zhuǎn)換為文本類型，該函數(shù)的語法形式如下：Number.From(valueasany,optionalcultureasnullabletext)asnullablenumber例：Number.From(#date(2019,10,28))得到43766，Number.From(true)將邏輯值true轉(zhuǎn)換為數(shù)值1。65.1.1原子類型數(shù)據(jù)之間的類型轉(zhuǎn)換4.將文本類型數(shù)據(jù)轉(zhuǎn)換為日期類型可以使用Date.FromText()函數(shù)將符合某種文化的日期格式的文本轉(zhuǎn)換為日期類型的數(shù)據(jù)，該函數(shù)的語法形式如下：Date.FromText(textasnullabletext,optionalcultureasnullabletext)asnullabledate例：Date.FromText("2019-10-28")得到代表2019年10月28日的date類型數(shù)據(jù)。75.1.2表格和記錄數(shù)據(jù)之間互相轉(zhuǎn)換1.將記錄數(shù)據(jù)轉(zhuǎn)換為表格可以使用Record.ToTable()或Table.FromRecords()函數(shù)將記錄數(shù)據(jù)轉(zhuǎn)換為表格。Table.FromRecords()函數(shù)的語法形式如下：Table.FromRecords(recordsaslist,optionalcolumnsasany,optionalmissingFieldasnullablenumber)astable85.1.2表格和記錄數(shù)據(jù)之間互相轉(zhuǎn)換【例5-1

】通過手工構(gòu)造記錄數(shù)據(jù)，轉(zhuǎn)換為表格，效果如圖所示。let//通過手工構(gòu)造的列表創(chuàng)建一個(gè)表格保存到t1中

t1=Table.FromRecords({[Name="Name",Value="Zhou"],[Name="Age",Value=25],[Name="Country",Value="CHINA"]}),res=t1inres95.1.2表格和記錄數(shù)據(jù)之間互相轉(zhuǎn)換Record.ToTable()函數(shù)的語法形式如下：Record.ToTable(recordasrecord)astable該函數(shù)將一個(gè)記錄對象轉(zhuǎn)換為表格，表格只有兩列，分別代表名稱和值，而記錄對象中的每個(gè)名稱-值對會(huì)作為轉(zhuǎn)換后的表格中的一行。105.1.2表格和記錄數(shù)據(jù)之間互相轉(zhuǎn)換2.表格數(shù)據(jù)轉(zhuǎn)換為記錄可以使用Table.ToRecords()和Record.FromTable()函數(shù)完成將表格數(shù)據(jù)轉(zhuǎn)換為記錄的任務(wù)。Table.ToRecords()函數(shù)的語法形式如下：Table.ToRecords(tableastable)aslist該函數(shù)將作為參數(shù)的表格轉(zhuǎn)換為一個(gè)記錄的列表返回，表格的列名和每一行的值分別構(gòu)成記錄列表中每個(gè)記錄的名稱-值對。Record.FromTable()函數(shù)的語法形式如下：Record.FromTable(tableastable)asrecord115.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換1.將列表數(shù)據(jù)轉(zhuǎn)換為表格可以使用Table.FromRows()函數(shù)按照行方向構(gòu)造表格，該函數(shù)語法形式為：Table.FromRows(rowsaslist,optionalcolumnsasany)astable參數(shù)rows是代表每行數(shù)據(jù)的列表，一般而言該列表的每個(gè)元素也是一個(gè)列表，代表一行數(shù)據(jù)，所以每個(gè)元素列表的長度應(yīng)該是一樣的；可選的參數(shù)columns用來給定每一列的名稱，因此是一個(gè)文本列表，返回值是得到的表格125.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換【例5-2】假設(shè)原有代表表格數(shù)據(jù)的列表的列表{{“18101”,“張強(qiáng)”,“男”},{“18102”,“李云”,“女”},{“18103”,“趙飛”,“男”}}，則使用如下代碼替換【例5-1】中的t1定義可以得到如圖所示的表格。t1=Table.FromRows({{"18101","張強(qiáng)","男"},{"18102","李云","女"},{"18103","趙飛","男"}},{"學(xué)號(hào)","姓名","性別"})135.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換可以使用Table.FromColumns()函數(shù)按照列方向構(gòu)造表格，該函數(shù)語法形式為：Table.FromColumns(listsaslist,optionalcolumnsasany)astable參數(shù)lists是代表每列數(shù)據(jù)的列表的列表，每個(gè)子列表代表一列，通常情況下每個(gè)子列表的長度應(yīng)該一致，否則會(huì)導(dǎo)致有些表格單元數(shù)據(jù)為空值；可選的參數(shù)columns用來給定每一列的名稱，因此是一個(gè)文本列表。返回值是得到的表格。145.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換【例5-3】假設(shè)原有代表表格數(shù)據(jù)的列表的列表{{“18101”,“18102”,“18103”},{“張強(qiáng)”,“李云”,“趙飛”},{“男”,“女”,“男”}}，則使用如下t1定義代碼可以得到如圖所示的表格。t1=Table.FromColumns({{"18101","18102","18103"},{"張強(qiáng)","李云","趙飛"},{"男","女","男"}},{"學(xué)號(hào)","姓名","性別"})155.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換2.將表格數(shù)據(jù)轉(zhuǎn)換為列表將表格數(shù)據(jù)轉(zhuǎn)換為列表時(shí)，可以有選擇按行方向或者列方向進(jìn)行轉(zhuǎn)換。可以使用Table.ToList()函數(shù)將每一行的數(shù)組按照某種合并方式組合后生成一個(gè)數(shù)據(jù)，然后將整個(gè)表格的所有行轉(zhuǎn)換為一個(gè)列表，該函數(shù)語法形式為：Table.ToList(tableastable,optionalcombinerasnullablefunction)aslist參數(shù)table是等待轉(zhuǎn)換的表格；可選的參數(shù)combiner表示合并每行數(shù)組所使用的合并函數(shù)。返回值是一個(gè)列表，列表的每個(gè)元素對應(yīng)表格中一行數(shù)據(jù)使用合并函數(shù)合并后的值。165.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換【例5-4】如例5-2中所示的表格t1，將每行數(shù)據(jù)都轉(zhuǎn)換為文本類型，使用文本合并函數(shù)以逗號(hào)為分隔符合并成一個(gè)文本，最終構(gòu)成一個(gè)如圖所示的文本列表。t2=Table.ToList(t1,Combiner.CombineTextByDelimiter(",")),175.1.3表格和列表數(shù)據(jù)之間互相轉(zhuǎn)換可以使用Table.ToColumns()函數(shù)將表格按照列方向轉(zhuǎn)換為列表，表格的每一列被轉(zhuǎn)換為一個(gè)列表，而整個(gè)表格被轉(zhuǎn)換為列表的列表。例如例5-2中所示的表格t1可以使用如下代碼按照列方向轉(zhuǎn)換，轉(zhuǎn)換后效果如圖所示。t2=Table.ToColumns(t1)185.2.1表格行列轉(zhuǎn)換在很多場合下，需要進(jìn)行表格的行列轉(zhuǎn)換，例如進(jìn)行矩陣計(jì)算或者對外部讀入的數(shù)據(jù)進(jìn)行整理時(shí)，可以通過Table.Transpose()函數(shù)完成表格的行列互換任務(wù)，該函數(shù)的語法形式為：Table.Transpose(tableastable,optionalcolumnsasany)astable參數(shù)table是希望處理的表格；返回值是行列轉(zhuǎn)置以后的表格。195.2.1表格行列轉(zhuǎn)換【例5-5】可以將例5-1中的表格使用Table.Transpose()函數(shù)完成行列轉(zhuǎn)置，效果如圖所示，相關(guān)代碼如下：

let//通過手工構(gòu)造的列表創(chuàng)建一個(gè)表格保存到t1中

t1=Table.FromRecords({[Name="Name",Value="Zhou"],[Name="Age",Value=25],[Name="Country",Value="CHINA"]}),//進(jìn)行行列轉(zhuǎn)置，結(jié)果保存到t2中

t2=Table.Transpose(t1),res=t2inres205.2.2提升表格數(shù)據(jù)第一行為列標(biāo)題很多時(shí)候從外部導(dǎo)入PowerBI的數(shù)據(jù)構(gòu)成表格后，第一行不是數(shù)據(jù)本身，而是代表列標(biāo)題，此時(shí)可以通過Table.PromoteHeaders()函數(shù)將表格的第一行數(shù)據(jù)提升為列標(biāo)題。該函數(shù)的語法形式為：Table.PromoteHeaders(tableastable,optionaloptionsasnullablerecord)astable參數(shù)table是等待處理的表格，通常要求第一行數(shù)據(jù)是文本或數(shù)值類型的，否則的話，需要給出第二個(gè)參數(shù)；可選的參數(shù)options是一個(gè)記錄，用來說明是否轉(zhuǎn)換第一行的所有標(biāo)量值為列標(biāo)題，以及轉(zhuǎn)換時(shí)使用的文化背景格式；返回值是轉(zhuǎn)換得到的表格。215.2.3表格中列的數(shù)據(jù)類型的轉(zhuǎn)換可以使用Table.TransformColumnTypes()在需要是將表格中某列數(shù)據(jù)的類型轉(zhuǎn)換為期望的類型，當(dāng)然前提要求是這種轉(zhuǎn)換是可行的。該函數(shù)的語法形式是：Table.TransformColumnTypes(tableastable,typeTransformationsaslist,optionalcultureasnullabletext)astable參數(shù)table是等待處理的表格；參數(shù)ransformations是一個(gè)用來說明需要轉(zhuǎn)換類型的列及相應(yīng)的數(shù)據(jù)類型的列表；返回值是轉(zhuǎn)換列類型的表格。225.2.3表格中列的數(shù)據(jù)類型的轉(zhuǎn)換例如上圖中的表格t3，導(dǎo)入數(shù)據(jù)后年齡列暫時(shí)被識(shí)別為文本類型，可以使用Table.TransformColumnTypes()將其設(shè)置為整數(shù)類型，代碼如下，轉(zhuǎn)換后得到的表格如下圖5所示。t2=Table.TransformColumnTypes(t3,{"Age",Int32.Type})235.2.4將表格中值為記錄類型的數(shù)據(jù)進(jìn)行擴(kuò)展可以使用Table.ExpandRecordColumn()函數(shù)將單元值為記錄類型的數(shù)據(jù)進(jìn)行擴(kuò)展，該函數(shù)的語法形式如下：Table.ExpandRecordColumn(tableastable,columnastext,fieldNamesaslist,optionalnewColumnNamesasnullablelist)astable參數(shù)table是等待處理的表格;參數(shù)column是待擴(kuò)展的列名，該列的每個(gè)元素都是一個(gè)記錄數(shù)據(jù);參數(shù)fieldNames是一個(gè)列表，列表的元素對應(yīng)等待擴(kuò)展的記錄類型中的名稱，作為需要擴(kuò)展到列;參數(shù)newColumnNames給出擴(kuò)展生成的新列的列名。當(dāng)擴(kuò)展表格時(shí)，記錄數(shù)據(jù)會(huì)生成新的列，而非記錄數(shù)據(jù)的原始表格中的數(shù)據(jù)會(huì)根據(jù)當(dāng)前行中記錄擴(kuò)展的新行復(fù)制到每個(gè)新行中;返回值是擴(kuò)展后的表格。245.2.5將表格中值為列表類型的數(shù)據(jù)進(jìn)行擴(kuò)展可以使用Table.ExpandListColumn()函數(shù)將單元值為列表類型的數(shù)據(jù)進(jìn)行擴(kuò)展，該函數(shù)的語法形式如下：Table.ExpandListColumn(tableastable,columnastext)astable參數(shù)table是等待處理的表格；參數(shù)column是待擴(kuò)展的列名，該列的每個(gè)元素都是一個(gè)列表類型的數(shù)據(jù)，當(dāng)擴(kuò)展表格時(shí)，列表數(shù)據(jù)會(huì)生成新的列，而非列表數(shù)據(jù)的原始表格中的數(shù)據(jù)會(huì)根據(jù)當(dāng)前行中記錄擴(kuò)展的新行復(fù)制到每個(gè)新行中；返回值是擴(kuò)展后的表格。255.2.6刪除表格中的列當(dāng)表格中的某列不再需要時(shí)，可是使用Table.RemoveColumns()函數(shù)刪除該列，函數(shù)的語法形式是：Table.RemoveColumns(tableastable,columnsasany,optionalmissingFieldasnullablenumber)astable參數(shù)table是等待處理的表格；參數(shù)columns是待刪除的列名；返回值是刪除指定列后的表格。265.2.7拆分表格中的列可以使用Table.SplitColumn()函數(shù)將表格中的指定列按照給定的拆分函數(shù)拆分為若干個(gè)新列。該函數(shù)的語法形式為：Table.SplitColumn(tableastable,sourceColumnastext,splitterasfunction,optionalcolumnNamesOrNumberasany,optionaldefaultasany,optionalextraColumnsasany)astable參數(shù)table是等待拆分的表格；參數(shù)sourceColumn是等待拆分的列名；參數(shù)splitter是拆分指定列數(shù)據(jù)的拆分函數(shù)；返回值是拆分列后的表格。275.2.8獲取表格中的列名在處理過程中，可以獲取表格各列的列名，以方便后續(xù)的數(shù)據(jù)處理?？梢允褂肨able.ColumnNames()函數(shù)獲取作為參數(shù)的表格的各列列名，組織為一個(gè)文本列表返回，該函數(shù)的語法形式如下：Table.ColumnNames(tableastable)aslist285.3.1文本數(shù)據(jù)的獲取可以使用Csv.Document()從文本文件或CSV文件中獲取文本數(shù)據(jù)，該函數(shù)的語法形式如下：Csv.Document(sourceasany,optionalcolumnsasany,optionaldelimiterasany,optionalextraValuesasnullablenumber,optionalencodingasnullablenumber)astable參數(shù)source表示數(shù)據(jù)源文件名稱；參數(shù)columns給定想要獲取的列的信息，可以是列的數(shù)量或者列名等信息；參數(shù)delimiter給定同一行數(shù)據(jù)之間的分隔符號(hào)，默認(rèn)分隔符號(hào)是逗號(hào)；參數(shù)extraValues給出額外值類型；參數(shù)encoding給出文本文件的字符集編碼。除了第一個(gè)參數(shù)外的其他信息可以組織成一個(gè)記錄類型的參數(shù)。函數(shù)將獲取的數(shù)據(jù)以表格形式返回。295.3.1文本數(shù)據(jù)的獲取【例5-6】獲取一個(gè)以空格作為分隔符號(hào)，第一行數(shù)據(jù)表示標(biāo)題的文本文件數(shù)據(jù)，其內(nèi)容是2020年度中國大陸各省的農(nóng)作物產(chǎn)量信息。letsource=Csv.Document(File.Contents("D:\RESOURCES\agri.txt"),4,"",ExtraValues.Ignore,936),t1=Table.PromoteHeaders(source,[PromoteAllScalars=true]),t2=Table.TransformColumnTypes(t1,{{"地區(qū)",typetext},{"播種面積(千公頃)",Int64.Type},{"總產(chǎn)量(萬噸)",Int64.Type},{"單位面積產(chǎn)量(公斤/公頃)",Int64.Type}})int2305.3.2EXCEL數(shù)據(jù)的獲取可以使用File.Contents()和Excel.Workbook()配合從EXCEL文件中獲取文本數(shù)據(jù)，F(xiàn)ile.Contents()函數(shù)的語法形式如下：File.Contents(pathastext,optionaloptionsasnullablerecord)asbinary參數(shù)path表示帶獲取的數(shù)據(jù)的文件路徑，將文件內(nèi)容以二進(jìn)制形式讀取后返回。315.3.2EXCEL數(shù)據(jù)的獲取Excel.Workbook()函數(shù)的語法形式如下：Excel.Workbook(workbookasbinary,optionaluseHeadersasnullablelogical,optionaldelayTypesasnullablelogical)astable參數(shù)workbook表示數(shù)據(jù)源EXCEL文件內(nèi)容的二進(jìn)制映像；參數(shù)useHeaders說明是否使用標(biāo)題行；參數(shù)delayTypes表示是否延遲類型指定。函數(shù)的返回值是一個(gè)表格，EXCEL工作簿文件的每個(gè)工作表作為一個(gè)表格中的行被識(shí)別。325.3.2EXCEL數(shù)據(jù)的獲取【例5-7】獲取EXCEL文件中的產(chǎn)品銷售數(shù)據(jù)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,{{"產(chǎn)品",typetext},{"銷售量",Int64.Type},{"銷售額",Int64.Type}})int2335.3.3網(wǎng)頁數(shù)據(jù)的獲取可以使用File.Contents()和Web.Page()函數(shù)配合從網(wǎng)頁文檔中獲取文本數(shù)據(jù)，Web.Page()函數(shù)的語法形式如下：Web.Page(htmlasany)astable函數(shù)唯一的參數(shù)表示數(shù)據(jù)源網(wǎng)頁文件的HTML源代碼。函數(shù)的返回值是一個(gè)表格，網(wǎng)頁文檔本身以及網(wǎng)頁文檔中所包含的表格屬性及數(shù)據(jù)被識(shí)別分別作為結(jié)果表格的一行。當(dāng)需要后續(xù)處理時(shí)，通過指名提取該函數(shù)返回結(jié)果表格中的哪一行來獲取相應(yīng)的實(shí)際表格數(shù)據(jù)。345.3.3網(wǎng)頁數(shù)據(jù)的獲取【例5-8】獲取網(wǎng)頁文檔中包含的美元對一些國家貨幣的匯率信息。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文本數(shù)據(jù)的提取1. 提取文本中指定位置的一個(gè)字符可以使用Text.At()實(shí)現(xiàn)提取文本中指定位置的一個(gè)字符，該函數(shù)的語法形式如下：Text.At(textasnullabletext,indexasnumber)asnullabletext參數(shù)text是原文本；參數(shù)index是從0開始的下標(biāo)位置；返回值是單個(gè)字符構(gòu)成的文本數(shù)據(jù)。例如Text.At("china",2)返回”I”，Text.At("中國南京",2)返回“南”。365.4.1文本數(shù)據(jù)的提取2. 從文本開始位置向后提取指定數(shù)量的字符構(gòu)成子文本可以使用Text.Start()實(shí)現(xiàn)從文本開始位置向后提取指定數(shù)量的字符構(gòu)成子文本，該函數(shù)的語法形式如下：Text.Start(textasnullabletext,countasnumber)asnullabletext參數(shù)text是原文本；參數(shù)count是提取的字符數(shù)量。例如Text.Start("中國南京",2)返回“中國”。375.4.1文本數(shù)據(jù)的提取3. 從文本結(jié)尾位置向前提取指定數(shù)量的字符構(gòu)成子文本可以使用Text.End()實(shí)現(xiàn)3. 從文本結(jié)尾位置向前提取指定數(shù)量的字符構(gòu)成子文本，該函數(shù)的語法形式如下：Text.End(textasnullabletext,countasnumber)asnullabletexttext參數(shù)是原文本；參數(shù)count是提取的字符數(shù)量。例如Text.End("中國南京",2)返回“南京”。385.4.1文本數(shù)據(jù)的提取4.從文本中間某個(gè)位置開始提取指定數(shù)量的字符構(gòu)成子文本可以使用Text.Middle()實(shí)現(xiàn)從文本中間某個(gè)位置開始提取指定數(shù)量的字符構(gòu)成子文本，該函數(shù)的語法形式如下：Text.Middle(textasnullabletext,startasnumber,optionalcountasnullablenumber)asnullabletext參數(shù)text是原文本；參數(shù)start提取的起始位置，通過從0開始的下標(biāo)表示；可選的參數(shù)count是提取的字符數(shù)量，如果缺省的話表示一直提取到文本結(jié)束。例如Text.Middle("中國江蘇南京",2,2)返回“江蘇”，而Text.Middle("中國江蘇南京",2)則返回“江蘇南京”。395.4.2文本拆分可以使用Text.Split()函數(shù)將子文本通過拆分的方式從原文本中提取出來，該函數(shù)的語法形式如下：Text.Split(textastext,separatorastext)aslist參數(shù)text是待拆分的原文本；參數(shù)separator是作為分隔符號(hào)的文本；返回值是拆分出來的子文本構(gòu)成的列表。在表格處理中，將一列文本數(shù)據(jù)根據(jù)分隔符號(hào)拆分為多列數(shù)據(jù)時(shí)需要使用Text.Split()函數(shù)處理每一行上的原始文本數(shù)據(jù)。該函數(shù)的一個(gè)示例可以是：通過使用Text.Split("86,"-")實(shí)現(xiàn)將完整的電話號(hào)碼文本拆分為國家代碼“86”、地區(qū)編碼“025”和座機(jī)號(hào)碼“80008888”三個(gè)部分，返回值是一個(gè)列表{“86”,”025”,”80008888”}。405.4.3文本合并有時(shí)候需要將多個(gè)文本合并成一段完整的文本，此時(shí)可以使用Text.Combine()函數(shù)完成文本的合并任務(wù)。在表格數(shù)據(jù)處理中，如果需要將多列數(shù)據(jù)合并為一列，可以根據(jù)情況將非文本數(shù)據(jù)轉(zhuǎn)換為文本類型，然后使用Text.Combine()函數(shù)完成每一行上數(shù)據(jù)的合并任務(wù)。該函數(shù)的語法形式如下：Text.Combine(textsaslist,optionalseparatorasnullabletext)astext參數(shù)texts是待合并的多個(gè)文本構(gòu)成的列表；可選的參數(shù)separator是合并時(shí)用作分隔各個(gè)子文本的分隔符號(hào)，通過分隔符號(hào)在需要時(shí)可以將數(shù)據(jù)再拆分出來，當(dāng)?shù)诙€(gè)參數(shù)缺省時(shí)，所有子文本無分隔的被合并為完整的文本。例如使用Text.Combine({"86","025","80008888"},"-")可以得到文本"86，而使用Text.Combine({"86","025","80008888"})可以得到文本"8602580008888"。415.4.4文本處理案例—身份證號(hào)碼解析身份證號(hào)碼的解析。中國大陸地區(qū)的身份證號(hào)碼是典型的具有編碼格式的文本。下面嘗試從“數(shù)據(jù)處理素材.xlsx”中導(dǎo)入“身份證號(hào)碼”信息，在此查詢基礎(chǔ)上，根據(jù)身份證號(hào)碼，生成“地區(qū)編碼”、“生日”和“性別”信息。在身份證文本中，前6個(gè)字符構(gòu)成代表地區(qū)的編碼文本；中間6到8位代表生日，需要注意15位老身份證和18位新身份證的區(qū)別，15位老身份證7到12位是生日，需加年代“19”，18位身份證7到14位是生日；可以根據(jù)15位老身份證最后一位和18位身份證倒數(shù)第二位確定性別，奇數(shù)為男性，偶數(shù)為女性。425.4.4文本處理案例—身份證號(hào)碼解析解決思路：（1）問題的求解方案，是根據(jù)數(shù)據(jù)表格中“身份證號(hào)”數(shù)據(jù)列，設(shè)計(jì)算法產(chǎn)生“地區(qū)編碼”、“生日”和“性別”三個(gè)新的數(shù)據(jù)列。因此可以考慮設(shè)計(jì)三個(gè)函數(shù)，以每一行中“身份證號(hào)”數(shù)據(jù)為輸入，分別產(chǎn)生“地區(qū)編碼”、“生日”和“性別”三列所對應(yīng)的數(shù)據(jù)。（2）對于“地區(qū)編碼”信息：算法思想是從左其獲取“身份證號(hào)”文本的六個(gè)字符構(gòu)成“地區(qū)編碼”文本子串即可，因此可以應(yīng)用文本函數(shù)中的TEXT.START()處理。（3）對于“生日”信息：可以使用文本函數(shù)中的TEXT.MIDDLE()獲取處于“身份證號(hào)”文本中間的生日子串，但是需要根據(jù)“身份證號(hào)”文本的長度判斷是18位新身份證號(hào)還是15位老身份證號(hào)，新老身份證號(hào)需要獲取的文本子串起始位置和長度都不同，而且對于老身份證號(hào)還需要增補(bǔ)“19”年代子串在生日子串的前面；當(dāng)生成生日文本子串后，再使用類型轉(zhuǎn)換函數(shù)Date.FromText()將生日子串轉(zhuǎn)換為日期類型的數(shù)據(jù)。這是一個(gè)包含分支判斷的操作序列，單獨(dú)一個(gè)M語言函數(shù)無法完成任務(wù)，因此可以考慮編寫一個(gè)自定義函數(shù)來完成“生日”信息的生成。435.4.4文本處理案例—身份證號(hào)碼解析解決思路：（4）對于“性別”信息：可以由“身份證號(hào)”文本中的一個(gè)字符來進(jìn)行判斷，因此可以考慮使用Text.At()函數(shù)實(shí)現(xiàn)單個(gè)字符的提取，但是新老身份證性別字符的位置是不同的，因此需要分情況進(jìn)行判斷處理；當(dāng)提取到性別字符后，需要根據(jù)其奇偶性判斷，因此需要將文本轉(zhuǎn)換為數(shù)值，可以考慮使用Number.FromText()進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，再使用數(shù)值函數(shù)Number.Mod()進(jìn)行奇偶性判斷。這些任務(wù)也需要編寫一個(gè)自定義函數(shù)來完成。445.4.4文本處理案例—身份證號(hào)碼解析455.5.1數(shù)值處理常用函數(shù)1. 數(shù)值的舍入運(yùn)算可以使用Number.Round()函數(shù)實(shí)現(xiàn)實(shí)數(shù)的舍入運(yùn)算，該函數(shù)的語法形式如下：Number.Round(numberasnullablenumber,optionaldigitsasnullablenumber,optionalroundingModeasnullablenumber)asnullablenumber參數(shù)number是需要舍入的實(shí)數(shù)；可選的參數(shù)是舍入發(fā)生的小數(shù)點(diǎn)位置digits，如果省略的話number被舍入到最近的整數(shù)；可選的參數(shù)是舍入的方向roundingMode，當(dāng)其值為RoundingMode.Up時(shí)表示向上舍入，當(dāng)其值為RoundingMode.Down時(shí)表示向上舍入，注意該參數(shù)只在待舍入小數(shù)位置的下一位為5時(shí)起作用，當(dāng)不等于5時(shí)還是按照小于5向下舍入，大于5向上進(jìn)位處理；函數(shù)的返回值是根據(jù)參數(shù)要求舍入后的數(shù)值，如果傳入的第一個(gè)參數(shù)number的值是空值，則返回值也是空值。465.5.1數(shù)值處理常用函數(shù)例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數(shù)值處理常用函數(shù)2. 乘冪和對數(shù)運(yùn)算可以使用Number.Power()函數(shù)實(shí)現(xiàn)乘冪運(yùn)算，該函數(shù)的語法形式如下：Number.Power(numberasnullablenumber,powerasnullablenumber)asnullablenumber函數(shù)的返回值是number的power次方，如果兩個(gè)參數(shù)中有一個(gè)為空值，則函數(shù)返回空值。例如Number.Power(10,3)返回1000，Number.Power(1.5,2)返回2.25。注意求自然常數(shù)e的乘冪可以使用Number.Exp()函數(shù)。485.5.1數(shù)值處理常用函數(shù)可以使用Number.Log()函數(shù)實(shí)現(xiàn)求對數(shù)運(yùn)算，，該函數(shù)的語法形式如下：Number.Log(numberasnullablenumber,optionalbaseasnullablenumber)asnullablenumber函數(shù)的返回值是以base為底的number的對數(shù)，如果第二個(gè)參數(shù)缺省，則缺省值為自然常數(shù)e，如果第一個(gè)參數(shù)number的值為空值，則函數(shù)返回空值。例如Number.Log(100,10)返回2，Number.Log(256,2)返回8，Number.Log(2.718)的值為0.999896315728952。也可以使用Number.Ln()求自然對數(shù)，使用Number.Log10()求10為底的對數(shù)。495.5.2數(shù)值處理案例——計(jì)算銷售業(yè)績獎(jiǎng)金在“員工銷售數(shù)據(jù).xlsx”文件中的“員工數(shù)據(jù)”表中提供了某企業(yè)員工的個(gè)人情況和銷售情況，現(xiàn)在需要計(jì)算每個(gè)員工的基礎(chǔ)工資、銷售獎(jiǎng)金和總收入，并根據(jù)如下規(guī)則產(chǎn)生三個(gè)新列，基礎(chǔ)工資列根據(jù)下圖的規(guī)則由文化程度列生成，獎(jiǎng)金列根據(jù)下圖的規(guī)則由銷售業(yè)績列生成，實(shí)際收入列根據(jù)“基礎(chǔ)工資+獎(jiǎng)金”的規(guī)則生成。505.5.2數(shù)值處理案例——計(jì)算銷售業(yè)績獎(jiǎng)金解決思路：可以為基礎(chǔ)工資的計(jì)算和銷售獎(jiǎng)金的計(jì)算各設(shè)計(jì)一個(gè)函數(shù)來實(shí)現(xiàn)。具體實(shí)現(xiàn)代碼：letbasesalary=(diplomaastext)=>let salary=ifdiploma="博士"then5200 elseifdiploma="碩士"then4700 elseifdiploma="本科"then4100 else3200in salary,515.5.2數(shù)值處理案例——計(jì)算銷售業(yè)績獎(jiǎng)金compute_bonus=(salesasnumber)=>let bonus=ifsales<40000thensales*0.05 elseifsales>=40000andsales<50000thensales*0.08 elsesales*0.1in bonus,525.5.2數(shù)值處理案例——計(jì)算銷售業(yè)績獎(jiǎng)金Source=Excel.Workbook(File.Contents("D:\RESOURCES\員工銷售數(shù)據(jù).xlsx"),null,true),

員工數(shù)據(jù)_Sheet=Source{[Item="員工數(shù)據(jù)",Kind="Sheet"]}[Data],#"PromotedHeaders"=Table.PromoteHeaders(員工數(shù)據(jù)_Sheet,[PromoteAllScalars=true]),#"ChangedType"=Table.TransformColumnTypes(#"PromotedHeaders",{{"工號(hào)",Int64.Type},{"姓名",typetext},{"身份證號(hào)碼",typetext},{"文化程度",typetext},{"銷售業(yè)績",Int64.Type}}),table1=Table.AddColumn(#"ChangedType","基礎(chǔ)工資",eachbasesalary([文化程度])),table2=Table.AddColumn(table1,"獎(jiǎng)金",eachcompute_bonus([銷售業(yè)績])),table3=Table.AddColumn(table2,"實(shí)際收入",each([基礎(chǔ)工資]+[獎(jiǎng)金]))intable3535.6.1日期和時(shí)間的生成用于日期和時(shí)間生成的M語言函數(shù)及其說明如表所示54函數(shù)DateTime.LocalNow()asdatetime作用返回由系統(tǒng)時(shí)間得到的調(diào)用時(shí)刻的日期時(shí)間示例DateTime.LocalNow()函數(shù)#date(yearasnumber,monthasnumber,dayasnumber)asdate作用由給定的年、月、日分量構(gòu)造一個(gè)日期數(shù)據(jù)示例#date(2019,10,28)返回日期"2019-10-28"函數(shù)#time(hourasnumber,minuteasnumber,secondasnumber)astime作用由給定的時(shí)、分、秒分量構(gòu)造一個(gè)時(shí)間數(shù)據(jù)示例#time(9,30,0)返回時(shí)間"9:30:00"函數(shù)#datetime(yearasnumber,monthasnumber,dayasnumber,hourasnumber,minuteasnumber,secondasnumber)asany作用由給定的年、月、日、時(shí)、分、秒分量構(gòu)造一個(gè)日期時(shí)間型數(shù)據(jù)示例#datetime(2019,10,28,9,30,0)返回日期時(shí)間數(shù)據(jù)"2019-10-289:30:00"函數(shù)DateTime.Date(dateTimeasany)asnullabledate作用根據(jù)給出的參數(shù)得到其日期部分示例DateTime.Date(#datetime(2019,10,28,9,30,0))返回日期"2019-10-28"函數(shù)DateTime.Time(dateTimeasany)asnullabletime作用根據(jù)給出的參數(shù)得到其時(shí)間部分示例DateTime.Time(#datetime(2019,10,28,9,30,0))返回時(shí)間"9:30:00"5.6.2取日期和時(shí)間分量能夠完成取日期和時(shí)間分量的函數(shù)及其說明如表所示55函數(shù)Date.Year(dateTimeasany)asnullablenumber作用取年份分量示例Date.Year(Date.FromText("2019-10-28"))返回年份2019函數(shù)Date.Month(dateTimeasany)asnullablenumber作用取月份分量示例Date.Month(Date.FromText("2019-10-28"))返回月份10函數(shù)Date.Day(dateTimeasany)asnullablenumber作用取日子分量示例Date.Day(Date.FromText("2019-10-28"))返回具體日子28函數(shù)Time.Hour(dateTimeasany)asnullablenumber作用取小時(shí)分量示例Time.Hour(DateTime.FromText("2019-10-28T09:30:00"))返回小時(shí)分量9函數(shù)Time.Minute(dateTimeasany)asnullablenumber作用取分鐘分量示例Time.Minute(DateTime.FromText("2019-10-28T09:30:00"))返回小時(shí)分量30函數(shù)Time.Second(dateTimeasany)asnullablenumber`作用取秒分量示例Time.Second(DateTime.FromText("2019-10-28T09:30:00"))返回小時(shí)分量05.6.3日期時(shí)間的計(jì)算1. 日期偏移的計(jì)算56函數(shù)Date.AddDays(dateTimeasany,numberOfDaysasnumber)asany作用以第一個(gè)參數(shù)為基準(zhǔn)，偏移第二個(gè)參數(shù)給定的天數(shù)后的日期。示例Date.AddDays(Date.FromText("2019-10-28"),5)返回日期"2019-11-2"Date.AddDays(Date.FromText("2019-10-28"),-2)返回日期"2019-10-26"函數(shù)Date.AddMonths(dateTimeasany,numberOfMonthsasnumber)asany作用以第一個(gè)參數(shù)為基準(zhǔn)，偏移第二個(gè)參數(shù)給定的月份數(shù)后的日期。示例Date.AddMonths(Date.FromText("2019-10-28"),1)返回日期"2019-11-28"函數(shù)Date.AddQuarters(dateTimeasany,numberOfQuartersasnumber)asany作用以第一個(gè)參數(shù)為基準(zhǔn)，偏移第二個(gè)參數(shù)給定的季度數(shù)后的日期。示例Date.AddQuarters(Date.FromText("2019-10-28"),1)返回日期"2020-1-28"函數(shù)Date.AddWeeks(dateTimeasany,numberOfWeeksasnumber)asany作用以第一個(gè)參數(shù)為基準(zhǔn)，偏移第二個(gè)參數(shù)給定的星期數(shù)后的日期。示例Date.AddWeeks(Date.FromText("2019-10-28"),2)返回日期"2019-11-11"函數(shù)Date.AddYears(dateTimeasany,numberOfYearsasnumber)asany作用以第一個(gè)參數(shù)為基準(zhǔn)，偏移第二個(gè)參數(shù)給定的年份數(shù)后的日期。示例Date.AddYears(Date.FromText("2019-10-28"),1)返回日期"2020-10-28"5.6.3日期時(shí)間的計(jì)算2. 日期定位的計(jì)算57函數(shù)Date.DayOfWeek(dateTimeasany,optionalfirstDayOfWeekasnullablenumber)asnullablenumber作用計(jì)算第一個(gè)參數(shù)給定的日期是所在星期的第幾天，返回值在0到6之間，第二個(gè)可選參數(shù)表示第一天的基準(zhǔn)，可以使用Day.Sunday,Day.Monday等，如果不給的話，缺省值取決于系統(tǒng)所在國家的文化習(xí)慣。示例在中國大陸地區(qū)，Date.DayOfWeek(Date.FromText("2019-10-28"))返回0Date.DayOfWeek(Date.FromText("2019-10-28"),Day.Sunday)返回1函數(shù)Date.DayOfWeekName(dateasany,optionalcultureasnullabletext)作用計(jì)算第一個(gè)參數(shù)給定的日期是星期幾，第二個(gè)可選參數(shù)是表示文化習(xí)慣的文本示例Date.DayOfWeekName(Date.FromText("2019-10-28"))返回”星期一”Date.DayOfWeekName(Date.FromText("2019-10-28"),"en-us")返回”Monday”函數(shù)Date.DayOfYear(dateTimeasany)asnullablenumber作用計(jì)算第一個(gè)參數(shù)給定的日期是所在年份的第幾天示例Date.DayOfYear(Date.FromText("2019-10-28"))返回301函數(shù)Date.WeekOfYear(dateTimeasany,optionalfirstDayOfWeekasnullablenumber)asnullablenumber作用計(jì)算第一個(gè)參數(shù)給定的日期是所在年份的第幾個(gè)星期，第二個(gè)可選的參數(shù)表示以星期幾作為一個(gè)星期的開始，可以使用Day.Sunday,Day.Monday等，如果不給的話，缺省值取決于系統(tǒng)所在國家的文化習(xí)慣。示例Date.WeekOfYear(Date.FromText("2019-10-28"))返回44函數(shù)Date.QuarterOfYear(dateTimeasany)asnullablenumber作用計(jì)算第一個(gè)參數(shù)給定的日期是所在年份的第幾個(gè)季度示例Date.QuarterOfYear(Date.FromText("2019-10-28"))返回4函數(shù)Date.IsLeapYear(dateTimeasany)asnullablelogical作用判斷第一個(gè)參數(shù)給定的日期所在年份是否是閏年示例Date.IsLeapYear(Date.FromText("2019-10-28"))返回FALSE5.6.4日期時(shí)間數(shù)據(jù)處理案例有時(shí)候需要生成指定起止時(shí)間段內(nèi)的包含年、月、日、星期、季度等信息的日期表，例如在財(cái)務(wù)數(shù)據(jù)分析中需要借助日期表來進(jìn)行統(tǒng)計(jì)分析。生成日期表的關(guān)鍵在于首先根據(jù)起止時(shí)間段生成之間每一天的日期數(shù)據(jù)構(gòu)成的列表，然后轉(zhuǎn)換為表格，接著使用日期函數(shù)來根據(jù)日期數(shù)據(jù)列生成其他各個(gè)信息列。下面給出生成一個(gè)簡易日期表的相關(guān)代碼及其注釋，生成的日期表如圖所示。585.6.4日期時(shí)間數(shù)據(jù)處理案例let//qi'起始和截至日期，這里使用構(gòu)造方式演示，實(shí)際處理中可以從其他數(shù)據(jù)源得到

start=#date(2019,2,28),end=#date(2020,2,5),/*使用列表生成函數(shù)構(gòu)造包含每一天日期的列表，這里的用法是以start為起始值，在生成的列表元素值小于等于end值時(shí)，繼續(xù)使用Data.AddDays以1天為偏移量生成下一個(gè)列表元素。*/workdays=List.Generate(()=>start,each_<=end,eachDate.AddDays(_,1)),//轉(zhuǎn)換成只有一列“日期”的表格

cal1=Table.FromColumns({workdays},{"日期"}),//設(shè)置類型為datecal2=Table.TransformColumnTypes(cal1,{"日期",typedate}),595.6.4日期時(shí)間數(shù)據(jù)處理案例

//使用Date.Year()函數(shù)生成年份列

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

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

cal5=Table.AddColumn(cal4,"日",eachDate.Day([日期]),typenumber),//使用Date.DayOfWeek()函數(shù)生成星期列，表示該日期是星期幾

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

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

res=cal7inres605.7.1JSON數(shù)據(jù)的轉(zhuǎn)換和讀取JSON數(shù)據(jù)的轉(zhuǎn)換可以使用Json.FromValue()函數(shù)將不同類型的數(shù)據(jù)轉(zhuǎn)換為JSON對象，函數(shù)語法形式如下：Json.FromValue(valueasany,optionalencodingasnullablenumber)asbinary該函數(shù)將參數(shù)value給出的某種類型的值轉(zhuǎn)換為JSON表示，具體值的類型和轉(zhuǎn)換后得到的JSON表示如下所述：NULL，文本和邏輯值被表示為相應(yīng)的JSON類型；數(shù)字被表示為JSON中的數(shù)字，特殊常量##infinity,-#infinity和#nan會(huì)被轉(zhuǎn)換為空值；列表被表示為JSON數(shù)組；記錄被表示為JSON對象；表格被表示為對象數(shù)組；Dates,times,datetimes,datetimezones和durations類型的數(shù)據(jù)被表示為ISO-8601格式的文本；二進(jìn)制值被表示為基于64編碼的文本；如果傳入的不是數(shù)據(jù)，而是數(shù)據(jù)類型或函數(shù)，則會(huì)產(chǎn)生一個(gè)錯(cuò)誤。參數(shù)encoding表示使用的文本編碼，缺省值為UTF8。615.7.1JSON數(shù)據(jù)的轉(zhuǎn)換和讀取JSON數(shù)據(jù)的獲取可以使用Json.Document()函數(shù)讀取外部的JSON數(shù)據(jù)并轉(zhuǎn)換為相應(yīng)的類型，函數(shù)語法形式為：Json.Document(jsonTextasany,optionalencodingasnullablenumber)asany該函數(shù)將參數(shù)jsonText代表的JSON文本轉(zhuǎn)換為相應(yīng)的M語言內(nèi)部數(shù)據(jù)類型，對應(yīng)關(guān)系和Json.FromValue()函數(shù)說明中類似，例如JSON對象轉(zhuǎn)換為記錄，JSON數(shù)組轉(zhuǎn)換為列表。參數(shù)encoding表示使用的文本編碼，缺省值為UTF8。如果讀取JSON數(shù)據(jù)處理時(shí)出現(xiàn)亂碼，則需要考慮修改數(shù)據(jù)源中的字符集編碼設(shè)置。625.7.2JSON數(shù)據(jù)的整理1. 層次型JSON數(shù)據(jù)的轉(zhuǎn)換層次型JSON數(shù)據(jù)是具有樹形層次結(jié)構(gòu)的數(shù)據(jù)的JSON表達(dá)。635.7.2JSON數(shù)據(jù)的整理letstep1=Json.Document(File.Contents("D:\RESOURCES\city.json"),936),//第一步，讀取JSON數(shù)據(jù)，注意指定文本編碼，否則可能出現(xiàn)亂碼step2=Record.ToTable(step1),//第二步，將得到的記錄數(shù)據(jù)轉(zhuǎn)換為表格數(shù)據(jù)step3=Table.Transpose(step2),//第三步，表格行列轉(zhuǎn)置，理順標(biāo)題于數(shù)據(jù)之間的關(guān)系step4=Table.PromoteHeaders(step3,[PromoteAllScalars=true]),//第四步，將第一行數(shù)據(jù)提升為標(biāo)題，這里的標(biāo)題就是最頂層JSON對象中的名稱step5=Table.ExpandListColumn(step4,"province"),//第五步，擴(kuò)展第一層結(jié)點(diǎn)所屬的JSON數(shù)組對應(yīng)的列表，展開后的行數(shù)由原JSON數(shù)組元素個(gè)數(shù)決定//第六步，原數(shù)組中每個(gè)元素都是JSON對象，現(xiàn)在就是記錄，再次擴(kuò)展，根據(jù)記錄的名稱-值對情況擴(kuò)展為多列

step6=Table.ExpandRecordColumn(step5,"province",{"name","cities"},{"province","cities"}),step7=Table.ExpandRecordColumn(step6,"cities",{"city"},{"city"}),//第七步，擴(kuò)展后還是JSON對象，雖然只有一個(gè)名稱-值對，繼續(xù)擴(kuò)展step8=Table.ExpandListColumn(step7,"city"),//第八步，現(xiàn)在是列表，因?yàn)橹暗闹凳俏谋緮?shù)組，繼續(xù)將列表擴(kuò)展result=step8//處理完畢inresult645.7.2JSON數(shù)據(jù)的整理表格型JSON數(shù)據(jù)的轉(zhuǎn)換有些JSON數(shù)據(jù)中，包含多個(gè)名稱值對，每個(gè)名稱的值是一個(gè)屬性名，而對應(yīng)的值是一個(gè)數(shù)據(jù)序列，而每個(gè)名稱值對中值所對應(yīng)的數(shù)據(jù)序列的長度是相同的，因此這其實(shí)是一張表格數(shù)據(jù)的JSON表示，每個(gè)名稱值對代表的是表格中的一列。655.7.2JSON數(shù)據(jù)的整理let//讀取表格型JSON數(shù)據(jù)，得到一個(gè)記錄

step1=Json.Document(File.Contents("D:\RESOURCES\gdp.json"),936),//從記錄中得到所有的名稱，構(gòu)成一個(gè)列表

tmpnames=Record.FieldNames(step1),//從記錄中得到所有值，這里每個(gè)值都是一個(gè)列表，且元素個(gè)數(shù)相同，最終構(gòu)成一個(gè)列表的列表

step2=Record.ToList(step1),//使用列表的列表轉(zhuǎn)換得到一個(gè)表格，表格的列名由之前得到名稱列表指定

step3=Table.FromColumns(step2,tmpnames),//得到最終結(jié)果

result=step3inresult665.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理有的時(shí)候從外部數(shù)據(jù)源導(dǎo)入的數(shù)據(jù)并非完全結(jié)構(gòu)化的，這包括EXCEL文件、網(wǎng)頁文檔、JSON對象等。但是這些數(shù)據(jù)本身卻是可以通過業(yè)務(wù)邏輯結(jié)合數(shù)據(jù)表示格式進(jìn)行解析，并通過數(shù)據(jù)處理手段將其結(jié)構(gòu)化并存儲(chǔ)為PowerBI的數(shù)據(jù)表格對象。675.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理基本處理思路：（1）這里出現(xiàn)的數(shù)據(jù)是半結(jié)構(gòu)化的，體現(xiàn)在數(shù)據(jù)并非嚴(yán)格按照行列排列，每列代表一個(gè)屬性的數(shù)據(jù)，并具有相同的類型；（2）EXCEL工作表中的每個(gè)原子數(shù)據(jù)本質(zhì)上是存儲(chǔ)在一個(gè)單元格內(nèi)的，圖中所示杜邦分析效果是通過合并單元格、設(shè)置居中等單元格格式設(shè)置手段實(shí)現(xiàn)的，當(dāng)消除所有單元格格式設(shè)置后，可以看到最原始的每個(gè)數(shù)據(jù)所在的單元格位置。例如財(cái)務(wù)指標(biāo)名稱“凈資產(chǎn)收益率”位于A2單元格，也就是行列位置為第1列第2行，而該指標(biāo)的值7.63%位于A3單元格，也就是行列位置為第1列第3行。（3）EXCEL工作表被導(dǎo)入PowerBI后，轉(zhuǎn)換為表格對象后，其行列與EXCEL工作表中的行列是對應(yīng)的，因此這里的半結(jié)構(gòu)化EXCEL工作表轉(zhuǎn)換成PowerBI表格對象后，因?yàn)闃?biāo)題和數(shù)據(jù)沒有明顯的劃分結(jié)構(gòu)，因此所有數(shù)據(jù)都作為文本類型被識(shí)別，而沒有值的EXCEL工作表單元格對應(yīng)的PowerBI表格對象的表格對象單元格的值是空值。685.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理基本處理思路：（4）因此，對于一個(gè)EXCEL文件，可以通過在程序中提供原EXCEL工作表中各數(shù)據(jù)標(biāo)題和具體數(shù)值的位置，來在轉(zhuǎn)換后的PowerBI表格中提取相應(yīng)單元格的值，從而構(gòu)造標(biāo)題列表和數(shù)據(jù)列表，再轉(zhuǎn)換為PowerBI表格，實(shí)現(xiàn)一張半結(jié)構(gòu)化EXCEL表的結(jié)構(gòu)化處理，構(gòu)成最終表格數(shù)據(jù)中的一行。（5）為了使得能夠自動(dòng)化的獲取需要提取的數(shù)據(jù)及其標(biāo)題的位置，可以構(gòu)造一個(gè)模板EXCEL表，用于說明每個(gè)標(biāo)題、其對應(yīng)的數(shù)據(jù)以及提取后在PowerBI表格中的次序。這樣在將EXCEL表導(dǎo)入為PowerBI表后，可以沿著行方向表格拆分成一行一行，然后按照從首行到最后一行的次序?qū)⑺行泻喜⒊梢恍袛?shù)據(jù)，就可以使用PowerQuery的List.PositionOf()函數(shù)來自動(dòng)提取所有標(biāo)題和數(shù)據(jù)的位置，進(jìn)而使用得到的位置，在真正的財(cái)務(wù)數(shù)據(jù)表格中提取相關(guān)數(shù)據(jù)及其對應(yīng)的標(biāo)題。（6）可以通過對文件夾內(nèi)文件的逐個(gè)處理，獲取所有的企業(yè)數(shù)據(jù)表格，然后合并這些表格，再對每一列數(shù)據(jù)進(jìn)行合適的處理并指定正確的數(shù)據(jù)類型，即可得到最終結(jié)構(gòu)化后的所有企業(yè)的財(cái)務(wù)指標(biāo)數(shù)據(jù)表格。695.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理705.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理當(dāng)需要對一個(gè)文件夾里的文件逐個(gè)進(jìn)行處理時(shí)，可以將根據(jù)模板提取標(biāo)題和數(shù)據(jù)的單元格位置封裝為一個(gè)模塊，然后將處理一個(gè)數(shù)據(jù)文件的代碼也封裝為模塊，根據(jù)文件、標(biāo)題和數(shù)據(jù)的位置來實(shí)現(xiàn)數(shù)據(jù)的提取和轉(zhuǎn)換。像凈利潤這些數(shù)據(jù)的值包含中文的數(shù)量單位“億”，因此并不是數(shù)值數(shù)據(jù)，而是文本數(shù)據(jù)，這是無法應(yīng)用到后續(xù)的數(shù)據(jù)分析中的，因此需要按照業(yè)務(wù)邏輯含義對得到的表格對象的每一列數(shù)據(jù)進(jìn)行合適的處理，再設(shè)置最終的數(shù)據(jù)類型，得到轉(zhuǎn)換成功的數(shù)據(jù)表。715.8數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理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"標(biāo)題"&Number.ToText(_)), title_pos=List.Combine(List.Transform(titletext,eachList.PositionOf(model_t3,_,List.Count(model_t3)))), datatext=List.Transform({1..posct},each"數(shù)據(jù)"&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數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理 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數(shù)據(jù)處理綜合案例——半結(jié)構(gòu)化EXCEL數(shù)據(jù)的處理 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