How to lookup value from another table in Power Query?-CodePudding

Hi everyone,

I want to transform the strategy code number in my Strategy Code column (Data table) into strategy name based on the dim_strategy table. My Challenge is there can be more than 1 strategy code appear in each row and hence I want to use as the delimiter to combine different strategy name in Data table.

This is the desire output in Data table:

Any help or advise will be greatly appreciated!

CodePudding user response：

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"