Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
valcat27
Helper III
Helper III

Create a Crosstab with 1 and 0, based on two columns in Power Query

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:

ClientIDProductID Value
111
211
321
221
311
441
131
111

 

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:

ClientID1234
11010
21100
31100
40001

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, 

10 REPLIES 10
valcat27
Helper III
Helper III

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.

edhans_0-1616779965863.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hello @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.

edhans_0-1617131204739.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hello @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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

@valcat27 

 

I get this:

edhans_0-1616707630107.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
AlexisOlson
Super User
Super User

You 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):

AlexisOlson_0-1616706932328.png

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors