Forum Discussion
Alternative to DAX GENERATE Function in Power Query
Hi Everyone,
I have two working tables (A & B) and the ideal transformed result table (C). I need to generate the same table within Power Query instead of DAX. Does anyone know if this is possible and if so how it can be done?
For context I have additional workflow steps later that I believe need to be completed within Power Query based on a lack of response to a prior forum post I've made. It appears that once I create a table within DAX I can no longer make changes to it using Power Query, specifically using the custom column feature unless I export and reimport the table.
The current DAX GENERATE function is as follows:
Table C: GENERATE Table = GENERATE('TABLE A: KPIs',FILTER('TABLE B: Balances',SEARCH('TABLE A: KPIs'[Code (PK)],'TABLE B: Balances'[Code (FK)],,0)))
This problem builds on a prior forum post that I made and was resolved with the GENERATE function.
Thanks
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.
15 Replies
The equivalent Power Query function is likely List.Generate() but you can do this in many different ways, even with recursive functions if you want to explore that. Please provide sample data in usable format (not as a picture) and show the expected outcome.
Hi lbendlin,
I greatly appreciate your assistance. I've included the Sample Data below. I'm not able to post the tables within this reply as it's to large. Table A & Table B are used to generate the expected outcome Table C.
The issue that I had in my previous post is I'm not able to create a relationship between the two tables in a normal PK/FK manner.
Sample Data:
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?
With a single query ... no need to combine
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxb8IwEIX/S2YkfHcEkpFS10itIjWhdSTEEKlDkSqGQv9/zw4ZfLYVlgy5T+/5PZ+Px0LXNTwBQLEoQCn+bv+ut9/h5zy4P0tVLlGhKk6LCUV44wl69qPbZigC7Ihn5LndcBm+koLkvHFOEJ2t4Uk5yn2fL3e1dcg9s5o1Sd+IRCatbnm6Gr1fMyCB44yd96fR34sm21zLWARkOszhm7BWd+bD3j6OE7xoX4doNwSxce22e5vsLWSpGXclKiLCMLtSG7kBHo2dK8kRfGJ0XZW8AZxuQISOQHrEFlySnaFVKnSE8qoYygavZO3AuE5sfwQStI2ey473dxwytVwKZ9r3fSpPLfMAvB8IyxlRcgfU/EjiJBN4+gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code (FK)" = _t, Balance = _t, Country = _t, Period = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code (FK)", type text}, {"Balance", Int64.Type}, {"Country", type text}, {"Period", type date}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Changed Type", "ID", each Text.Range([#"Code (FK)"],4,1)), #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata", {"Period"}, {{"all", each Table.InsertRows(Table.Group(_,{"ID"},{"Balance Sum",(b)=> List.Sum(b[Balance])}),0,{[ID="?",#"Balance Sum"=List.Sum([Balance])]})}}), #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"ID", "Balance Sum"}, {"ID", "Balance Sum"}), #"transfor col" = Table.TransformColumns(#"Tabella all espansa",{{"ID", each Record.Field(dict,_), type text}}) in #"transfor col"where dict is:
you get this result
Thanks Anonymous this looks
promising. I had some issues creating the dict table correctly. Is it possible for you to post the pbix file?
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.Thanks Imke, this is a beautiful solution! It's amazing how elegantly you were able to resolve the use case
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
