excel 如何将值列表与Power Query中的表匹配

ozxc1zmp 于 2023-04-22 发布在其他

关注(0)|答案(1)|浏览(195)

我在Microsoft Excel 365版本2301中使用Power Query。
我有两个表。第一个匹配一个键到一个数据列表。第二个匹配数据到一些值。

#"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

我想创建一个表，使Key与Data列表中的最大Value相匹配。最终结果表应如下所示。

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

如何在Power Query中实现这一点？

excel

来源：https://stackoverflow.com/questions/76055753/how-to-match-a-list-of-values-to-a-table-in-power-query

1条答案

按热度按时间

y4ekin9u1#

你可以试试这个

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"

或

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"

赞(0）回复(0）举报 2023-04-22

我来回答

excel 如何将值列表与Power Query中的表匹配

1条答案

相关问题

热门标签

最新问答