英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论