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.
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.
Solved! Go to Solution.
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
26 | |
26 |
User | Count |
---|---|
100 | |
87 | |
45 | |
43 | |
35 |