如何将 PowerQuery 中的两个表与多列匹配之一连接起来? [英] How to join two tables in PowerQuery with one of many columns matching?

问题描述

假设我们有两个 PowerQuery 查询:

Let's assume we have two PowerQuery queries:

1. 一个名为 Query1 的查询，包含 Col1_1 和 Col1_2
2. 一个名为 Query2 的查询，包含 Col2_1 和 Col2_2

1. a query named Query1 with columns Col1_1 and Col1_2
2. a query named Query2 with columns Col2_1 and Col2_2

我知道可以使用这样的合并查询来连接这两个查询:

I know it's possible to join these two queries with a merge query like this:

let
    Source = Table.NestedJoin(Query1,{"Col1_1", "Col1_2"},Query2,{"Col2_1", "Col2_2"},"Query2",JoinKind.LeftOuter)
in
    Source

在 SQL 中，这可以表示为

In SQL, this could be represented as

SELECT
    *
FROM
    Query1
    LEFT JOIN Query2 ON Query1.Col1_1 = Query2.Col2_1 AND Query1.Col1_2 = Query2.Col2_2

问题:如果两列中的至少一列匹配，是否可以连接这两个查询?在 SQL 中，这可以表示为

Question: is it possible to join the two queries if at least one of the two columns match? In SQL, this could be represented as

SELECT
    *
FROM
    Query1
    LEFT JOIN Query2 ON Query1.Col1_1 = Query2.Col2_1 OR Query1.Col1_2 = Query2.Col2_2

推荐答案

据我所知，在 PQ 的 Join 函数中没有改变默认匹配方法的选项.但是，您可以对所需的每一列进行两次连接，然后组合这些连接的结果.

As far as I know there are no options to alter the default method of matching in Join functions in PQ. You could however do two joins on each of the columns you want, then combine the results of those joins.

当 col1 和 col2 都匹配时，这将导致重复匹配，我不确定这是否是预期的结果.如果没有，您可以使用 PQ 设置索引来捕获这些重复项并删除它们.

This would result in duplicate matches when both col1 and col2 match, which I'm not sure if that is the intended result. If not, you can use PQ to setup indexes to catch these duplicates and remove them.

假设 Query2 也添加了如下所示的索引:

Assuming Query2 also has had an Index added that looks like this:

let
    Source = Query1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"col1"},Query2,{"col1"},"col1Join",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"col2"},Query2,{"col2"},"col2Join",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries1", "MergeTables", each Table.Combine({[col1Join],[col2Join]})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "MergeTables", {"col1", "col2", "Index"}, {"Query2.col1", "Query2.col2", "Query2.Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Custom", {"Index", "Query2.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Index", "col1Join", "col2Join", "Query2.Index"})
in
    #"Removed Columns"

不是一个非常可扩展的解决方案，但我认为它可以正常工作?

Not a very scale-able solution, but I think it works correctly?

这篇关于如何将 PowerQuery 中的两个表与多列匹配之一连接起来?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！