|
使用Power Query进行“逆透视”是处理数据时非常有用的技巧。今天我们详细介绍一下这个技巧。
假设我们有如下数据： 
上表中分别记录了不同颜色和尺码的产品在12个月中的销售数据。 现在我们希望得到如下的表格：
这个需求就是逆透视（因为从下表到上表是一个透视的过程）。
要使用逆透视本身非常简单，选中数据区域任意单元格，在数据选项卡下点击“来自表格区域”， 
在Power Query中选中1月~12月列，然后在转换选项卡中点击“逆透视列”， 
这个操作相当于在公式编辑栏中使用下面的公式：
= Table.TransformColumnTypes(源,{{"产品", type text}, {"颜色", type text}, {"尺码", type text}, {"1月", Int64.Type}, {"2月", Int64.Type}, {"3月", Int64.Type}, {"4月", Int64.Type}, {"5月", Int64.Type}, {"6月", Int64.Type}, {"7月", Int64.Type}, {"8月", Int64.Type}, {"9月", Int64.Type}, {"10月", Int64.Type}, {"11月", Int64.Type}, {"12月", Int64.Type}})
然后点击主页选项卡中的关闭并上载： 
就可以得到结果表格。
但是这个方法有缺陷！
上述方法的可以很简单得到逆透视结果，而且也是动态的。这里动态的意思是指源数据如果增加了行，只要点击刷新按钮，就可以得到最新的结果数据。 但是如果我们源数据中删除了某些列，比如只保留1月~6月，或者从6个月的数据又变成了12个月的数据，那么这个方法要么报错，要么不能包含新增数据。从这个意义上，上述方法就是“静态的”逆透视。
我们可以采取下面的步骤：
1. 将数据导入到Power Query中，此时，在PQ编辑器中，是这样的结果：
2. 在最后一步“更改的类型”上点击鼠标右键，选择“插入步骤后”，
在公式编辑栏中，出现新的步骤： 
3. 修改该步骤，使用公式： = Table.ColumnNames(更改的类型)
得到结果，是一个列表：
4. 选中该列表，然后鼠标右键，选择“到表”： 
在弹出的对话框中直接点击确定： 
得到结果：
5. 添加一个新的步骤，使用公式：
= Table.AddColumn( 转换为表, "自定义", each Date.From("2022/" & Text.Replace([Column1],"月","") & "/1"))
这个公式为表格添加了一个自定义列：
6. 保留错误行
在主页选项卡中，点击保留行，保留错误：
得到结果：
7. 选择Column1，鼠标右键，点击“深化”，
得到结果： 8. 修改该步骤的名称为：列名（这一步是为了好记）：
9. 添加步骤，使用公式：
= Table.UnpivotOtherColumns(更改的类型,列名,"月份","销量")
得到结果：
10. 关闭并上载。得到的结果就是完全动态的了。 整个PQ过程的全部代码如下：
let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 更改的类型 = Table.TransformColumnTypes(源,{{"产品", type text}, {"颜色", type text}, {"尺码", type text}, {"1月", Int64.Type}, {"2月", Int64.Type}, {"3月", Int64.Type}, {"4月", Int64.Type}, {"5月", Int64.Type}, {"6月", Int64.Type}, {"7月", Int64.Type}, {"8月", Int64.Type}, {"9月", Int64.Type}, {"10月", Int64.Type}, {"11月", Int64.Type}, {"12月", Int64.Type}}), 自定义1 = Table.ColumnNames(更改的类型), 转换为表 = Table.FromList(自定义1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 已添加自定义 = Table.AddColumn(转换为表, "自定义", each Date.From("2022/"& Text.Replace([Column1],"月","") & "/1")), 保留的错误 = Table.SelectRowsWithErrors(已添加自定义, {"自定义"}), 列名 = 保留的错误[Column1], 自定义2 = Table.UnpivotOtherColumns(更改的类型,列名,"月份","销量")in 自定义2
更详细的解释，请看视频 学习更多Power Query课程 http://www./portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI
Power Excel 知识库 按照以下方式进入知识库学习Excel函数 底部菜单：知识库->Excel函数自定义函数 底部菜单：知识库->自定义函数 Excel如何做 底部菜单：知识库->Excel如何做面授培训 底部菜单：培训学习->面授培训 Excel企业应用 底部菜单：企业应用 也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。
|
