Forum Discussion
Alternative to DAX GENERATE Function in Power Query
Hi campbellmurphy ,
if you're interested in a more generic approach for this kind of wildcard matches, you can check out the file enclosed.
It creates wildcard profiles for each kind of wildcard distribution and uses a simple join at the end. It should be fairly fast as long as there are not too many different wildcard profiles.
In Power Query you have unlimited options for creating custom joins, very similar to what you can do in SQL (like joins, for example) Read up on Table.AddColumn, especially the custom columnGenerator function - i found that to be extremely impressive.
Assuming that the question mark is a single character placeholder your table A has a potential conflict between the first row and all the other rows, as the pattern in the first row covers the patterns in the other rows. Please clarify.
Thanks lbendlin I'll look into Table.Addcolumn and columnGenerator.
The two complications are:
- The values under Code in Table A include multiple wildcards indicated by the question mark as a single character placeholder
- There is a many to many relationship between Code, the Primary Key in Table A and Code, the Foreign Key Code in Table B given these wildcards
I've included the tables below:
Table A: KPIs, ~2k rows
Code (PK) | KPI Number | KPI Name | Department |
E99??1?1 | 1 | Total Sales | Overall |
E99?B1?1 | 2 | Total Sales | Body Shop |
E99?D1?1 | 3 | Total Sales | Pre-Delivery |
E99?N1?1 | 4 | Total Sales | New Vehicles |
Table B: Balances, ~200k rows
Code (FK) | Balance | Country |
E991B111 | 100 | Australia |
E991B121 | 200 | USA |
E991B131 | 300 | Canada |
E992B111 | 400 | UK |
E992B121 | 500 | China |
E992B131 | 100 | Australia |
E993B111 | 200 | USA |
E993B121 | 300 | Canada |
E993B131 | 400 | UK |
E991D111 | 500 | China |
E991D121 | 100 | Australia |
E991D131 | 200 | USA |
E992D111 | 300 | Canada |
E992D121 | 400 | UK |
E992D131 | 500 | China |
E993D111 | 100 | Australia |
E993D121 | 200 | USA |
E993D131 | 300 | Canada |
E991N111 | 400 | UK |
E991N121 | 500 | China |
E991N131 | 100 | Australia |
E992N111 | 200 | USA |
E992N121 | 300 | Canada |
E992N131 | 400 | UK |
E993N111 | 500 | China |
E993N121 | 100 | Australia |
E993N131 | 200 | USA |
Table C: Transformed Data, ~2k rows
Code (PK) | KPI Number | KPI Name | Department | Total |
E99??1?1 | 1 | Total Sales | Overall | 7800 |
E99?B1?1 | 2 | Total Sales | Body Shop | 2500 |
E99?D1?1 | 3 | Total Sales | Pre-Delivery | 2600 |
E99?N1?1 | 4 | Total Sales | New Vehicles | 2700 |