分组转置

题目:


在excel中使用数据有效性制作二级联动下拉菜单的时候,需要如右表格式的数据,但我们拿到的源数据往往是如左表所示。
从左表转成右表,如果要在excel中做可能得费把劲,看看在PQ中如何做?

思路:

大多数人看到题目第一反应可能就是转置,但做起来会发现又没那么简单,转来转去都不对,因为直接转置的话同一个省会有多列,而我们最后需要的只是一个省一列。
那就想办法让同一个省聚合嘛,于是很容易想到Table.Group分组,先看下效果:

没错,是我们要的效果,一个省对应该省所有市的list,然后再转置?

标题是对了,可是你会发现下面的市没法展开,全部展开结构就乱了,方案不可行!
这时候我们想到,一个list作为列中的数据,另一个list作为列名,这不正是Table.FromColumns需要的两个参数么?于是:

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组 = Table.Group(源, {"省"}, {"市", each _[市]}),
    结果 = Table.FromColumns(分组[市],分组[省])
in
    结果

附件

18 Replies to “分组转置”

  1. let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
        分组的行 = Table.Group(源, {"省"}, {{"计数", each Text.Combine([市],"*"), type text}}),
        按分隔符拆分列 = Table.SplitColumn(分组的行, "计数", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"计数.1", "计数.2", "计数.3", "计数.4", "计数.5", "计数.6", "计数.7", "计数.8", "计数.9", "计数.10", "计数.11", "计数.12", "计数.13", "计数.14", "计数.15", "计数.16", "计数.17", "计数.18", "计数.19", "计数.20", "计数.21"}),
        更改的类型 = Table.TransformColumnTypes(按分隔符拆分列,{{"计数.1", type text}, {"计数.2", type text}, {"计数.3", type text}, {"计数.4", type text}, {"计数.5", type text}, {"计数.6", type text}, {"计数.7", type text}, {"计数.8", type text}, {"计数.9", type text}, {"计数.10", type text}, {"计数.11", type text}, {"计数.12", type text}, {"计数.13", type text}, {"计数.14", type text}, {"计数.15", type text}, {"计数.16", type text}, {"计数.17", type text}, {"计数.18", type text}, {"计数.19", type text}, {"计数.20", type text}, {"计数.21", type text}}),
        转置表 = Table.Transpose(更改的类型),
        提升的标题 = Table.PromoteHeaders(转置表, [PromoteAllScalars=true]),
        更改的类型1 = Table.TransformColumnTypes(提升的标题,{{"重庆", type text}, {"浙江", type text}, {"云南", type text}, {"新疆", type text}, {"香港", type text}, {"西藏", type text}, {"天津", type text}, {"台湾", type text}, {"四川", type text}, {"上海", type text}, {"陕西", type text}, {"山西", type text}, {"山东", type text}, {"日本", type text}, {"青海", type text}, {"其它国家", type text}, {"宁夏", type text}, {"内蒙古", type text}, {"辽宁", type text}, {"江西", type text}, {"江苏", type text}, {"吉林", type text}, {"湖南", type text}, {"湖北", type text}, {"黑龙江", type text}, {"河南", type text}, {"河北", type text}, {"海南", type text}, {"贵州", type text}, {"广西", type text}, {"广东", type text}, {"甘肃", type text}, {"福建", type text}, {"北京", type text}, {"澳门", type text}, {"安徽", type text}})
    in
        更改的类型1
    

    我之前是用先分组聚合文本再拆开的办法,虽然有点傻,不过效果好像是一样的

  2. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组的行 = Table.Group(源, {"省"}, {{"计数", each Text.Combine([市],"*"), type text}}),
    按分隔符拆分列 = Table.SplitColumn(分组的行, "计数", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"计数.1", "计数.2", "计数.3", "计数.4", "计数.5", "计数.6", "计数.7", "计数.8", "计数.9", "计数.10", "计数.11", "计数.12", "计数.13", "计数.14", "计数.15", "计数.16", "计数.17", "计数.18", "计数.19", "计数.20", "计数.21"}),
    更改的类型 = Table.TransformColumnTypes(按分隔符拆分列,{{"计数.1", type text}, {"计数.2", type text}, {"计数.3", type text}, {"计数.4", type text}, {"计数.5", type text}, {"计数.6", type text}, {"计数.7", type text}, {"计数.8", type text}, {"计数.9", type text}, {"计数.10", type text}, {"计数.11", type text}, {"计数.12", type text}, {"计数.13", type text}, {"计数.14", type text}, {"计数.15", type text}, {"计数.16", type text}, {"计数.17", type text}, {"计数.18", type text}, {"计数.19", type text}, {"计数.20", type text}, {"计数.21", type text}}),
    转置表 = Table.Transpose(更改的类型),
    提升的标题 = Table.PromoteHeaders(转置表, [PromoteAllScalars=true]),
    更改的类型1 = Table.TransformColumnTypes(提升的标题,{{"重庆", type text}, {"浙江", type text}, {"云南", type text}, {"新疆", type text}, {"香港", type text}, {"西藏", type text}, {"天津", type text}, {"台湾", type text}, {"四川", type text}, {"上海", type text}, {"陕西", type text}, {"山西", type text}, {"山东", type text}, {"日本", type text}, {"青海", type text}, {"其它国家", type text}, {"宁夏", type text}, {"内蒙古", type text}, {"辽宁", type text}, {"江西", type text}, {"江苏", type text}, {"吉林", type text}, {"湖南", type text}, {"湖北", type text}, {"黑龙江", type text}, {"河南", type text}, {"河北", type text}, {"海南", type text}, {"贵州", type text}, {"广西", type text}, {"广东", type text}, {"甘肃", type text}, {"福建", type text}, {"北京", type text}, {"澳门", type text}, {"安徽", type text}})
    in
    更改的类型1
    我之前使用分组后先聚合文本再拆开的方法,应该也可以

  3. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"省", type text}, {"市", type text}}),
    分组的行 = Table.Group(更改的类型, {"省"}, {{"计数", each Table.AddIndexColumn(_, "索引", 0, 1), type table}}),
    #"展开的“计数”" = Table.ExpandTableColumn(分组的行, "计数", {"市", "索引"}, {"市", "索引.1"}),
    已透视列 = Table.Pivot(#"展开的“计数”", List.Distinct(#"展开的“计数”"[省]), "省", "市")
    in
    已透视列

    我的方法是 先分组,然后到组table里添加一个计数列,也就是计算每个table里有几个记录,展开后再透视

  4. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组的行 = Table.Group(源, {"省"}, {"辅助", each _[市]}),
    处理 = List.RemoveLastN(Table.TransformColumns(分组的行,{"辅助",each [a=Table.AddIndexColumn(分组的行,"索引",0,1),b=List.Transform({0..Table.RowCount(分组的行)},each {分组的行[省]{_}}&分组的行[辅助]{_})][b]})[辅助]{0},1),
    //处理这步里面有问题,不过这也是自己先尝试写的,就我这水平居然也写出来了哈哈
    结果 = Table.PromoteHeaders(Table.FromColumns(处理))
    in
    结果

  5. 请问这段let...in...的代码就是在编辑栏里输入吗,我下载了示例文件,在编辑栏里输入了这部分代码,为什么没有达到效果,反而给当成字符串了呢,求指导。

  6. Table.FromColumns()对市取出来的时候会自动展开,而不是像第一张图那样是一行list状态!妙!

  7. 分组里转置表,再展开,再转置,两次转置搞定
    let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组 = Table.Group(源,"省",{"a", each Table.Transpose(_){1}}),
    展开 = Table.ExpandRecordColumn(分组, "a", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}),
    转置 = Table.PromoteHeaders(Table.Transpose(展开))
    in
    转置

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注