Table.SelectRows当一个列被连接而另一个被拆分成行时

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

Table.SelectRows when one column concatenated and the other is split into rows

问题

我想使用Table.SelectRows(或任何其他表达式)创建一个自定义列,该列执行以下操作。在我的Table1中,我有一个名为Cities的列,其中列出了用逗号分隔的值:

Country Cities
France Paris, Lyon, Marseille
Germany Berlin, Munich, Dortmund

在我的Table2中,我有一个名为Sister_Cities的列,内容如下:

City Sister_Cities
Paris Rome
Lyon Birmingham
Lyon Guangzhou
Lyon Montreal
Lyon Addis Ababa
Marseille Genoa
Marseille Glasgow

我知道逻辑上应该将第一个表拆分为行,然后基于Cities和City列进行连接,但我想保持它们连接,以便在Table1中有一个新列,看起来像这样:

Country Cities Custom.Sister_Cities
France Paris, Lyon, Marseille Rome, Birmingham, Guangzhou, Montreal, Addis Ababa, Genoa, Glasgow

如何最好地实现这一目标?感谢任何建议!

英文:

I want to create a custom column using Table.SelectRows (or any other expression) that does the following. In my Table1, I have a column called Cities that lists values separated by a comma:

Country Cities
France Paris, Lyon, Marseille
Germany Berlin, Munich, Dortmund

In my Table2, I have a Sister_Cities column like this:

City Sister_Cities
Paris Rome
Lyon Birmingham
Lyon Guangzhou
Lyon Montreal
Lyon Addis Ababa
Marseille Genoa
Marseille Glasgow

I know the logical thing to do would be to split the first table into rows and then do a join based on the Cities and City columns, but I want to keep them concatenated so that I have a new column in Table1 that looks like this:

Country Cities Custom.Sister_Cities
France Paris, Lyon, Marseille Rome, Birmingham, Guangzhou, Montreal, Addis Ababa, Genoa, Glasgow

What's the best way to achieve this? Appreciate any advice!

答案1

得分: 1

I would probably still do it that way

我可能仍然会以这种方式做:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Cities", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Cities"),

"Merged Queries" = Table.NestedJoin(# "Split Column by Delimiter", {"Cities"}, Table2, {"City"}, "Table2", JoinKind.LeftOuter),

"Expanded Table2" = Table.ExpandTableColumn(# "Merged Queries", "Table2", {"Sister_Cities"}, {"Sister_Cities"}),

"Grouped Rows" = Table.Group(# "Expanded Table2", {"Country"}, {

{"Cities", each Text.Combine(List.Transform(List.Distinct([Cities]), Text.From), ", "), type text},
{"Sister_Cities", each Text.Combine(List.Transform([Sister_Cities], Text.From), ", "), type text}
})
in # "Grouped Rows"

alternate, probably slower

备用方式,可能较慢

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sisters= Table.Buffer(Table.Group(Table2, {"City"}, {{"Sister_Cities", each Text.Combine(List.Transform([Sister_Cities], Text.From), ", "), type text} })),

"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform(Text.Split([Cities],", "), (x)=> try Table.SelectRows(Sisters, each [City] = x)[Sister_Cities]{0} otherwise null)),

"Added Custom1" = Table.AddColumn(# "Added Custom", "Sister_Cities", each Text.Combine(List.Transform(List.RemoveNulls([Custom]), Text.From), ",")),

"Removed Columns" = Table.RemoveColumns(# "Added Custom1",{ "Custom"})

in # "Removed Columns"

英文:

I would probably still do it that way

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Cities", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Cities"),
#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"Cities"}, Table2, {"City"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Sister_Cities"}, {"Sister_Cities"}),
#"Grouped Rows" = Table.Group(#"Expanded Table2", {"Country"}, {
    {"Cities", each Text.Combine(List.Transform(List.Distinct([Cities]), Text.From), ", "), type text},
    {"Sister_Cities", each Text.Combine(List.Transform([Sister_Cities], Text.From), ", "), type text}
    })		
in  #"Grouped Rows"

Table.SelectRows当一个列被连接而另一个被拆分成行时

alternate, probably slower

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sisters= Table.Buffer(Table.Group(Table2, {"City"}, {{"Sister_Cities", each Text.Combine(List.Transform([Sister_Cities], Text.From), ", "), type text} })),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform(Text.Split([Cities],", "), (x)=> try Table.SelectRows(Sisters, each [City] = x)[Sister_Cities]{0} otherwise null)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Sister_Cities", each Text.Combine(List.Transform(List.RemoveNulls([Custom]), Text.From), ",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in  #"Removed Columns"

huangapple
  • 本文由 发表于 2023年6月6日 06:04:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76410274.html
匿名

发表评论

匿名网友

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

确定