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

Question

Hello all,&nbsp;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&nbsp;Value111211321221311441131111&nbsp;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.&nbsp;&nbsp;To conclude, this is the crosstab that I want:ClientID123411010211003110040001It 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.&nbsp;&nbsp;Thanks in advance,&nbsp;

alexisolson · Answer

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

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.

edhans · Answer

valcat27&nbsp;
&nbsp;
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.
&nbsp;
However, if the Values is an amount to be aggregated and might not always be 1 in your example, then change my measure from:
&nbsp;
Total Value = COUNTROWS('Table')
&nbsp;
to this:
&nbsp;
Total Value = SUM('Table'[Value])

&nbsp;
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.
&nbsp;
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&nbsp;any&nbsp;rows, this will return a 1.

edhans · Answer

You did select the PQ forum&nbsp;valcat27&nbsp;- I moved it to the Desktop forum because I thought this was about a Matrix visual. Moving it back.&nbsp;😂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"
&nbsp;
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.
&nbsp;
How to use M code provided in a blank query:1) In Power Query, select New Source, then Blank Query2) On the Home ribbon, select "Advanced Editor" button3) Remove everything you see, then paste the M code I've given you in that box.4) Press Done5) See this article if you need help using this M code in your model.

edhans · Answer

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"
&nbsp;
However, we should step back and figure out what your goal is here. You&nbsp;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.&nbsp;
&nbsp;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.

edhans · Answer

Understood&nbsp;valcat27&nbsp;- 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.

Forum Discussion

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

10 Replies

Related content

Create columns group

Creating a date column

Creating a custom column

unpivoting columns and creating datetime column

Appending columns and creating new column of old column headers

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

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