分组和取消分组行,同时保留所有列的动态性。

huangapple go评论61阅读模式
英文:

Group and ungroup rows preserving all columns dynamically

问题

在Power Query中,我想按照某些列对表进行分组,根据分组添加一个新列,然后再取消分组。我可以使用GUI来实现这一点。但是GUI会硬编码我的列名和类型。如果我以后想要更改列名或类型,更新起来会很耗时。

我能否使Power Query对所有列进行分组和取消分组,而不硬编码列名?我可以使用Table.Schema来访问列名和类型的名称,但是获取类型本身会很麻烦。

英文:

In Power Query, I want to group my table by certain columns, add a new column based on the groups, then ungroup my table again. I can do this using the GUI. But the GUI hard-codes my column names and types. If I later want to change a column name or type, it takes a long time to update.

Can I make Power Query group and ungroup all the columns, without hard-coding the column names?
I can use Table.Schema to access the column names and the names of the types, but it's a pain to get the types themselves.

答案1

得分: 1

在一个分组中,你可以简单地删除组步骤代码中**[** **]**之间的代码,然后它将对任何列进行操作。

因此,自动生成的代码

#"Grouped Rows" = Table.Group(#"Changed Type", {"a"}, {{"data", each _, type table [a=nullable number, b=nullable number, c=nullable number]}})

变成了

#"Grouped Rows" = Table.Group(#"Changed Type", {"a"}, {{"data", each _, type table }})
英文:

In a group you can simply remove the code between the [ ] within the group step code, and then it will operate on any columns

so that the autogenerated code

#"Grouped Rows" = Table.Group(#"Changed Type", {"a"}, {{"data", each _, type table [a=nullable number, b=nullable number, c=nullable number]}})

becomes

#"Grouped Rows" = Table.Group(#"Changed Type", {"a"}, {{"data", each _, type table }})

答案2

得分: 0

如果您事先知道可能的列类型,那么这是可能的。您可以使用if [Kind]="text" then type text else ... else type any将类型名称转换为类型。然后,您需要构建一个记录类型,该类型使用设计不太方便的Type.ForRecord函数。

如果前一步被称为Did Previous Step,您可以使用以下代码按列"Column A""Column B"进行分组。

=Table.Group(#"Did Previous Step", {"Column A", "Column B"}, {{"AllRows", each _,
type table Type.ForRecord(Record.FromList(
Table.TransformRows(
Table.Schema(#"Did Previous Step"),
each [Type=if [Kind]="text" then type nullable text
else if [Kind]="number" then type nullable number
else type any, Optional=false]
), Table.Schema(#"Inserted Merged Column")[Name]),false}})

反过来也是类似的,不过您应该使用List.Difference()两次,以避免重复列分组的列。

英文:

This is possible if you know in advance what column types are possible. You can use if [Kind]="text then type text else ... else type any to turn the type names into types. Then you have to build a record type, which uses the inconveniently-designed Type.ForRecord function.

If the previous step is called Did Previous Step, you can use the following code to group by columns "Column A" and "Column B".

=Table.Group(#"Did Previous Step", {"Column A", "Column B"}, {{"AllRows", each _,
  type table Type.ForRecord(Record.FromList(
    Table.TransformRows(
      Table.Schema(#"Did Previous Step"), 
      each [Type=if [Kind]="text" then type nullable text 
            else if [Kind]="number" then type nullable number 
            else type any, Optional=false]
    ), Table.Schema(#"Inserted Merged Column")[Name]),false)}})

Going the other way works similarly, though you should use List.Difference() twice to avoid duplicating the columns that you grouped by.

huangapple
  • 本文由 发表于 2023年7月27日 16:03:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76777672.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定