cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Create a reference column on which to summarize

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

Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Highlighted
Solution Sage
Solution Sage

Re: Create a reference column on which to summarize

@67nmark,

 

Is that really what you're looking for?

 

Index.gif

Highlighted
Super User III
Super User III

Re: Create a reference column on which to summarize

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

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Helper I
Helper I

Re: Create a reference column on which to summarize

@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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors