Forum Discussion
Solved
Merge with wildcards
Hello Power BI Community,
I need to find (in other words set a flag for) the rows in table A which matches any of the rows in Table B. If it wasn't for the wildcards (*) it would have been easy to create a combined key in each table and merge the tables. (Wildcard means the rule is valid for any value in that column.)
How can I achieve this?
Table A: Transactions, 100000+ rows
| Company | Div | Dept | Invoice |
| 22 | A | 1321908 | 711058256 |
| 22 | C | 2231020 | 4211264977 |
| 22 | B | 2233407 | 4211274926 |
| 22 | A | 2233615 | 4211275016 |
| 22 | D | 2233615 | 4213075816 |
| 22 | A | 21208600 | 4213075886 |
| 22 | A | 387700 | 4213075886 |
| 22 | A | 31129 | 4213076187 |
| 22 | C | 23100800 | 4513002378 |
| 24 | B | 21101406 | 4513002382 |
| 24 | A | 162120 | 51803336279 |
| 24 | D | 22408000 | 6621870 |
| 24 | A | 3510012 | 6625197 |
| 24 | A | 1321807 | 6626487 |
| 24 | A | 22503000 | 6627186 |
| 24 | C | 22407000 | 6627635 |
Table B: Rules, ~50 rows
| Company2 | Div2 | Dept2 | Invoice2 |
| * | A | 162120 | * |
| 24 | D | * | * |
| * | A | 3510012 | * |
| * | A | 1321807 | * |
| * | A | 22503000 | * |
| 24 | C | * | * |
| 22 | * | * | 711058256 |
| 22 | C | 2231020 | 4211264977 |
| 22 | B | 2233407 | * |
| * | * | 2233615 | * |
| * | D | 2233615 | * |
| * | * | 21208600 | * |
| * | * | 387700 | * |
Best Regards,
Daniel
You may use code below to add a custom column.
let r = _ in Table.MatchesAnyRows( Rules, each List.Contains({r[Company], "*"}, [Company2]) and List.Contains({r[Div], "*"}, [Div2]) and List.Contains({r[Dept], "*"}, [Dept2]) and List.Contains({r[Invoice], "*"}, [Invoice2]) )
2 Replies
You may use code below to add a custom column.
let r = _ in Table.MatchesAnyRows( Rules, each List.Contains({r[Company], "*"}, [Company2]) and List.Contains({r[Div], "*"}, [Div2]) and List.Contains({r[Dept], "*"}, [Dept2]) and List.Contains({r[Invoice], "*"}, [Invoice2]) )
