Create table/report with each row and one column value

[klyjen]

Hi -- I am a PowerBI newbie so thanks in advance for any assistance!

I have a data source organized like this:

Code	ValueA	ValueB	ValueC	ValueD	...ValueAX
Code01	0	3	2	0
Code02	4	3	0	6
Code03	0	0	4	0
Code04	2	5	4	8

I am trying to manipulate the data to get something that only shows the Value column(s) where the values aren't zero. I can envision it like this:

Code 01

ValueB - 3

Value C - 2

Code 02

ValueA - 4

Value B - 3

ValueD - 6

Code03

ValueC - 4

Code04

ValueA - 2

ValueB - 5

ValueC - 4

ValueD - 8

or even like this:

Code	ValueLabel	ValueValue
Code01	ValueB	3
Code01	ValueC	2
Code02	ValueA	4
Code02	ValueB	3
Code02	ValueD	6
Code03	ValueC	4
Code04	ValueA	2
Code04	ValueB	5
Code04	ValueC	4
Code04	ValueD	8

Note that there are about 35 'value' columns currently, but this will grow/shrink in other instances of this data. I'd love something that allows me to run this against the data regardless of the number of columns.

I keep picturing it as a query to populate a new table, like

For Each [Code.row],

For Each [Value.column]

If Value.Column>0,

Create row (Code.row, Value.Column, Value)

Next

Next

(I'm not a coder, but that is the logic I want to use so that the # of columns and # of rows can be variable.)

Is this even possible? Any suggestions would be appreciated. Thanks in advance!

[klyjen]

Wow. This was exactly what I needed. Thank you so much!

[AlexisOlson · Accepted solution]

This is much easier in Power Query than you might expect. All you need to do is unpivot the Value columns and filter out the zeros.

See also: https://www.howtoexcel.org/power-query/how-to-unpivot-data-with-power-query/