Forum Discussion

klyjen's avatar
klyjen
New Member
4 years ago
Solved

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:

CodeValueAValueBValueCValueD...ValueAX
Code010320 
Code024306 
Code030040 
Code042548 

 

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:

CodeValueLabelValueValue
Code01ValueB

3

Code01ValueC

2

Code02ValueA

4

Code02ValueB3
Code02ValueD

6

Code03ValueC4
Code04ValueA2
Code04ValueB5
Code04ValueC4
Code04ValueD8

 

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!