Forum Discussion
Power Query Merge with LIKE % type logic
A question for Power Query experts. No AI replies please. Thank you.
I know that Power Query Merge has a Fuzzy logic function,
but is there any method to do table joins using LIKE % wildcards ?
For example I want to join this Sales Table and Lookup Table to produce a report showing the total Amount of each Fruit that has been sold
Note that:-
- the % in the lookup table is wildcard text.
- wildcard text can be any length
- the % wildcard text can be at the start, end or both of the code!
For example:-
- BCDZFGMDS is BCDZF% Apples
- HIMRTY is %RTY Oranges
- ZUIPLKQGS is %PLK% Oranges
I know that this could be achived by adding a Conditonal columns, but each condition would need to be hardcoded.
Whereas I need a dynamic solution that uses just the two tables
I have provided screen prints and a OneDrive link to PBIX for you to create and share a solution .
Many thanks, I look forward to your answer !
Sales Table
Lookup Table
Hi speedramps
Try the following:
let _likecode = Lookup[Likecode], _fruit = Lookup[Fruit], _code = [Code], // helper function that evaluates a single LIKE pattern MatchesLike = (pattern as text, code as text) as logical => let startsWithPct = Text.StartsWith(pattern, "%"), endsWithPct = Text.EndsWith(pattern, "%"), inner = if startsWithPct and endsWithPct then Text.Middle(pattern, 1, Text.Length(pattern) - 2) else if startsWithPct then Text.End(pattern, Text.Length(pattern) - 1) else if endsWithPct then Text.Start(pattern, Text.Length(pattern) - 1) else pattern, result = if startsWithPct and endsWithPct then Text.Contains(code, inner) else if startsWithPct then Text.EndsWith(code, inner) else if endsWithPct then Text.StartsWith(code, inner) else code = inner in result, // find all matching positions (0-based) _returnedPositions = List.Select( List.Positions(_likecode), each MatchesLike(_likecode{_}, _code) ), // get corresponding fruits for those positions _fruitMatches = List.Transform(_returnedPositions, each _fruit{_}) in _fruitMatchesNote: I used AI to figure this out.
7 Replies
speedramps have you looked at fuzzy matching options. How fuzzy matching works in Power Query - Power Query | Microsoft Learn
Hey speedramps ,
You can try the following steps for achieving the dynamic solution:
1) Reference Sources2) Function that implements LIKE with % at start/end/both
3) For each Sales row, find all lookup rows whose Likecode matches the Code
4) Break ties by specificity (longest literal after removing %)
5) Aggregate total Amount by Fruit
Output:
Check out the pbix file attached.
Best Regards,
Nasif AzamPower Query does not natively support SQL-style LIKE % wildcard joins directly in the Merge dialog, but you can dynamically replicate this logic by expanding all combinations and applying custom logic in a filtered step.
Steps for Dynamic LIKE (%) Joins in Power Query
Add an Index to both Sales and Lookup tables (to help with joining if needed).
Create a custom column in the Lookup Table to split or detect the position of the '%' wildcard (start, end, both).
Use a Cartesian join:
Add a dummy key to both tables (e.g., “JoinKey” = 1).
Merge tables on this key to generate all possible combinations.
Add a custom column to handle the LIKE logic:
For each row, replace the wildcard rules with Power Query (M) string matching functions:
For %FRTY, check if SalesCode ends with ‘FRTY’
For PLK%, check if SalesCode starts with ‘PLK’
For %PLK%, check if SalesCode contains ‘PLK’
Filter out rows that do not match the criteria.
Example M Logic for Wildcard Matches
= Table.AddColumn(MergedTable, "Match", each if Text.StartsWith([SalesCode], Text.BeforeDelimiter([LookupPattern], "%")) and Text.EndsWith([SalesCode], Text.AfterDelimiter([LookupPattern], "%")) then true else if Text.StartsWith([LookupPattern], "%") and Text.EndsWith([LookupPattern], "%") and Text.Contains([SalesCode], Text.Middle([LookupPattern], 1, Text.Length([LookupPattern]) - 2)) then true else if Text.StartsWith([LookupPattern], "%") and Text.EndsWith([SalesCode], Text.AfterDelimiter([LookupPattern], "%")) then true else if Text.EndsWith([LookupPattern], "%") and Text.StartsWith([SalesCode], Text.BeforeDelimiter([LookupPattern], "%")) then true else false )Then filter the table to rows where [Match] = true and aggregate as needed.
This approach is fully dynamic and adapts as your Lookup Table changes, with no need to hardcode conditions
Hi speedramps
Try the following:
let _likecode = Lookup[Likecode], _fruit = Lookup[Fruit], _code = [Code], // helper function that evaluates a single LIKE pattern MatchesLike = (pattern as text, code as text) as logical => let startsWithPct = Text.StartsWith(pattern, "%"), endsWithPct = Text.EndsWith(pattern, "%"), inner = if startsWithPct and endsWithPct then Text.Middle(pattern, 1, Text.Length(pattern) - 2) else if startsWithPct then Text.End(pattern, Text.Length(pattern) - 1) else if endsWithPct then Text.Start(pattern, Text.Length(pattern) - 1) else pattern, result = if startsWithPct and endsWithPct then Text.Contains(code, inner) else if startsWithPct then Text.EndsWith(code, inner) else if endsWithPct then Text.StartsWith(code, inner) else code = inner in result, // find all matching positions (0-based) _returnedPositions = List.Select( List.Positions(_likecode), each MatchesLike(_likecode{_}, _code) ), // get corresponding fruits for those positions _fruitMatches = List.Transform(_returnedPositions, each _fruit{_}) in _fruitMatchesNote: I used AI to figure this out.
Hi speedramps,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian , Shubham_rai955 , Nasif_Azam and parry2k for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi speedramps,
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarPower Query does not natively support SQL-style LIKE % wildcard joins directly in the Merge dialog, but you can dynamically replicate this logic by expanding all combinations and applying custom logic in a filtered step.
Steps for Dynamic LIKE (%) Joins in Power Query
Add an Index to both Sales and Lookup tables (to help with joining if needed).
Create a custom column in the Lookup Table to split or detect the position of the '%' wildcard (start, end, both).
Use a Cartesian join:
Add a dummy key to both tables (e.g., “JoinKey” = 1).
Merge tables on this key to generate all possible combinations.
Add a custom column to handle the LIKE logic:
For each row, replace the wildcard rules with Power Query (M) string matching functions:
For %FRTY, check if SalesCode ends with ‘FRTY’
For PLK%, check if SalesCode starts with ‘PLK’
For %PLK%, check if SalesCode contains ‘PLK’
Filter out rows that do not match the criteria.
Example M Logic for Wildcard Matches
= Table.AddColumn(MergedTable, "Match", each if Text.StartsWith([SalesCode], Text.BeforeDelimiter([LookupPattern], "%")) and Text.EndsWith([SalesCode], Text.AfterDelimiter([LookupPattern], "%")) then true else if Text.StartsWith([LookupPattern], "%") and Text.EndsWith([LookupPattern], "%") and Text.Contains([SalesCode], Text.Middle([LookupPattern], 1, Text.Length([LookupPattern]) - 2)) then true else if Text.StartsWith([LookupPattern], "%") and Text.EndsWith([SalesCode], Text.AfterDelimiter([LookupPattern], "%")) then true else if Text.EndsWith([LookupPattern], "%") and Text.StartsWith([SalesCode], Text.BeforeDelimiter([LookupPattern], "%")) then true else false )Then filter the table to rows where [Match] = true and aggregate as needed.
This approach is fully dynamic and adapts as your Lookup Table changes, with no need to hardcode conditions.
