强迫症终结者:Table.ColumnNames

在PQ中我们经常需要对数据进行展开,而这个动作通常只是需要点一下鼠标不需要我们写公式的,所以一般也不会注意到如何去简化。比如如下一组数据,对其先分组再展开,虽然得到的结果还是它。

先完全界面化操作,不作任何修改,看下系统自动生成的公式:

let
    源 = Excel.CurrentWorkbook(){[Name="表8"]}[Content],
    分组的行 = Table.Group(源, {"名称"}, {{"计数", each _, type table}}),
    #"展开的“计数”" = Table.ExpandTableColumn(分组的行, "计数", {"名称", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N"}, {"名称.1", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N"}),
    删除的列 = Table.RemoveColumns(#"展开的“计数”",{"名称.1"})
in
    删除的列

 
什么玩意啊这是?强迫症患者表示不能忍,于是:

let
    源 = Excel.CurrentWorkbook(){[Name="表8"]}[Content],
    分组 = Table.Group(源, {"名称"}, {"a", each Table.RemoveColumns(_,{"名称"})}),
    展开 = Table.ExpandTableColumn(分组, "a", Table.ColumnNames(分组{0}[a]))
in
    展开

使用Table.ColumnNames获取要展开的列名list,替换系统生成的那一大坨,强迫症瞬间好了,感觉棒棒哒!
 
当然除了短之外,使用该函数最大的好处就是实现字段名动态化。如果有一天列名"D"变成了"P",按照系统自动生成的公式,找不到"D"将会报错,因为列名已经被写死了。而如果使用我们下面的方法就可以避免这种情况,因为都是动态的。

27 Replies to “强迫症终结者:Table.ColumnNames”

      1. 就是如果展开的表有跟此表有相同的字段名,就会报错,弹出已存在字段“xx”,需要再添加一个List.RemoveItems,移除不需要的字段

  1. let
    源 = Excel.CurrentWorkbook(){[Name="表8"]}[Content],
    分组 = Table.Group(源, {"名称"}, {"a", each Table.RemoveColumns(_,{"名称"})}),
    展开 = Table.ExpandTableColumn(分组, "a", Table.ColumnNames(分组{0}[a]))
    in
    展开

    分组的列名不要更改~

  2. 能不能解释一下: Table.Group(源, {"名称"}, {"a", each Table.RemoveColumns(_,{"名称"})})

    看了很久没看懂,谢谢!

    1. Table.Group返回按照名称分组的一张表,包含两列,第一列是名称,第二列又是一张表,包含当前行的所有列。
      不理解的话你先写成 Table.Group(源, {"名称"}, {"a", each _})看下结果。
      如果展开的话名称列就出现两次重复了,所以Table.RemoveColumns删掉第二列表里的名称列。

  3. 各位大侠请教一下:“展开 = Table.ExpandTableColumn(分组, "a", Table.ColumnNames(分组{0}[a]))”。

    这一句的后面“分组{0}[a]”,这里“分组”是上面返回的包含“a”的整张表,为什么要写“分组{0}”? 不加{0}就报错吧,{0}表示这个表的第一条记录么?

        1. 更改的类型这一步建议删掉,需要什么类型可以自己指定,更改的类型上一步参考施阳老师的方法即可实现自动化

          1. 那可能需要一张辅助表,实时抓取主表字段,按一定逻辑判断更改什么类型,形成一个list,当主表需要更改类型的时候(这一步最好安排在最后),再把辅助表转化成符合ColumnNames的字符串

  4. 这种分组{0}的写法,如果不同表格字段有差异,会默认按第一张表展开,会丢掉一些字段。
    我换了种写法,先Table.Combine取列标题,然后删掉“Column*”这种空字段再展开,这样适应范围更广:
    展开 = Table.ExpandTableColumn(分组, "a", List.Select(Table.ColumnNames(Table.Combine(分组[a]),each not Text.Contains(_,"Column")))
    感谢楼主提供的思路!

    1. 我也补充一下,楼上的代码编译失败,主要是在Table.Combine取列标题的时候,就出错了。可能原始表构造不同,我这里贴另外一个办法:就是用list.transform构造一个对不同sheet的遍历循环,然后通过record.totable再深化[Value]列的方法取到每个sheet第一行的字段名;然后用list.union把这几个sheet的字段名集合再取并集,从而获得最大包容的总列名,用于做扩展时的列名list。

      let
      源 = Excel.Workbook(File.Contents("\\Mac\Home\Desktop\输入表.xlsx"), null, true),
      删除的其他列 = Table.SelectColumns(源,{"Name", "Data"}),
      展开 = Table.ExpandTableColumn(删除的其他列,"Data",List.Union(List.Transform({0..Table.RowCount(删除的其他列)-1},each Record.ToTable(删除的其他列{_}[Data]{0})[Value])))
      in
      展开

    2. 报错原因是在 Table.ColumnNames(Table.Combine(分组[a])后少了 ”)“
      正确如下:
      展开 = Table.ExpandTableColumn(分组, "a", List.Select(Table.ColumnNames(Table.Combine(分组[a])),each not Text.Contains(_,"Column")))

发表回复

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