如何使2列数据拟透视

mavin

原始数据表，是金山文档设计的表单，生成的数据。每一户家庭就是一行。现在要转置为一人一行。



实际工作中，已经用VBA转换了。请教powerquery中是否能完成。

ThxAlot

这个对PQ来说太容易了，PQ就是为这类数据整理而生的，代码量也比VBA小。

1. let
   
2.     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVHtSgMxEHyX+90f2eQ2mzxL6btYzqpQLYi0VL1DkIKntFao2KMKvkxzJW9hPs7SKxpCSHbYmclst5tA0kmQcXfWeWmuRvZz5O6AbnHgmlL3cuXdovBlrTkIhqh8w7aamtlpnW88ojQpBqRS5l7Hu9eJQiII2WxhxpWZPfs+ksxRSsUxIraY+LJQIHlKpLXXvy63X0t7cheEkIF0Okje83Bi79cHZQr69fvLtsrMYB4UBBAXQvKAPCzt+Lzuf0TPICVpJNg7lMGheb20j7lj2WUXB+Qa0xZ4dhsz0ch/M6nnT3a6Mqu3JhOJDGMmDTIYNoRuMzpKy7vwLJx5L3VR2pu++c7CQNwvGGCqWETMZr3PiaQi+CP2NmmccntokGoiJjT9291w9H4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [楼栋 = _t, 房号 = _t, 姓名1 = _t, 电话1 = _t, 姓名2 = _t, 电话2 = _t, 姓名3 = _t, 电话3 = _t, 姓名4 = _t, 电话4 = _t, 姓名5 = _t, 电话5 = _t, 姓名6 = _t, 电话6 = _t]),
   
3. 
   
4.     Info = let
   
5.             cols=Table.ToColumns(Source),
   
6.             f=List.FirstN(cols,2)
   
7.         in
   
8.             Table.Combine(List.Transform(List.Split(List.Skip(cols,2),2),each Table.FromColumns(f&_, {"Bldg","Rm","Name","Tel"}))),
   
9.     #"Filtered Rows" = Table.SelectRows(Info, each ([Tel] <> ""))
   
10. in
    
11.     #"Filtered Rows"

复制代码

vermouth7788

1. let
   
2.     Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
   
3.     #"Grouped Rows" = Table.Group(Source, {"楼栋", "房号"}, {{"计数",(x)=>Table.Combine( List.Transform(Table.ToRows(x),(y)=>Table.FromRecords( List.Transform(List.Split(List.Skip(y,2),2),(z)=>[a=z{0},b=z{1}] ) ) )     )   }}),
   
4.     #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "计数", {"a", "b"}, {"a", "b"})
   
5. in
   
6.     #"Expanded {0}"

复制代码

mystic23

凑个热闹~  ， 供参考

mavin

谢谢大家的回答。是不是对pq来说，再乱的数据也有办法清理？

这么学习这些代码的逻辑。还是有些是在pq中操作，生成了一些代码，然后再去修改代码？

wdx223

手痒，来一发

新函数

mavin

终于自己做出来了，思路就是先合并列，合并以后拟透视，最后再拆分列。
还不会改这些函数，全手工操作的。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"楼栋", Int64.Type}, {"房号", Int64.Type}, {"姓名1", type text}, {"电话1", Int64.Type}, {"姓名2", type text}, {"电话2", Int64.Type}, {"姓名3", type text}, {"电话3", Int64.Type}, {"姓名4", type text}, {"电话4", Int64.Type}, {"姓名5", type text}, {"电话5", Int64.Type}, {"姓名6", type text}, {"电话6", Int64.Type}}),
    合并的列 = Table.CombineColumns(Table.TransformColumnTypes(更改的类型, {{"电话1", type text}}, "zh-CN"),{"姓名1", "电话1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户1"),
    合并的列1 = Table.CombineColumns(Table.TransformColumnTypes(合并的列, {{"电话2", type text}}, "zh-CN"),{"姓名2", "电话2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户2"),
    合并的列2 = Table.CombineColumns(Table.TransformColumnTypes(合并的列1, {{"电话3", type text}}, "zh-CN"),{"姓名3", "电话3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户3"),
    合并的列3 = Table.CombineColumns(Table.TransformColumnTypes(合并的列2, {{"电话4", type text}}, "zh-CN"),{"姓名4", "电话4"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户4"),
    合并的列4 = Table.CombineColumns(Table.TransformColumnTypes(合并的列3, {{"电话5", type text}}, "zh-CN"),{"姓名5", "电话5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户5"),
    合并的列5 = Table.CombineColumns(Table.TransformColumnTypes(合并的列4, {{"电话6", type text}}, "zh-CN"),{"姓名6", "电话6"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"客户6"),
    逆透视的其他列 = Table.UnpivotOtherColumns(合并的列5, {"楼栋", "房号"}, "属性", "值"),
    筛选的行 = Table.SelectRows(逆透视的其他列, each [值] <> ":"),
    删除的列 = Table.RemoveColumns(筛选的行,{"属性"}),
    按分隔符拆分列 = Table.SplitColumn(删除的列, "值", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"值.1", "值.2"}),
    更改的类型1 = Table.TransformColumnTypes(按分隔符拆分列,{{"值.1", type text}, {"值.2", type number}})
in
    更改的类型1

mavin

wdx223 发表于 2022-5-17 08:18
手痒，来一发

新函数

通过学习，终于把List.Split这个写法看懂了。

问一下这个函数在哪个版本里才有啊？
老版本可以升级吗？

慢慢啃这些函数。

mavin

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    转列表 = Table.ToColumns(源),
    序列 = List.Skip(List.Positions(转列表),2),
    奇数 = List.Select(序列,Number.IsOdd),
    偶数 = List.Select(序列,Number.IsEven),
    压缩 = List.Zip({偶数,奇数}),
    遍历 = List.Transform(压缩,each Table.FromColumns({转列表{0},转列表{1},转列表{_{0}},转列表{_{1}}},{"楼栋","房号","姓名","电话"})),
    合并 = Table.Combine(遍历),
    删除空行 = Table.SelectRows(合并, each ([姓名] <> null))
in
    删除空行

henry6

我也参与一下，大部分基本操作

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视的其他列 = Table.UnpivotOtherColumns(源, { "楼栋", "房号"}, "属性", "值"),
    已添加自定义 = Table.AddColumn(逆透视的其他列, "a", each if Text.Contains([属性],"电话") then "电话" else "姓名"),
    删除的列 = Table.RemoveColumns(已添加自定义,{"属性"}),
    自定义1 = List.Transform(Table.Split(删除的列,2),each Table.Pivot(_, List.Distinct([a]), "a", "值")),
    转换为表 = Table.FromList(自定义1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开的“Column1”" = Table.ExpandTableColumn(转换为表, "Column1", {"楼栋", "房号", "姓名", "电话"}, {"楼栋", "房号",  "姓名", "电话"})
in
    #"展开的“Column1”"