Forum Discussion

navafolk's avatar
navafolk
Icon for Helper IV rankHelper IV
2 years ago
Solved

Conditionally selecting expand column based on row value - Power Query

Hi pros,

I would better give sample tables in Power Query.

- table 1: BASE

VendorType
ATTon
EPPoff

 

- table 2: LOOKUP

Vendor_IDLocal_IDGlobal_ID
ATTlocal1 
EPPlocal2global1

 

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

    • navafolk's avatar
      navafolk
      Icon for Helper IV rankHelper IV
      2 years ago

      Thank you, bhanu_gautam for your response.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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