Forum Discussion

navafolk's avatar
navafolk
Helper IV
1 year 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 Vendor Type ATT on EPP off   - table 2: LOOKUP Vendor_ID Local_ID Global_ID ATT local1 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year 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

Anonymous's avatar
Anonymous
Not applicable
1 year 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