Power Query 是做数据转换、数据清洗的利器,不管是在Excel还是PowerBI,如何玩好Power Query是成功建模的必不可少的一步。
今天要get到的一个新技巧:行列转换
如何将多行数据串联到一列
场景:很多时候需要把多行的数据串联起来
假设有数据如下,如何在Excel或者PowerBI做数据转换得到需要的结果呢?
名字 | 产品 | 数量 |
---|---|---|
张三 | 苹果 | 4 |
张三 | 雪梨 | 2 |
李四 | 香蕉 | 8 |
李四 | 橙子 | 3 |
李四 | 苹果 | 10 |
需求:将各人买的东西和数量串联起来到一行上。
1. 先把产品和数量串联起来: 添加列》
= Table.AddColumn(#"Changed Type", "产品数量", each [产品] & ":" & Text.From([数量]))
2. PowerQuery编辑器》转换》分组》 分组依据:名字 新列名:数据 操作:所有行
= Table.Group(#"新列 产品数量", {"名字"}, {{"数据", each _, type table [名字=text, 产品=text, 数量=number, 产品数量=text]}})
3. 添加列》 自定义列》 = Table.AddColumn(分组的行, "产品数量2", each Table.Column([数据],"产品数量"))
= Table.AddColumn(分组的行, "产品数量2", each Table.Column([数据],"产品数量"))
4. 点击【产品数量2】列的扩展按钮》提取值》分隔符:逗号 (任何你想要的间隔字符)
= Table.TransformColumns(已添加自定义, {"产品数量2", each Text.Combine(List.Transform(_, Text.From), ","), type text})
哇啦!结果显示出来啦! 苹果:4,雪梨:2 香蕉:8,橙子:3,苹果:10
5. 删除多余的列就得到结果啦!
= Table.RemoveColumns(提取值,{"数据"})
作业:
如果数据如下,如何得到结果呢?
名字 | 分类 | 产品 | 数量 |
---|---|---|---|
张三 | 水果 | 苹果 | 4 |
张三 | 水果 | 雪梨 | 2 |
李四 | 水果 | 香蕉 | 8 |
李四 | 水果 | 橙子 | 3 |
李四 | 水果 | 苹果 | 10 |
张三 | 饼干 | 桃酥 | 3 |
张三 | 饼干 | 杏仁饼 | 8 |
李四 | 饼干 | 龙须饼 | 3 |
李四 | 饼干 | 老婆饼 | 5 |
李四 | 饼干 | 老公饼 | 2 |
所需结果: