Conditionally selecting expand column based on row value - Power Query

[navafolk]

Hi pros,

I would better give sample tables in Power Query.

- table 1: BASE

Vendor	Type
ATT	on
EPP	off

- table 2: LOOKUP

Vendor_ID	Local_ID	Global_ID
ATT	local1
EPP	local2	global1

Matching rules are:

- Matching by [Vendor] in BASE and [Vendor_ID] in LOOKUP

- If [Type] in BASE is 'on' then gets 'Local_ID' in LOOKUP; [Type] in BASE is 'off' then gets 'Global_ID' in LOOKUP

Expected result would look like in BASE

I can obtain this in Power Query with several supporting steps like: Merge Queries with both [LOCAL_ID] [GLOBAL_ID] expanded -> Add conditional column to decide which ID to take based on [TYPE] on/off -> Remove expanded columns.

let
    Source = Excel.CurrentWorkbook(){[Name="BASE"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Vendor"}, LOOKUP, {"Vendor_ID"}, "LOOKUP", JoinKind.LeftOuter),
    #"Expanded LOOKUP" = Table.ExpandTableColumn(#"Merged Queries", "LOOKUP", {"Local_ID", "Global_ID"}, {"LOOKUP.Local_ID", "LOOKUP.Global_ID"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded LOOKUP", "ID", each if [Type] = "on" then [LOOKUP.Local_ID] else [LOOKUP.Global_ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"LOOKUP.Local_ID", "LOOKUP.Global_ID"})
in
    #"Removed Columns"

In Power Query, is it possible to conditionally select which column to expand based on row value in Merge Queries? or any better lookup way?

Thank you all.

[Anonymous · Accepted solution]

Hi @navafolk ,
You can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwJUdJRys9TitWJVnINCABx0tKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, Type = _t]),
    #"Merged and Added ID" = Table.AddColumn(
        Table.ExpandTableColumn(
            Table.NestedJoin(Source, {"Vendor"}, LOOKUP, {"Vendor_ID"}, "LOOKUP", JoinKind.LeftOuter),
            "LOOKUP", {"Local_ID", "Global_ID"}, {"LOOKUP.Local_ID", "LOOKUP.Global_ID"}
        ),
        "ID", each if [Type] = "on" then [LOOKUP.Local_ID] else [LOOKUP.Global_ID]
    ),
    #"Removed Unnecessary Columns" = Table.RemoveColumns(#"Merged and Added ID", {"LOOKUP.Local_ID", "LOOKUP.Global_ID"})
in
    #"Removed Unnecessary Columns"

This code requires only two steps

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bhanu_gautam]

@navafolk , Your current approach is correct

[navafolk]

Thank you, @bhanu_gautam for your response.

Just wondering if there is any more efficient way with less supporting steps?