Alternative to DAX GENERATE Function in Power Query

[campbellmurphy]

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

[ImkeF · Accepted solution]

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.

[campbellmurphy]

Thanks Imke, this is a beautiful solution! It's amazing how elegantly you were able to resolve the use case

[Anonymous]

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

[campbellmurphy]

Thanks @Anonymous this looks

promising. I had some issues creating the dict table correctly. Is it possible for you to post the pbix file?

[Anonymous]

it's a bit messy. I hope it will be useful for you.

[campbellmurphy]

Thanks Rocco, I really appreciate your assistance.  In the end I went with another solution but I agree that yours works and is a good alternative

[Anonymous]

take a look at this link, maybe it could be useful, with some adaptations.

[lbendlin]

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.

[campbellmurphy]

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.

[Anonymous]

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.

[lbendlin]

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?

[lbendlin]

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. 

[campbellmurphy]

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

[lbendlin]

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.

[campbellmurphy]

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:

Transformed Data Excel 

Transformed Data Power BI