Forum Discussion
Helper INeed Help with grouping and summing
My first table has all the list of possible table name category:
My second table name DataCollection is a table that collected data based on those category above. The category are seperated into individual column in the DataCollection table and we mark it 1 if it belongs to it.
How do I make a pivot to sum the above stuff into this:
| DESCRIPTION | Counts |
| I-Broad impact case | 4 |
| II-Procedural Appeals | 3 |
| III-Disc. findings/Remedies issues/Petitions for Enforcement | 1 |
| IV-Inadequate records | 12 |
| V-Other Merits appeals | 2 |
| VI-Settlement | 0 |
| VII-Administrative Closure | 5 |
I try it with the pivot and table visual with no luck. Thank you so much if anyone can help.
Hi, oluong
Based on your description, I'd like to suggest you use 'unpivot' and 'group by' feature in 'Query Editor'. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may make all column selected, click 'Transform' ribbon=>'Unpivot columns'.
Then you need to click 'Group by' in 'Transform' ribbon and set as below. Finally you can rename the columns to get the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- DataInsights
Super User
In Power Query, select all Category columns (e.g., Cat I, Cat II) in the query DataCollection, and select Unpivot Columns. These columns will be pivoted into two columns: Attribute and Value. Rename these as desired. Then, create a measure that counts the value in each Category:
Category Count = COUNT ( DataCollection[Value] )
Add this measure to a matrix with the Category table (DESCRIPTION field) as rows.
- amitchandak
oluong , Not very to me transpose or unpivot should help
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
oluongHi amitchandak , I'm so new to this..I forgot to mention I created the column here using:
In the links you gave me, it assume that the columns were to be created in the "Transform Data" using the editor, so I could not use the pivot and unpivot article. If I have to put these columns Cat I...Cat II using th query editor, I didn't recognize the dax codes, what would your further expertise recommend?
v-alq-msftCommunity Support
Hi, oluong
Based on your description, I'd like to suggest you use 'unpivot' and 'group by' feature in 'Query Editor'. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may make all column selected, click 'Transform' ribbon=>'Unpivot columns'.
Then you need to click 'Group by' in 'Transform' ribbon and set as below. Finally you can rename the columns to get the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- oluong
Hi Allan,
Thank you so much for your the pbix, I did left out a detail, the reply to amitchandak contains the details. Hopefully after you read the reply you can give me some more pointers.
