Search code examples
joinpowerbiconcatenationpowerquerylookup

How to lookup value from another table in Power Query?

enter image description here

enter image description here

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 desired output in Data table:

enter image description here

Solution

  • 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.

    enter image description here

    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"