使用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.tropic.com.cn/portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
Excel企业应用 底部菜单:企业应用
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服