【原】多列結(jié)構(gòu)的二維表轉(zhuǎn)一維表，PowerQuery可以這樣做

PowerBI星球 2022-09-13 發(fā)布于河南

展開(kāi)全文

關(guān)于一維表，你想知道的都在這里了

該文介紹了幾種常見(jiàn)數(shù)據(jù)結(jié)構(gòu)的轉(zhuǎn)換方法，不過(guò)還有一種結(jié)構(gòu)也很常見(jiàn)，就像下面這種：

課程和成績(jī)都有多個(gè)列，無(wú)法直接通過(guò)逆透視來(lái)實(shí)現(xiàn)，那么這種如何轉(zhuǎn)換為一維表呢？

其實(shí)熟練掌握了PowerQuery基本界面功能，這種結(jié)構(gòu)的轉(zhuǎn)換也不難，下面來(lái)看一下轉(zhuǎn)換步驟：

第一步：選中課程1和成績(jī)1兩列，點(diǎn)擊合并列。

分割符可以任選一個(gè)，比如選空格。

同樣的方式把課程2和成績(jī)2、課程3和成績(jī)3合并，這幾列兩兩合并后的數(shù)據(jù)：

第二步：選中“姓名”列，點(diǎn)擊“逆透視其他列”，逆透視后數(shù)據(jù)結(jié)構(gòu)如下。

第三步：刪除“屬性”列，拆分“值”列，以之前合并時(shí)候的分割符空格為拆分符，并修改拆分后的列名。

這樣就完成了一維表的轉(zhuǎn)換。

上面的步驟很簡(jiǎn)單，不過(guò)如果列數(shù)特別多，第一步合并列將會(huì)非常繁瑣。幸運(yùn)的是，星友分享了一個(gè)自定義函數(shù)非常強(qiáng)悍（原創(chuàng)作者不詳，如果作者看到這篇文章，可與我聯(lián)系），通過(guò)輸入幾個(gè)參數(shù)，就可以一次性將這種結(jié)構(gòu)的表轉(zhuǎn)換為一維表。

自定義函數(shù)如下：

let多列組合=(需要操作的表 as table, x as number, y as number, optional 固定列終點(diǎn) as number) as table=>Table.Combine(List.Transform({1..x},            each Table.FromColumns(                                   List.Range( Table.ToColumns(需要操作的表),0,                                               if 固定列終點(diǎn)=null then 1 else 固定列終點(diǎn)                                                                                              )&                                   List.Range( Table.ToColumns(需要操作的表),((_-1)*y+固定列終點(diǎn)),y)                                  )                            )                      ),元數(shù)據(jù)=[Documentation.Name="批量多列合并",      Documentation.Description="可以把多列相同的數(shù)據(jù)合并到一起。第1參數(shù)是需要操作的表，第2參數(shù)x代表的是循環(huán)幾次，第3參數(shù)代表的是多少列循環(huán)，第4參數(shù)是固定標(biāo)題的結(jié)束位置",      Documentation.Examples={[Description="第1列為固定列，每3列進(jìn)行合并存放，一共循環(huán)2次",                            Code="批量多列合并(源,2,3,1)",                            Result="  "]                             }                             ]inValue.ReplaceType(多列組合,Value.Type(多列組合) meta 元數(shù)據(jù))

這里將這個(gè)自定義函數(shù)命名為“批量多列合并”，以上面的數(shù)據(jù)為例，只需要這樣輸入這幾個(gè)參數(shù)：

第一個(gè)參數(shù)是需要處理的表
第二個(gè)參數(shù)是做幾次循環(huán)合并(本示例做3次合并列）
第三個(gè)參數(shù)是每次合并幾列（本示例是每次進(jìn)行課程和成績(jī)2列的合并）
第四個(gè)參數(shù)是前面固定幾列不做合并（本示例中第1列姓名不需要合并）

然后點(diǎn)擊“調(diào)用”，就可以直接得到最終的結(jié)果：

是不是非常便捷。

關(guān)于這個(gè)自定義函數(shù)的邏輯，可以不去理解，只需要知道這幾個(gè)參數(shù)應(yīng)該輸入什么數(shù)據(jù)就行，當(dāng)你有類似的數(shù)據(jù)結(jié)構(gòu)需要轉(zhuǎn)換為一維表時(shí)，直接調(diào)用這個(gè)自定義函數(shù)就可以了。