Bringing more than match from two tables

[Justas4478]

Hi I have two tables that I am trying to match.

First table has:

Second table has:

 
I am trying to bring everything from second table to the first and I am matching on basis of 1 (product number aka(Material) 2 Plant.
The only problem is it only bring those items that as well have exactly same plant.
I want to bring back item matched with material and plant, but as well same items even if Plant does not match as long as product number match.
I tried different joint's but results are same.
I as well tried fuzzy matching but it then matches not the same product numbers which is a problem.
The only other solution that I can think of is to only match on product number and ignore plant.
Please let me know if there are any other solutions that I could try.

[ronrsnfld]

Why bother matching (also) on plant if you want to bring over matching product numbers even if plant does not match?

Just match on the product number.

[jaineshp · Accepted solution]

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

[jaineshp]

Hey @Justas4478,

Based on your requirement to match records from both tables while handling cases where Plant codes don't match exactly, here are several solutions you can implement:

Solution 1: Union-Based Approach (Recommended)

Step 1: Create two separate queries

• Query A: Join tables on both Material AND Plant (exact matches)
• Query B: Join tables on Material only, then filter out records already captured in Query A

Step 2: Union both queries to get comprehensive results

• This ensures you get exact matches first, then additional matches based on Material only
• Eliminates duplicate records automatically

Solution 2: Conditional Column Approach

Step 1: Perform a left join on Material (Product Number) only Step 2: Add a custom column to categorize matches:

• "Exact Match" - where both Material and Plant match
• "Partial Match" - where only Material matches Step 3: Sort results to prioritize exact matches over partial matches

Solution 3: Multiple Merge Operations

Step 1: Start with your first table as base Step 2: Merge with second table using Material + Plant (Inner Join) Step 3: For unmatched records, perform second merge using Material only Step 4: Combine results from both merge operations

Solution 4: Fuzzy Matching with Threshold Control

Step 1: Use Fuzzy Matching but set strict similarity threshold (95-100%) Step 2: Apply fuzzy matching only on Material column, not Plant Step 3: Manually verify fuzzy matches to ensure accuracy

Solution 5: Power Query M Code Approach

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

Then add second join for Material-only matches on unmatched records.

Implementation Recommendation

Primary Approach: Use Solution 1 (Union-Based) as it provides:

• Complete control over matching logic
• Clear distinction between exact and partial matches
• No risk of incorrect fuzzy matching
• Maintains data integrity

Alternative: If you need simpler implementation, use Solution 2 with conditional columns to flag match types for easy filtering and analysis.

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

Best Regards,
Jainesh Poojara | Power BI Developer

[Justas4478]

@jaineshp I am trying to go with Solution 1. I am just unsure how do I filter out query A records from Query B?