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.
Hi lbendlin,
I've had another look at simplyfying Table A. With wildcards it contains 2k rows. Each wildcard could be one of 35 characters A-Z 1-9. After accounting for multiple wildcards within a single row there will be roughly 4 milion permutations including duplicates (which need recounted as they appear as seperate KPIs). That includes two instances of four single wildcards in a one row so 35^4 (roughly 1.5 million rows per).
Ideally there would be a simplier solution. If not do you know how I can effienctly generate these wildcard permutations?
As mentioned in my original post I was able to generate the necessary permutations (not all four million) using GENERATE in DAX. However this created a seperate issue where I needed to use Power Query at a later date. It doesn't appear to be possible to create a column using Power Query on a table that was created using GENERATE in DAX based on a my recent forum post.
I may not have explained my self well. Looking at this
|
Code (PK) |
KPI Number |
KPI Name |
Department |
|
E99??1?1 |
1 |
Total Sales |
Overall |
|
E99?B1?1 |
2 |
Total Sales |
Body Shop |
I think that row 2 will never hit because its pattern is already covered by the row 1 pattern. What am I missing?