Bringing more than match from two tables

Hey Justas4478,

Great choice going with Solution 1! Here's how to filter out Query A records from Query B:

Step-by-Step Implementation:

Query A (Exact Matches):

= Table.NestedJoin(
Table1,
{"Material", "Plant"},
Table2,
{"Material", "Plant"},
"ExactMatch",
JoinKind.Inner
)

Query B (Material-Only Matches, Excluding Query A):

= let
// First, join on Material only
MaterialOnlyJoin = Table.NestedJoin(
Table1,
{"Material"},
Table2,
{"Material"},
"MaterialMatch",
JoinKind.Inner
),

// Create a composite key for Query A records to exclude
QueryA_Keys = Table.AddColumn(
QueryA,
"CompositeKey",
each [Material] & "|" & [Plant]
),

// Create same composite key for current query
WithCompositeKey = Table.AddColumn(
MaterialOnlyJoin,
"CompositeKey",
each [Material] & "|" & [Plant]
),

// Filter out records that exist in Query A
FilteredResults = Table.SelectRows(
WithCompositeKey,
each not List.Contains(
QueryA_Keys[CompositeKey],
[CompositeKey]
)
)
in
FilteredResults

Final Step - Union Both Queries:

= Table.Combine({QueryA, QueryB})

Pro Tip: Add a custom column in each query before union to identify match type:

• Query A: Add column "MatchType" = "Exact"
• Query B: Add column "MatchType" = "Material Only"

This way you can easily filter and analyze different match types in your final result!

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer