how to match a list of values to a table in Power Query

Question

I am using Power Query in Microsoft Excel 365 version 2301.

I have 2 Tables . The first matches a Key to a list of Data. The second matches Data to some Value.

#"Tbl1" = 
let
    Source = #table({"Key", "Data"},
        {
            {"XXX-1", {"Data 1.1", "Data 1.2"}},
            {"XXX-2", {"Data 1.2A"}},
            {"XXX-3", {"unmatchable data"}}
        }
    )
in
    Source


#"Tbl2" =
let
    Source = #table({"Data", "Value"},
        {
            {"Data 1.1A", 1},
            {"Data 1.1B", 2},
            {"Data 1.1", 2},
            {"Data 1.2A", 3},
            {"Data 1.2B", 4},
            {"Data 1.2", 4}
        }
    )
in
    Source

I would like to create a table that matches Key to the largest Value in the Data list. The final result table should look like this.

#table({"Key", "Largest Value"},
    {
        {"XXX-1", 4},
        {"XXX-2", 3},
        {"XXX-3", null}
    }
)

How can I achieve this in Power Query?

Answer 1

You can try this

let Source = Tbl1,
BufferTbl2= Table.Buffer(Tbl2),  //you dont need this step, but speeds up the process for moderately large tables
#"Added Custom" = Table.AddColumn(Source, "LargestValue",  each try List.Max(List.Transform([Data],  (x)=> Table.SelectRows(BufferTbl2, each [Data] = x)[Value]{0})) otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"})
in  #"Removed Columns"

or

let Source = Tbl1,
#"Expanded Data" = Table.ExpandListColumn(Source, "Data"),
#"Merged Queries" = Table.NestedJoin(#"Expanded Data", {"Data"}, Tbl2, {"Data"}, "Tbl2", JoinKind.LeftOuter),
#"Expanded Tbl2" = Table.ExpandTableColumn(#"Merged Queries", "Tbl2", {"Value"}, {"Value"}),
#"Grouped Rows" = Table.Group(#"Expanded Tbl2", {"Key"}, {{"LargestValue", each List.Max([Value]), type nullable number}}) 
in #"Grouped Rows"