Forum Discussion
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
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
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
jaineshp I am trying to go with Solution 1. I am just unsure how do I filter out query A records from Query B?