Pivot data and column from another to create a new table

[plugwater]

Hi,

I have a table A 

Column1	Column2	Column3	Column4
1	AA	RR	TT
	BB	RR

This needs to be converted into a new table for Table B

ColumnName	Count
Column1	1
Column2	2
Column3	2
Column4	1

This is to plot a visual to see the columns with the most or least values.

Thanks.

[audreygerred · Accepted solution]

Hello! In Power Query you can select all of your columns, go to the Transform tab in the ribbon, then select Unpivot Columns, then rmove blanks from Value. Once you have that done it should look like this:

Once back in Power BI, create the count measure:

Count = COUNTROWS('Table')

[Ritaf1983 · Accepted solution]

Hi @plugwater 
To get the option of analyzing columns as "values", you need to unpivot this table.
The steps on PQ :

1. As there is no category column, except these to unpivot, add the blank column.

2. select this column and "unpivot others"

3 . Filter out the blank rows from values

4. Remove unneeded column

5. Create simple countrows measure  with Dax (after close and apply):

count_rep = COUNTROWS('Table')

PBIX is attached you can download and follow my steps

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[audreygerred · Accepted solution]

Hello! In Power Query you can select all of your columns, go to the Transform tab in the ribbon, then select Unpivot Columns, then rmove blanks from Value. Once you have that done it should look like this:

Once back in Power BI, create the count measure:

Count = COUNTROWS('Table')