緣起

就像我很多年都沒搞懂Excel數(shù)據(jù)透視表一樣慎框，我對PowerQuery的透視和逆透視也是一直迷迷糊糊烁焙。直到今天遇到一個實(shí)際的例子重窟。

我更新windows10時，從微軟網(wǎng)站上下載了一個關(guān)于此更新的文件信息csv文件妒穴。一時興起，想拿這個文件來練練手摊崭。

文件鏈接見：http://pan.baidu.com/share/link?shareid=2260904732&uk=3579801784

文件分析

這是一個csv文件讼油，我用Excel打開之后，發(fā)現(xiàn)這個文件其實(shí)分為兩部分：一部分是很標(biāo)準(zhǔn)的表格形式：

第一部分的格式.PNG

這種格式很好處理呢簸。

從第32544行開始矮台，數(shù)據(jù)變成另一種格式：

第32544行開始的數(shù)據(jù).PNG

我準(zhǔn)備建立三個查詢：一個是原始數(shù)據(jù)，一個是第一部分?jǐn)?shù)據(jù)根时，再一個是第二部分?jǐn)?shù)據(jù)瘦赫。

原始數(shù)據(jù)處理思路

第一部分?jǐn)?shù)據(jù)，從前三行都是無效數(shù)據(jù)蛤迎，因此可以通過Table.Skip()來跳過前三行确虱。
這里有個奇怪的地方是，我導(dǎo)入原始數(shù)據(jù)時替裆，用的是從csv文件導(dǎo)入校辩，但是PowerQuery居然不能正確識別分隔符，導(dǎo)致所有數(shù)據(jù)都在同一列辆童，我表示不知道為什么【2016年11月14日補(bǔ)充：應(yīng)該是PowerQuery老版本的bug之類的問題宜咒，我剛才將Office2016升級到1610.7466.2038后問題解決】。所以只好按文本處理把鉴，然后手動分列荧呐，最終代碼如下：

let
源 = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\wangh\Downloads\3197954.csv"))}),
用分隔符分列 = Table.SplitColumn(源,"Column1",Splitter.SplitTextByDelimiter(","),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9"}),
刪除的頂端行 = Table.Skip(用分隔符分列,3),
提升的標(biāo)題 = Table.PromoteHeaders(刪除的頂端行)
in
提升的標(biāo)題

第一部分?jǐn)?shù)據(jù)處理思路

第一部分?jǐn)?shù)據(jù)在原始數(shù)據(jù)查詢的基礎(chǔ)上進(jìn)行引用，這樣原始數(shù)據(jù)將來萬一需要任何修改的話纸镊，可以很方便地將修改結(jié)果反映到第一部分?jǐn)?shù)據(jù)處理這個查詢上倍阐。

這里的難點(diǎn)是如何安全高效剔除第二部分?jǐn)?shù)據(jù)。當(dāng)然可以加載到excel表格中逗威，手動找到第二部分開始的前一行的行號峰搪，然后回到查詢編輯器里，用Table.FirstN()來篩選出第一部分?jǐn)?shù)據(jù)凯旭。但這種辦法只適用于處理這一個文件概耻。PowerQuery的魅力在于流程化處理滿足條件的所有文件而不是某個特定文件，因此罐呼，需要用更動態(tài)的法子來獲取截斷處的行號鞠柄。我用的是：

List.PositionOf(Table.Column(源,"File name"),"Additional file information")

因?yàn)橥ㄟ^觀察發(fā)現(xiàn)，截斷處附近包含文本"Additional file information"嫉柴⊙岫牛可以合理假設(shè)其他微軟的類似文件也都有這句話，所以找到這句話所在的行號，就可以截取了夯尽。

第一部分?jǐn)?shù)據(jù)處理的完整代碼如下：

let
源 = 原始,
保留的行 = Table.FirstN(源,List.PositionOf(Table.Column(源,"File name"),"Additional file information")),
已刪除的空行 = Table.SelectRows(保留的行, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
已刪除的空行

第二部分?jǐn)?shù)據(jù)處理思路

第二部分的數(shù)據(jù)有兩種方法可以獲得瞧壮，一是反向合并兩個表格，二是老老實(shí)實(shí)利用處理第一部分?jǐn)?shù)據(jù)的思路來獲取匙握。我采取的是第二種方法咆槽。

這里要注意的是，第二部分?jǐn)?shù)據(jù)開始的第一個數(shù)據(jù)片段圈纺，跟后面的數(shù)據(jù)片段相比秦忿，少了一行，所以蛾娶，這個片段是無效數(shù)據(jù)灯谣，需要剔除。我用了Table.Skip()來剔除茫叭。

接下來處理掉每個數(shù)據(jù)片段的空格。直接在UI上選擇刪除空行半等。

接下來就到了這個例子中最復(fù)雜的部分了揍愁。
數(shù)據(jù)都是每7行、每兩列為一條完整的記錄杀饵，因此莽囤，我們需要將六行兩列變成一行六列的有效記錄。

我們知道行變列可以用轉(zhuǎn)置的方法切距，但是在這個例子是行不通的朽缎，因?yàn)椴⒉皇呛唵蔚男辛修D(zhuǎn)換問題。

這里需要Table.Pivot()函數(shù)谜悟。

在開始透視之前话肖，還有一個額外的步驟要做，就是為每一條記錄設(shè)置一個ID葡幸。如果不設(shè)置這個ID最筒，直接透視，PowerQuery會報如下錯誤：

Expression.Error: 枚舉中用于完成該操作的元素過多蔚叨。

為什么會這樣床蜘，我還不清楚，我只知道獲取記錄的ID之后蔑水，就不會報錯邢锯。

獲取ID的第一步是為表格添加索引，可以直接在UI的“添加列”選項(xiàng)卡找到“添加索引列”命令搀别，我們添加一個開始為1的索引列丹擎。之所以開始為1，主要是我個人愛好——覺得這樣好理解歇父。其實(shí)在后面我們還要通過運(yùn)算將索引變?yōu)?開始才能正確獲取記錄的ID鸥鹉。

接下來是再添加一列蛮穿，獲取ID：

 ID = Table.AddColumn(已添加索引, "ID", each Number.IntegerDivide([索引]-1,6)),

可以看到將索引列減1就是將其變成從0開始。
這里這個Number.IntegerDivide()很過癮毁渗，就是返回兩個相除之后商的整數(shù)部分践磅。由于每一條記錄的六行所在的索引號減去1之后除以6，獲取的商的整數(shù)部分都相同灸异，因此府适，可以判斷出這里的整數(shù)商就是判斷是否同一條記錄的依據(jù)，因此這是獲取ID的關(guān)鍵步驟肺樟。

注意獲取ID之后有一個重命名表格列名稱的步驟檐春，原因在于我們整理原始數(shù)據(jù)時將列標(biāo)題處理好了，但是現(xiàn)在第二部分?jǐn)?shù)據(jù)的列標(biāo)題需要從行里獲取么伯，如果獲取之后的列標(biāo)題和已有列標(biāo)題重復(fù)疟暖，PowerQuery會報錯。這個步驟可以在導(dǎo)入源之后的操作及Table.Pivot()操作中間的任何一步進(jìn)行田柔。

接下來是選取我們需要的列俐巴，刪除那些不需要的列。

到這一步硬爆，我們才真正開始用Table.Pivot()函數(shù)來獲取數(shù)據(jù)：

已透視列 = Table.Pivot(刪除的其他列,List.Distinct(Table.Column(刪除的其他列,"Column1")), "Column1","Column2"),

我個人一直認(rèn)為這個Table.Pivot()函數(shù)很不好理解欣舵，事實(shí)上我也是折騰了好久才偶然弄出我想要的結(jié)果來。

第一個參數(shù)“刪除的列”是要透視的表格缀磕，這個沒什么好說缘圈。
第二個參數(shù)是要將行里的數(shù)據(jù)提取成為透視后的表格列標(biāo)題。在此例中袜蚕，Column1里邊的數(shù)據(jù)就是我們要變成列標(biāo)題的數(shù)據(jù)糟把。由于這一列的所有數(shù)據(jù)都是六個標(biāo)題行的重復(fù)，因此我們需要獲取不重復(fù)值牲剃，因此

List.Distinct(Table.Column(刪除的其他列,"Column1"))

得到的其實(shí)是一個有6行的list糊饱。這個list的每一個元素將作為透視后的表格的每一列。

列標(biāo)題.PNG

【注意截圖中其實(shí)是7行颠黎。最后那一行x64 Windows10我表示很懵比柑晒，不知道它從哪里鉆出來的蒋情，后來檢查原始數(shù)據(jù)筹麸，才知這個第二部分的數(shù)據(jù)其實(shí)也是分了兩部分：x86和x64兩部分鹦马。鑒于我太懶，而且x64部分刪除空格后过椎，它的第一個片段也是5行室梅，加上x64 Windows10這一行，還是構(gòu)成一個完整的6行，并不會導(dǎo)致數(shù)據(jù)發(fā)生紊亂亡鼠，所以懶得對它進(jìn)行單獨(dú)處理了赏殃。】

回到Table.Pivot()函數(shù)上來间涵。

第三個和第四個參數(shù)其實(shí)就是原始表格中仁热，記錄（Record）的屬性（Attribute）和值（Value）。透視后勾哩，產(chǎn)生的每一條記錄抗蠢，其屬性來源于第三個參數(shù)，其值來源于第四個參數(shù)思劳。在這個例子中迅矛，每一條記錄的屬性都在原始表格第二部分?jǐn)?shù)據(jù)的第一列，值都在第二列潜叛。

屬性和值

let
源 = 原始,
刪除的頂端行 = Table.Skip(源,List.PositionOf(Table.Column(源,"File name"),"Additional file information")+4),
刪除的列 = Table.RemoveColumns(刪除的頂端行,{"File size", "Date", "Time", "Platform", "SP requirement", "Service branch", ""}),
刪除的頂端行1 = Table.Skip(刪除的列,6),
已刪除的空行 = Table.SelectRows(刪除的頂端行1, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
已添加索引 = Table.AddIndexColumn(已刪除的空行, "索引", 1, 1),
ID = Table.AddColumn(已添加索引, "ID", each Number.IntegerDivide([索引]-1,6)),
重命名的列 = Table.RenameColumns(ID,{{"File name", "Column1"}, {"File version", "Column2"}}),
刪除的其他列 = Table.SelectColumns(重命名的列,{"ID", "Column1", "Column2"}),
已透視列 = Table.Pivot(刪除的其他列,List.Distinct(Table.Column(刪除的其他列,"Column1")), "Column1","Column2"),
刪除的列1 = Table.RemoveColumns(已透視列,{"x64 Windows 10", "ID"})
in
刪除的列1