Excele表格資料“透視”你會！超級透視會嗎？
今天我們就來介紹一下，Dax 度量值（Power Pivot）和 Power Query 的 M 函式做法，主要帶大家
拓展思路
。
如下圖，我們現在需要將左錶轉換右邊的模式。
利用 Dax 度量值
我們先來看看操作：
製作一個輔助列，公式為：
=COUNTIF（$B$2：B2，B2）
插入資料透視表，並勾選「將此資料新增到資料模型」。
PS。我們想要使用 Dax，只需要在建立資料透視表時，勾選【將此資料新增到資料模型】即可。
單擊資料透視表區域，在【Power Pivot】選項卡下，單擊【度量值】-【新建度量值】。
在公式欄中輸入公式，度量值名稱為度量值 1。
=CONCATENATEX（‘區域’，‘區域’［小類］）
公式中，區域是我們的資料來源表格，區域［小類］是區域表中小類列。
CONCATENATEX 函式的作用，就是
將多個文字合併到一起，類似於 Excel 中的 TEXTJOIN 函式。
CONCATENATEX 函式的結構如下：
=CONCATENATEX（表，表示式，分隔符）
=CONCATENATEX（‘區域’，‘區域’［小類］）
所以上面 Dax 函式公式的含義，就是對區域表中的小類列進行文字合併。
將輔助列放在行區域，將大類放在列區域，將度量值 1 放在值區域。
將總計行和列禁用。
到這裡，就製作完成了。
關於 Dax，大家可能有些疑惑，下面我來簡單的介紹一下。
傳統的資料透視表無法對文字進行透視，但是由於超級透視表（Power Pivot）的出現，利用 Dax 度量值我們就可以實現這一功能。
Power 是超級的意思，所以
Power Pivot 就是超級資料透視表。
DAX 是
Data Analysis Expression
的縮寫，即
資料分析表示式
，Dax 是在 Power Pivot 的基礎上使用的資料統計函式。
使用 DAX 的好處是：
可以彌補資料透視表中的【計算欄位】的諸多缺陷。
Dax 函式可以修改聚合計算的方法。
在
普通資料透視表
中，值彙總方式，只有求和，計數……等幾種方式。
而在 Power Pivot 中，可以透過多種 Dax 函式達到更靈活的彙總。
比如這個案例中，我們使用 CONCATENATEX 函式對文字進行合併。
利用 PowerQuery
PowerQuery 是資料清理和資料轉換的利器，現在我們就來看看，利用它，是如何達到所想要的效果的。
具體步驟：
將資料匯入到 PQ 編輯器中。
選擇資料區域-在【資料】選項卡下，選擇【來自工作表】-【確定】，進入 PQ 編輯器中。
選擇大類列，在【主頁】選項卡下，單擊【分組依據】-所有行-【確定】。
PS：分組依據功能是對資料進行分組統計的，這裡我們想要的是對大類進行分組，同時，彙總項需要的是，大類中的小類形成的 list。
將 M 函式公式後面改成 each ［小類］。
單擊【fx】新增一個步驟，輸入公式：
= Table。FromColumns（分組的行［計數］，分組的行［大類］）
▲ 左右滑動檢視
Table。FromColumns 函式能夠將各列形成 list 轉換為各列的表格。
= Table。FromColumns（lists，標題形成的list）
案例中：
= Table。FromColumns（分組後的小類形成的lists，標題大類的list）
▲ 左右滑動檢視
所以公式為，
= Table。FromColumns（分組的行［計數］，分組的行［大類］）
▲ 左右滑動檢視
關閉並上載表格。
到這裡，PQ 方法就完成了。
總結一下
本文介紹了二級下拉列表輔助表的延伸拓展方法：
使用 Dax 度量值：
利用 Countif 函式輔助列，統計第幾次出現。
新建度量值，CONCATENCEX 函式將文字進行合併。
大類為列區域，輔助列為行區域，度量值為值區域。
使用 PowerQuery：
分組依據。
Table。FromColumns。