英文:
how to match a list of values to a table in Power Query
问题
I can help you translate the provided content. Here's the translation:
我正在使用 Microsoft Excel 365 版本 2301 中的 Power Query。
我有两个表格。第一个将一个键与一组数据匹配，第二个将数据与某个值匹配。
#"Tbl1" =
let
Source = #table({"Key", "Data"},
{
{"XXX-1", {"数据 1.1", "数据 1.2"}},
{"XXX-2", {"数据 1.2A"}},
{"XXX-3", {"无法匹配的数据"}}
}
)
in
Source
#"Tbl2" =
let
Source = #table({"Data", "Value"},
{
{"数据 1.1A", 1},
{"数据 1.1B", 2},
{"数据 1.1", 2},
{"数据 1.2A", 3},
{"数据 1.2B", 4},
{"数据 1.2", 4}
}
)
in
Source
我想要创建一个表格，将键与数据列表中的最大值匹配。最终的结果表格应如下所示。
#table({"Key", "最大值"},
{
{"XXX-1", 4},
{"XXX-2", 3},
{"XXX-3", null}
}
)
我该如何在 Power Query 中实现这个目标？
英文:
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?
答案1
得分: 1
你可以尝试这样做：
let 源 = Tbl1,
BufferTbl2= Table.Buffer(Tbl2), //你不需要这一步，但可以加快处理中等大小表的速度
#"添加自定义" = Table.AddColumn(源, "LargestValue", each try List.Max(List.Transform([Data], (x)=> Table.SelectRows(BufferTbl2, each [Data] = x)[Value]{0})) otherwise null),
#"删除列" = Table.RemoveColumns(#"添加自定义",{"Data"})
in #"删除列"
或者
let 源 = Tbl1,
#"扩展数据" = Table.ExpandListColumn(源, "Data"),
#"合并查询" = Table.NestedJoin(#"扩展数据", {"Data"}, Tbl2, {"Data"}, "Tbl2", JoinKind.LeftOuter),
#"扩展Tbl2" = Table.ExpandTableColumn(#"合并查询", "Tbl2", {"Value"}, {"Value"}),
#"分组行" = Table.Group(#"扩展Tbl2", {"Key"}, {{"LargestValue", each List.Max([Value]), type nullable number}})
in #"分组行"
英文:
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"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论