Power Query分組功能也太強大了吧~!

HI，大家好，我是星光。

分組聚合是數據處理與分析過程中最常用的技能之一，大家對此也並不陌生，比如將"成績"按"班級"分組求平均，將"工資"按"部門"0分組匯總等等。

正因為這傢伙太常用，因此各類軟體或語言大都提供了專門且高效的功能或語句，比如咱們所熟悉的Excel的分類匯總和數據透視表……

Power Query也不例外，而且相比於透視表，它在很多方面更加獨特且強大，可以對聚合欄位進行複雜的計算，在分組結果中篩選目標值、刪除特定值、去除重複值、合併同類項等。

今天就來給大家詳細介紹一下它。

一個例子

舉個例子還是。

有一份成績表，如下圖所示，包含了班級、小組、姓名、科目和成績等欄位。現在需要使用PowerQuery統計每個班級的綜合平均分。

首先將數據加載到Power Query編輯器。

在PQ編輯器的[主頁]選項卡下，單擊[分組依據]命令，打開對話框。

分組欄位選擇'班級'，新列名輸入為'平均分'，操作類型選擇'平均值'，柱（也就是聚合運算的欄位）選擇'成績'。

確定後返回結果如下▼

如果我們需要查詢不同班級不同小組的總分以及平均分，只需要添加相關分組和聚合欄位即可。

在分組對話框內，選中[高級]單選按鈕。單擊[添加分組]命令，將分組欄位設置為'班級'和'小組'。單擊'添加聚合'命令，分別設置新列名、聚合方式以及聚合欄位。

確定後返回結果如下：

看到這兒，有些朋友可能會困惑，同樣的功能使用Excel透視表就可以輕鬆解決了，為什麼還要學習PQ的分組功能呢？

……

你最帥，但你這想法不大對。

這事主要有兩個原因。一個是PQ分組後的結果表可以嵌套在其它步驟表中繼續使用；另外一個是PQ的聚合方式支持函數運算，可以解決很多不單純的問題，比如在分組結果中刪除特定值、去除重複值、合併同類項等。此外，值得一提的是，它還有一個獨特的分組方式，按連續值分組。

我舉幾個小例子。

第1個例子

分組再查詢

依然以上圖所示的成績表為例，現在我們需要查詢個別班級和小組的總分及平均分數據，查詢名單如下▼

這查詢名單隨時可能發生變動，也就是說班級和小組可能增、改、刪……因此查詢結果必須是動態的。

操作步驟如下▼

首先將A:B列的數據以[自表格/區域]的方式加載到PQ編輯器。

然後將成績表的數據按之前咱們所分享的方式分組聚合，得出結果如下：

最後切換到查詢表，在[主頁]選項卡下單擊[合併查詢]。在打開的對話框中，主表設置為'查詢表'，匹配表設置為'成績表'，匹配欄位分別設置為'班級'和'小組'，聯結種類為：左外部第1個中的所有行第2個中的匹配行，也就是返回第1張表的所有記錄以及第2張表與之相匹配的數據。

確定後返回結果如下▼

單擊[成績表]欄位右側的擴展按鈕，在彈出的菜單中去掉和主表欄位重複的'班級'和'小組'，同時取消勾選【使用原始列名作為前綴】並確定，即可獲取查詢結果。

將查詢結果上載到Excel工作表，如果查詢名單發生了變更，只需要刷新即可。

第2個例子

聚合去重文本值

依然以上文的成績表為例，現在需要查詢各個班級不重複的人員名單並聚合展示，模擬結果如下圖所示。

將成績表數據加載到PQ編輯器，添加新步驟後，在編輯欄輸入以下函數即可。

= Table.Group(源,          {"班級"},         {"人名", each Text.Combine(List.Distinct(_[姓名]),",")}  )
這是一個標準的Table.Group函數，它的基本語法如下▼
=Table.Group(表,分組欄位,{新列名,聚合方式})
以上述公式為例，來源表名為源，分組欄位是'班級'，新列名為'人名'……
重點是聚合部分。_[姓名]返回源表分組後的姓名列，是一個列表結構。List.Distinct對它去重複，Text.Combine函數再對去重複後的結果以逗號為分隔符合併為一個字符串。
這裡涉及到M函數的容器結構、函數式參數、上下文運算等概念，如果沒有M函數基礎，一時看不懂，攤手，那就不懂，了解一下，混個面熟也是好的。
第3個例子
按連續值分組
通常意義上的分組，是對整表相同類型的數據進行歸納，比如把整張表同為2班的數據分為一組等。PQ除了支持這樣的全局分組模式外，也支持局部分組，或者說連續值分組。
什麼意思呢？
我舉個例子。
如下圖所示，A列是中英文混雜的數據，需要將其轉換為右圖所示的結構，即中文在A列，英文合併在B列。
將A列數據加載到PQ編輯器，新增步驟後在編輯欄輸入以下函數即可。
= Table.Group(源,              "數據",               {"英文",each Text.Combine(List.Skip(_[數據]),",")},              0,              (x,y)=>Number.From(y>"z") )
和上一個例子相比，Table.Group函數新增了兩個可選的參數。
=Table.Group(表,分組欄位,{新列名,聚合方式},分組模式,分組依據)
第4參數分組模式是可選的。如果為1，或省略，則為全局分組；如果為0，則為局部連續值分組。本例為0。
第5參數是一個函數參數。其中x代表真元素，y代表真元素以下的其它元素。以本例而言，代碼開始運行時，默認第1個元素為真元素，即x為'星光'，y代表'星光'以下的其它元素。
Number.From(y>"z")，判斷元素是否為中文，如果是，則返回1，否則返回0。

如果該值為0，則元素為假元素，將其劃分為真元素的組中，比如starshine和starlight屬於'看見星光'；如果該值為1，則元素為真元素，比如'英語'，則新開一組，x為真元素'英語'，y為真元素'英語'以下的其它元素……以此類推，直至數據盡頭。
獲取分組結果後，用List.Skip函數刪除分組列表首個元素，再用Text.Combine將分組列表元素以逗號為分隔符合併為一個字符串。
……
沒了，打完收工，左上角點關注，咱們下期再見。
文件下載百度網盤..▼
https://pan.baidu.com/s/19BLVZx6ANy05_Zu_eTgKTQ 
提取碼: u7r4
♥♥溫馨小提示▼
公眾號每天會發布1篇函數教程+1篇編程教程+1個技巧小視頻，如果你沒有收到我的更新，是由於微信按算法顯示公眾號而不是實際更新時間——這時就需要如下圖所示星標我一下啦，撒花✿