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
npatten
Helper II
Helper II

summarize data by cell values

Hi--

 

Trying to solve a summarization problem. I'm working with survey data for a client. Currently the data output is structed with questions as the column headers, and the cell values (converted from text to numerical) are the responses, something like this: 

 

Q1Q2Q3Q4Q5
11100
01111
11000
01011
10001
01110

 

However, my clients are very interested in stacked charts to review survey responses, so I need the results to be transformed into something like this:

 

 10
Q132
Q241
Q330
Q430
Q530

 

This does is not seem to be a transpose or unpivot problem (I've tried), but I suspect it's a sophisticated summarization problem. My DAX skills are decent but not amazing. I have created lots of tables using the SUMMARIZE function, but I just can't seem to wrap my head around this problem. Any help would be very appreciated. 

 

Thank you in advance!

1 ACCEPTED SOLUTION
calerof
Impactful Individual
Impactful Individual

Hi @npatten,

 

It is indeed a Power Query transformation with Unpivot. See:

 

Unpivot all columnsUnpivot all columnsShow in a matrix with CountShow in a matrix with Count

Hope it helps.

Regards,

Fernando

 

View solution in original post

2 REPLIES 2
calerof
Impactful Individual
Impactful Individual

Hi @npatten,

 

It is indeed a Power Query transformation with Unpivot. See:

 

Unpivot all columnsUnpivot all columnsShow in a matrix with CountShow in a matrix with Count

Hope it helps.

Regards,

Fernando

 

Thanks Fernando, it appears I was just over thinking the problem!

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.