Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks Imke, this is a beautiful solution! It's amazing how elegantly you were able to resolve the use case
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?
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
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.
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.
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?
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:
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 |
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: