Concacenate and IF isblank

[abukapsoun]

Hi dears,

I have the following,

COL1 COL2 COL3 COL4

A         B         C     D

X         Y         Z

I want to create a new Column to concacenate Col1, Col2, Col3, Col4 however I want to have an IF statement to skip the blank in any of the cells(in the example Col4, row2).. so in row2 I get X, Y,Z instead of X, Y, Z,, 

What would be the formula of that newly created Column?

Many thanks,

[v-ljerr-msft · Accepted solution]

Hi @abukapsoun,

While it's simpler to use M query provided by @MarcelBeug in this scenario, the formula(DAX) below is also for your reference. 

Column = 
CONCATENATEX (
    FILTER (
        UNION (
            ROW ( "COLS", Table1[COL1] ),
            ROW ( "COLS", Table1[COL2] ),
            ROW ( "COLS", Table1[COL3] ),
            ROW ( "COLS", Table1[COL4] )
        ),
        NOT (
            [COLS] = ""
                || ISBLANK ( [COLS] )
        )
    ),
    [COLS],
    ","
)

Regards

[abukapsoun]

@v-ljerr-msft

It worked like charm! thank you.

[MarcelBeug]

Don't know in DAX, but in Power Query it would be simply the lists of values without nulls (assuming blank is null), combined with a comma as separator:

let
    Source = #table(type table[COL1 = text,COL2 = text,COL3 = text, COL4 = text],
                {{"A","B","C","D"},{"X","Y","Z",null}}),
    #"Added Custom" = Table.AddColumn(Source, "Concatenated", each Text.Combine(List.Select({[COL1],[COL2],[COL3],[COL4]}, each _ <> null),","), type text)
in
    #"Added Custom"