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
67nmark
Helper I
Helper I

Create a reference column on which to summarize

Hello again,

 

I have a 1000 row dataset from survey monkey. There are 6 columns of data (Col1,Col,...,Col6). The value in each row across all columns is a number, 1 - 6.

 

I'm new to Power BI so what I'm struggling with is how to create the row labels in my output table.

 

Thanks in advance,

Mark

 

power-bi.png

1 ACCEPTED SOLUTION

Hi @67nmark

 

You can easily do it in QUERY EDITOR with a series of unpivoting, GROUPING and Pivoting back

 

See the atatched file here

 

Step#1

 

Select All Columns and unpivot them

 

Step1.jpg

 

Step # 2

 

Group by VALUE and ATTRIBUTE and aggreagate by COUNTROWS

 

Step2.png

 

Step 3

 

Pivot the attribute Column using Count column as Values

 

Step3.png


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
SivaMani
Resident Rockstar
Resident Rockstar

@67nmark,

 

Is that really what you're looking for?

 

Index.gif

Hi @67nmark

 

You can easily do it in QUERY EDITOR with a series of unpivoting, GROUPING and Pivoting back

 

See the atatched file here

 

Step#1

 

Select All Columns and unpivot them

 

Step1.jpg

 

Step # 2

 

Group by VALUE and ATTRIBUTE and aggreagate by COUNTROWS

 

Step2.png

 

Step 3

 

Pivot the attribute Column using Count column as Values

 

Step3.png


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad you've just opened a door in my Power Query knowledge. Thanks for the great set of instructions and the sample file, much appreciated.

 

Cheers,

Mark

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.