Combine Columns with 1 value in each to one column with entire list

[Anonymous]

EDIT: I totally forgot that all other cells say "null" and the original answer isn't working with that tossed in. Any way to disregard the nulls?

Hello,

How would I combine columns that have one value per row? For example:

Column 1	Column 2	Column 3	Column 4	Column 5
Blue	null	null	null	null
null	Green	null	null	null
null	null	null	Yellow	null
null	null	null	null	Orange
null	null	Pink	null	null

Only one of these columns can contain data for each row. How do I collapse this to:

Column

Blue

Green

Yellow

Orange

Pink

[jdbuchanan71 · Accepted solution]

In the query editor you can add a custom column that combines your existing into 1.

[jdbuchanan71 · Accepted solution]

If you select all your columns you can right click on them an go to replace and replace null with (leave this box empty)

[jdbuchanan71 · Accepted solution]

If you select all your columns you can right click on them an go to replace and replace null with (leave this box empty)

[jdbuchanan71 · Accepted solution]

In the query editor you can add a custom column that combines your existing into 1.

[Anonymous]

Okay, one more wrinkle.....all the other fields have "null" in them so my result is all "null". How can I work around that?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

You could also create a calculated table using below dax expression:

Table 2 =
UNION (
    CALCULATETABLE (
        VALUES ( 'Table'[Column 1] ),
        FILTER ( 'Table', 'Table'[Column 1] <> "null" )
    ),
    CALCULATETABLE (
        VALUES ( 'Table'[Column 2] ),
        FILTER ( 'Table', 'Table'[Column 2] <> "null" )
    ),
    CALCULATETABLE (
        VALUES ( 'Table'[Column 3] ),
        FILTER ( 'Table', 'Table'[Column 3] <> "null" )
    ),
    CALCULATETABLE (
        VALUES ( 'Table'[Column 4] ),
        FILTER ( 'Table', 'Table'[Column 4] <> "null" )
    ),
    CALCULATETABLE (
        VALUES ( 'Table'[Column 5] ),
        FILTER ( 'Table', 'Table'[Column 5] <> "null" )
    )
)

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

So simple Thank you! I'm still very new and appreciate the help!