Power Query多層表頭數(shù)據(jù)表的自動(dòng)追加
點(diǎn)擊藍(lán)字
關(guān)注我們
在工作中，我們經(jīng)常會(huì)遇到Excel表中的有多表頭的情況，見下圖。此類表格需要被轉(zhuǎn)換為標(biāo)準(zhǔn)結(jié)構(gòu)化后才可被進(jìn)一步分析。
本文將會(huì)向大家演示在Power Query界面中對(duì)此表進(jìn)行數(shù)據(jù)整理的過程。
1
單表多表頭轉(zhuǎn)換
首先，用 Power BI導(dǎo)入數(shù)據(jù)表并進(jìn)入“編輯查詢”模式。
單擊轉(zhuǎn)換-轉(zhuǎn)置命令，將表中的行與列進(jìn)行轉(zhuǎn)置。此時(shí)的“Column1”與“Column2”分別變?yōu)榱恕澳辍焙汀霸隆?/p>
同時(shí)選中“Column1”與“Column2”，單擊轉(zhuǎn)換-合并列命令，將其合成為一字段
再次利用轉(zhuǎn)置功能，將表格恢復(fù)至初始的狀態(tài)。原來的“年”、“月”兩層表頭已經(jīng)合成為一層表頭。
單擊菜單的主頁(yè)-將第一行用作標(biāo)題命令，將第一行內(nèi)容提示為字段標(biāo)題。
選擇“國(guó)家”與“科目”兩個(gè)字段，在菜單中單擊轉(zhuǎn)換-逆透視列-逆透視其他列命令。
逆透視完成后，修改對(duì)應(yīng)的字段名稱，就可以實(shí)現(xiàn)以下效果。
2
多表頭的文件合并
如果在工作中遇到工作表中有多個(gè)多表頭的情況，見下圖，則處理會(huì)稍微復(fù)雜一些。具體的處理思路為使用函數(shù)逐個(gè)處理單文件格式，最后將其合并至一個(gè)文件中。
首先，單擊單表處理中的“高級(jí)編輯器”命令，選取下圖框中的代碼，復(fù)制至記事簿中：
轉(zhuǎn)置表 = Table.Transpose(更改的類型),
合并的列 = Table.CombineColumns(轉(zhuǎn)置表,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"已合并"),
轉(zhuǎn)置表1 = Table.Transpose(合并的列),
提升的標(biāo)題 = Table.PromoteHeaders(轉(zhuǎn)置表1, [PromoteAllScalars=true]),
上述代碼為M代碼，總是以let…in的格式出現(xiàn)，其中的每一行代碼將表操作返回變量，又成為下一行代碼的輸入，如此嵌套循環(huán)，直至in后返回最終的轉(zhuǎn)換結(jié)果。需要復(fù)制的代碼功能相當(dāng)于對(duì)單表的操作。打開一個(gè)新的Power BI文件，參照前文示例，獲取文件夾數(shù)據(jù)，并指向多表頭文件所在路徑。導(dǎo)入成功后，在“編輯查詢”界面左側(cè)版面右擊菜單中單擊“新建查詢”-“空查詢”命令
將空查詢改名為“轉(zhuǎn)換”，并單擊“高級(jí)編輯器”命令，在彈出的文本框內(nèi)貼入記事簿中的代碼，并進(jìn)行一定的修改
上述代碼中的的重點(diǎn)變化是：
1. “（t）=>”將查詢轉(zhuǎn)換為函數(shù)。
2. 在 “轉(zhuǎn)置表 = Table.Transpose(t)”中將變量t替代原有的“更改的類型”。
3. 作為最后一行轉(zhuǎn)換的“提升的標(biāo)題”的結(jié)尾逗號(hào)需要去除。
單擊“確定”按鈕完成函數(shù)的編寫。以下步驟與合并文件夾操作非常相似，若有需要，可參閱本書中相關(guān)章節(jié)。下面為查詢添加新自定義列，見下圖，但與之前不同，此處的Excel.Workbook是沒有True參數(shù)的，表示不需要表頭。若添加反而報(bào)錯(cuò)。
接下來是刪去多余列，僅保留新的“自定義”列，并展開“自定義”列中的“Data”。單擊菜單中的“添加列”-“調(diào)用自定義函數(shù)”，圖中的“功能查詢”中選擇轉(zhuǎn)換函數(shù)，按“確定”完成。
這里僅保留“轉(zhuǎn)換”列，展開字段，確保不勾選“使用原始列名作為前綴”復(fù)選框。
單擊“確定”按鈕完成。最終結(jié)果見下圖。
M函數(shù)功能非常強(qiáng)大，用戶如果可以熟練掌握M語(yǔ)句，則數(shù)據(jù)清理工作事半功倍。需要強(qiáng)調(diào)的是，所有合并文件的格式必須完全一致，只要數(shù)據(jù)結(jié)構(gòu)有差別，必會(huì)導(dǎo)致合并失敗。對(duì)于過于復(fù)雜的多頭表，一般會(huì)要求先行優(yōu)化數(shù)據(jù)結(jié)構(gòu)。在Power Query中每多一個(gè)數(shù)據(jù)轉(zhuǎn)換任務(wù)操作，對(duì)Power BI的性能影響就多一分，尤其對(duì)于量級(jí)大的數(shù)據(jù)更為明顯，復(fù)雜的M轉(zhuǎn)化也不利于維護(hù)與糾錯(cuò)。
▼
本文節(jié)選自《Power BI企業(yè)級(jí)分析與應(yīng)用》一書！
▊《Power BI企業(yè)級(jí)分析與應(yīng)用》
雷元 著
詳解企業(yè)場(chǎng)景下Power BI 設(shè)計(jì)理念
深度探討Power BI Service管理攻略
本書立足于企業(yè)應(yīng)用場(chǎng)景，從賦能商業(yè)價(jià)值、培養(yǎng)員工數(shù)據(jù)分析能力和引領(lǐng)企業(yè)數(shù)字變革這三大角度勾勒出一套敏捷BI實(shí)踐指南。
除提供豐富的理論指南和Power BI 實(shí)踐內(nèi)容外，本書還涉及Power BI Service治理方面的內(nèi)容。只有在一個(gè)治理完善的Power BI Service架構(gòu)上，Power BI的規(guī)?；瘧?yīng)用才有可能得以實(shí)現(xiàn)。另外，本書還涉及了Power BI與 Microsoft 365結(jié)合的案例，為Power BI的應(yīng)用場(chǎng)景提供了延伸。
（掃碼了解本書詳情）
如果喜歡本文 歡迎 在看丨留言丨分享至朋友圈 三連 熱文推薦
▼點(diǎn)擊閱讀原文，獲取本書詳情~