Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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]) )
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]) )
And is there any way to list the line (lines) that matched instead of true or false?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
109 | |
89 | |
76 | |
67 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |