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.
You will want to clean up your table A. The sample four rows you posted can all be folded into the first row as its wildcard covers all the others. Please check.
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?
It is not completely clear to me what you are looking for. In the attached file there are some transformations to obtain the table C2 starting from the data provided. I'm not sure I understood the logic behind the structure of the examples you produced, but in my interpretation what matters in the PK is only the V character which can be "B" for Body "D" for delivery "N" for New or "?" for any of these. Starting from this, using the groupby function, you get two parts of the C2 table that can be combined to get the complete table.
