Forum Discussion

Anonymous

Not applicable

2 years ago

Solved

How do I pivot a dimension count while keeping the ID rows?

Hi everyone.

This is what I want to do in Power Query. It's seemlingly very simple but I haven't been able to find the answer.

When I pivot by Product, it gives me the total product count for the whole set. But I want the count grouped by ID, as seen in the image below.

PBIX: https://drive.google.com/file/d/1tgQD-FD9PRQ1okqPZyMW2pXLzKUtMB_5/view?usp=sharing

Thanks for your time.

Anonymous

Hi, Anonymous

You can refer to amitchandak advice, his suggestion does work in Desktop using Matrix to achieve what you need, and if you want to achieve the effect in Power Query, you can refer to the following method.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyE9VitWBcJwTi+DsgJzEPIRMSFEqghNcWJpZVJSaAxYwghqiAOfBTDFCMcUIXaMxirQxsiXG2NTCXWqCotEEWaMJukYTZI2mSI4zRTHEFG5ILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDs = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDs", type text}, {"Product", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Trim, type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Product]), "Product", "Index", List.NonNullCount)
in
    #"Pivoted Column"

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Untitled.pbix28 KB

2 Replies

Anonymous

Not applicable

Hi, Anonymous

You can refer to amitchandak advice, his suggestion does work in Desktop using Matrix to achieve what you need, and if you want to achieve the effect in Power Query, you can refer to the following method.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyE9VitWBcJwTi+DsgJzEPIRMSFEqghNcWJpZVJSaAxYwghqiAOfBTDFCMcUIXaMxirQxsiXG2NTCXWqCotEEWaMJukYTZI2mSI4zRTHEFG5ILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDs = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDs", type text}, {"Product", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Trim, type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Product]), "Product", "Index", List.NonNullCount)
in
    #"Pivoted Column"

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Untitled.pbix28 KB

amitchandak
Super User
2 years ago
Anonymous , I matrix visual, put id on row, product on column and count measure in values. it should work