Forum Discussion
Post ProdigyBringing more than match from two tables
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.
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
FilteredResultsFinal 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
4 Replies
- jaineshp
Memorable Member
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
Justas4478jaineshp I am trying to go with Solution 1. I am just unsure how do I filter out query A records from Query B?
ronrsnfldSuper User
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
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
FilteredResultsFinal 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