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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
oluong
Helper I
Helper I

Need Help with grouping and summing

My first table has all the list of possible table name category:

oluong_0-1593979646000.png

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.

 
 

temp.png

How do I make a pivot to sum the above stuff into this:

DESCRIPTIONCounts
I-Broad impact case4
II-Procedural Appeals3
III-Disc. findings/Remedies issues/Petitions for Enforcement1
IV-Inadequate records12
V-Other Merits appeals2
VI-Settlement0
VII-Administrative Closure5

 

I try it with the pivot and table visual with no luck.  Thank you so much if anyone can help.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community 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:

f1.png

 

You may make all column selected, click 'Transform' ribbon=>'Unpivot columns'.

f2.png

f3.png

 

Then you need to click 'Group by' in 'Transform' ribbon and set as below. Finally you can rename the columns to get the result.

f4.png

f6.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community 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:

f1.png

 

You may make all column selected, click 'Transform' ribbon=>'Unpivot columns'.

f2.png

f3.png

 

Then you need to click 'Group by' in 'Transform' ribbon and set as below. Finally you can rename the columns to get the result.

f4.png

f6.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

amitchandak
Super User
Super User

Hi amitchandak , I'm so new to this..I forgot to mention I created the column here using:

 
 
 
 
 

Untitled.png

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?

DataInsights
Super User
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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