Forum Discussion
Create table/report with each row and one column value
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!
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/
2 Replies
- AlexisOlson
Super User
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/
- klyjenNew Member
Wow. This was exactly what I needed. Thank you so much!