如何从电源查询中的另一个表中查找值？

发布于 2025-02-08 08:21:04 字数 601 浏览 1 评论 0原文

基于dim_strategy表。我的挑战是，每行可能会出现超过1个策略代码，因此我想使用+作为定界符，将不同的策略名称组合在data表中。

这是data表中所需的输出：

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

秋日私语 2025-02-15 08:21:04

此查询将为您实现。您将需要更改表源的任何源，但是其余的步骤应该完全相同。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Strategy Code", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Strategy Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Strategy Code"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Strategy Code", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Strategy Code"}, dim_strategy, {"Strategy Code"}, "dim_strategy", JoinKind.LeftOuter),
    #"Expanded dim_strategy" = Table.ExpandTableColumn(#"Merged Queries", "dim_strategy", {"Strategy"}, {"Strategy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded dim_strategy",{"Strategy Code"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"No."}, {{"Stretagy Name", each Text.Combine([Strategy], " + "), type nullable text}})
in
    #"Grouped Rows"

This query will achieve that for you. You will need to change the source for whatever your table source is but the rest of the steps should be exactly the same.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Strategy Code", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Strategy Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Strategy Code"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Strategy Code", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Strategy Code"}, dim_strategy, {"Strategy Code"}, "dim_strategy", JoinKind.LeftOuter),
    #"Expanded dim_strategy" = Table.ExpandTableColumn(#"Merged Queries", "dim_strategy", {"Strategy"}, {"Strategy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded dim_strategy",{"Strategy Code"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"No."}, {{"Stretagy Name", each Text.Combine([Strategy], " + "), type nullable text}})
in
    #"Grouped Rows"

回复收藏 0 原文

衣神在巴黎 2025-02-15 08:21:04

或者，您可以添加一个列

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Strategy Name", each Text.Combine(
    List.Transform(Text.Split([Strategy Code],","), each  
    dim_strategy[Strategy]{List.PositionOf(dim_strategy[Strategy Code],Number.From(_))}
),", "))
in  #"Added Custom"

它将策略代码转换为列表，然后转换为数值列表，在DIM_STRATEGY策略码中找到该列表的位置，然后拉动相应的策略列并重新组合列表进入文本

列表。Bufferdim_strategy2 [策略代码]作为较早的步骤，如果数据集很大

Or you could add a column

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Strategy Name", each Text.Combine(
    List.Transform(Text.Split([Strategy Code],","), each  
    dim_strategy[Strategy]{List.PositionOf(dim_strategy[Strategy Code],Number.From(_))}
),", "))
in  #"Added Custom"

It converts the Strategy Code to a list, then to numerical list, finds the position of that in the dim_strategy Strategy Code column, then pulls the corresponding Strategy column and recombines the list into text

List.Buffer dim_strategy2[Strategy Code] as an earlier step if dataset is large

回复收藏 0 原文

~没有更多了~