Create a reference column on which to summarize

[67nmark]

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

[SivaMani]

@67nmark,

Is that really what you're looking for?

[Zubair_Muhammad · 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

Step # 2

Group by VALUE and ATTRIBUTE and aggreagate by COUNTROWS

Step 3

Pivot the attribute Column using Count column as Values

[67nmark]

@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