Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
I want to create a crosstab between two columns "ClientID" and "ProductID", in Power Query.
The goal is to see, by row, which products each client bought. For that, I also added a column with the value 1, like that:
ClientID | ProductID | Value |
1 | 1 | 1 |
2 | 1 | 1 |
3 | 2 | 1 |
2 | 2 | 1 |
3 | 1 | 1 |
4 | 4 | 1 |
1 | 3 | 1 |
1 | 1 | 1 |
I tried to use the Group By option in "ProductID" column, selecting "Value" column for Values Column field and "Sum" for Aggregate Value Function field. However, it returned only 3 rows of ClientID's and I do not understand what is failing.
To conclude, this is the crosstab that I want:
ClientID | 1 | 2 | 3 | 4 |
1 | 1 | 0 | 1 | 0 |
2 | 1 | 1 | 0 | 0 |
3 | 1 | 1 | 0 | 0 |
4 | 0 | 0 | 0 | 1 |
It should be noted that, when a client buys a product once or more, the value that should arise is 1.
The tables showed are just a sample of what I have.
Thanks in advance,
Hello @AlexisOlson and @edhans,
I am so sorry... I thought I had selected Power Query forum, but I have just realized that I didn't.
I want to apply that transformation in PowerQuery because this is not my final table yet. I will apply more transformations after...
I hope you can still help me and thank you very much for your answers.
You did select the PQ forum @valcat27 - I moved it to the Desktop forum because I thought this was about a Matrix visual. Moving it back. 😂
That said, see if this works. It pivots your Product ID column and aggregates using the MAX function.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeJYnWglIxSeMZBlhCJnhCKHUGkCZJnAeSBxYxQeVGUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"ProductID " = _t, Value = _t]),
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"ProductID "]), "ProductID ", "Value", List.Max)
in
#"Pivoted Column"
I would need to know more of your data to know if this makes sense though. Generally do you NOT want to denormalize your data in Power Query like this. It makes subseqent DAX harder as a rule, but that is from my perspective here, not in your seat with your entire project laid out before you.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you @edhans ,
I pasted your code and I got that result but this is just a sample.
Can you help me to adapt the code to my data?
This is my last rows when opening "Advanced Editor":
...
#" Value Column added" = Table.AddColumn(#"Sort Columns", "Value", each 1)
#"Change Type" = Table.TransformColumnTypes(#" Value Column added",{{"ProductID", type text}}),
in
#"Change Type"
I tried to add something like that:
...
#" Value Column added" = Table.AddColumn(#"Sort Columns", "Value", each 1)
#"Change Type" = Table.TransformColumnTypes(#" Value Column added",{{"ProductID", type text}}),
#"Pivoted Column" = Table.Pivot(#"Change Type", List.Distinct(#"Change Type"[#"ProductID"]), "ProductID", "Value", List.Max)
in
#"Pivoted Column"
but it returns me only four rows and not all rows.
You are going to have to give me some sample data or better yet an Excel workbook or PBIX file with the data embedded (Enter Data feature) with an expected result. The code I did above works with your original data, but if it isn't working with your other data, I need to see your real table structure. You should change the data to be fake though.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @edhans ,
Thank you for your effort but I can't share the data needed to create the pivot table. For that reason, I shared that table, which has the same structure that my original data but with far fewer rows and fake values.
This is what I did:
1) Imported data from SQL
2) Changed column type of ProductID from "whole number" to "text".
3) Added "Value" column
4) Changed column type of Value from "any" to "whole number"
At this point I have a table like the one that I shared in the first post. The type of ClientID and ProductID columns are "text" and they have duplicate values. The type of Value column is "whole number" and it only contains the value 1.
After that, I applied your formula and this is how my advanced editor looks like:
let
Source = Sql.Database(…),
#"Change Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}}),
#"Value Column added" = Table.AddColumn(#"Change Type", "Value", each 1),
#"Change Type1" = Table.TransformColumnTypes(#"Value Column added",{{"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Change Type1", List.Distinct(#"Change Type1"[ ProductID]), " ProductID ", "Value", List.Max)
in
#"Pivoted Column"
As result I got one table with only 4 rows (instead of thousands as expected) and thousands of columns (ProductID's) as expected. My problem is the others ClientID's that are not present in this final table.
I don't know. Mine works fine. I had 4 clients and 4 rows. Added a 5th client and it automatically went to 5 rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeJYnWglIxSeMZBlhCJnhCKHUGkCZJnAeSBxYxQeQqUpQmUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"ProductID " = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"ProductID "]), "ProductID ", "Value", List.Max)
in
#"Pivoted Column"
However, we should step back and figure out what your goal is here. You do not want to import a table with thousands of columns into the DAX model. That isn't how it is designed. At. All. Narrow tall tables in a Star Schema format is what it is looking for.
So instead of asking us to help you do something in Power Query you think you need to do something downstream, can you rephrase the question to say "This is my dataset, and this is the visual I want to show" and we can go from there.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @edhans,
Thank you for your recommendations.
Indeed, I do not want to import this table into the DAX model. This is just one of some transformations that I want to apply until to get the final table to import into the DAX model.
My idea was to create this table running a python script. However, it did not work as I showed in this post: IndexError: index 1323169739 is out of bounds for ... - Microsoft Power BI Community
I also tried to create the pivoted table in SLQ and then import it to Power BI but I did not get it either. So, now I was trying to do it in Power Query.
Anyway, I will try another approaches.
Understood @valcat27 - I don't understand why my code didn't work for you. I couldn't get it to not work. Hopefully you find an answer for your solution.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
I get this:
I used the client in the rows, product ID in the column of a Matrix visual, then for Values I used the COUNTROWS() function as shown above. That counts the number of times that the customer bought a product. You don't need the Values column.
However, if the Values is an amount to be aggregated and might not always be 1 in your example, then change my measure from:
Total Value = COUNTROWS('Table')
to this:
Total Value = SUM('Table'[Value])
In this case, they return the same thing, but they will not if your Values column has anything other than a 1 in it. So it depends on what you want, but if it always has 1, get rid of Values and use the Countrows function in the measure.
Alternatively, if you always want a 1 no matter how many times they buy, consider this:
Total Value =
VAR varRecordCount = COUNTROWS('Table')
RETURN
IF(
varRecordCount,
1,
0
)
You still don't need the Values column, and if there are any rows, this will return a 1.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can get the following pretty easily if you put Client on rows and Product on values in a matrix visual with Value in the values field (pick max for the aggregation):
The difficulty comes if you really need the zeros. Since ClientID and ProductdID are in the same table, DAX does some auto-exist optimization behind the scenes so that the empty combinations are never even evaluated and return nothing. Replacing blank with zero in the measure doesn't help at all since the measure is never evaluated.
To get the zeros, you'd need dimension tables so that Client and Product can be filtered independently. See my comments on this related question too.
Check out the July 2025 Power BI update to learn about new features.