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
