cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
67nmark Regular Visitor
Regular Visitor

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

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

3 REPLIES 3
SivaMani Senior Member
Senior Member

Re: Create a reference column on which to summarize

@67nmark,

 

Is that really what you're looking for?

 

Index.gif

Super User
Super User

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

Highlighted
67nmark Regular Visitor
Regular Visitor

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 49 members 1,003 guests
Please welcome our newest community members: