大家好，
前面我們講過利用常規(guī)的方法，制作二級下拉列表輔助表。
今天我們就來介紹一下，Dax 度量值（Power Pivot）和 Power Query 的 M 函數(shù)做法，主要帶大家拓展思路。
如下圖，我們現(xiàn)在需要將左表轉(zhuǎn)換右邊的模式。
01 利用 Dax 度量值
我們先來看看操作：
? 制作一個輔助列，公式為：
=COUNTIF($B$2:B2,B2)
? 插入數(shù)據(jù)透視表，并勾選「將此數(shù)據(jù)添加到數(shù)據(jù)模型」。
PS.我們想要使用 Dax，只需要在創(chuàng)建數(shù)據(jù)透視表時，勾選【將此數(shù)據(jù)添加到數(shù)據(jù)模型】即可。
? 單擊數(shù)據(jù)透視表區(qū)域，在【Power Pivot】選項卡下，單擊【度量值】-【新建度量值】。
? 在公式欄中輸入公式，度量值名稱為度量值 1。
=CONCATENATEX('區(qū)域','區(qū)域'[小類])
公式中，區(qū)域是我們的數(shù)據(jù)源表格，區(qū)域[小類]是區(qū)域表中小類列。
CONCATENATEX 函數(shù)的作用，就是將多個文本合并到一起，類似于 Excel 中的 TEXTJOIN 函數(shù)。
CONCATENATEX 函數(shù)的結(jié)構(gòu)如下：
=CONCATENATEX(表，表達式，分隔符)
=CONCATENATEX('區(qū)域','區(qū)域'[小類])
所以上面 Dax 函數(shù)公式的含義，就是對區(qū)域表中的小類列進行文本合并。
? 將輔助列放在行區(qū)域，將大類放在列區(qū)域，將度量值 1 放在值區(qū)域。
? 將總計行和列禁用。
? 到這里，就制作完成了。
關(guān)于 Dax，大家可能有些疑惑，下面我來簡單的介紹一下。
傳統(tǒng)的數(shù)據(jù)透視表無法對文本進行透視，但是由于超級透視表（Power Pivot）的出現(xiàn)，利用 Dax 度量值我們就可以實現(xiàn)這一功能。
Power 是超級的意思，所以 Power Pivot 就是超級數(shù)據(jù)透視表。
DAX 是 Data Analysis Expression 的縮寫，即數(shù)據(jù)分析表達式，Dax 是在 Power Pivot 的基礎(chǔ)上使用的數(shù)據(jù)統(tǒng)計函數(shù)。
使用 DAX 的好處是：
? 可以彌補數(shù)據(jù)透視表中的【計算字段】的諸多缺陷。
? Dax 函數(shù)可以修改聚合計算的方法。
在普通數(shù)據(jù)透視表中，值匯總方式，只有求和，計數(shù)……等幾種方式。
而在 Power Pivot 中，可以通過多種 Dax 函數(shù)達到更靈活的匯總。
比如這個案例中，我們使用 CONCATENATEX 函數(shù)對文本進行合并。
02 利用 PowerQuery
PowerQuery 是數(shù)據(jù)清理和數(shù)據(jù)轉(zhuǎn)換的利器，現(xiàn)在我們就來看看，利用它，是如何達到所想要的效果的。
具體步驟：
? 將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。
選擇數(shù)據(jù)區(qū)域-在【數(shù)據(jù)】選項卡下，選擇【來自工作表】-【確定】，進入 PQ 編輯器中。
? 選擇大類列，在【主頁】選項卡下，單擊【分組依據(jù)】-所有行-【確定】。
PS：分組依據(jù)功能是對數(shù)據(jù)進行分組統(tǒng)計的，這里我們想要的是對大類進行分組，同時，匯總項需要的是，大類中的小類形成的 list。
? 將 M 函數(shù)公式后面改成 each [小類]。
? 單擊【fx】新增一個步驟，輸入公式：
= Table.FromColumns(分組的行[計數(shù)],分組的行[大類])
Table.FromColumns 函數(shù)能夠?qū)⒏髁行纬?list 轉(zhuǎn)換為各列的表格。
= Table.FromColumns(lists,標(biāo)題形成的list)
案例中：
= Table.FromColumns(分組后的小類形成的lists,標(biāo)題大類的list)
所以公式為，
= Table.FromColumns(分組的行[計數(shù)],分組的行[大類])
? 關(guān)閉并上載表格。
到這里，PQ 方法就完成了。
03 總結(jié)一下
本文介紹了二級下拉列表輔助表的延伸拓展方法：
使用 Dax 度量值：
- 利用 Countif 函數(shù)輔助列，統(tǒng)計第幾次出現(xiàn)。
- 新建度量值，CONCATENCEX 函數(shù)將文本進行合并。
- 大類為列區(qū)域，輔助列為行區(qū)域，度量值為值區(qū)域。
使用 PowerQuery：
- 分組依據(jù)。
- Table.FromColumns。
關(guān)于二級下拉列表的輔助表的制作方法你學(xué)會了嘛~
