Merge 4 Columns ONLY if not Null

[Anonymous]

I have 4 columns I want to Merge, some Cols have Null others dont, so end result looks like

cust1,toys,,bikes

cust2,,,bikes

cust3,toys,,

etc

how can i merge only if not null so end result will look like this....just a cleaner output

cust1,toys,bikes

cust2,bikes

cust3,toys

etc

[spinfuzer]

Replace Combiner.CombineTextByDelimiter(",", QuoteStyle.None) with

each Text.Combine(List.RemoveNulls(_),",")

Or instead of transforming the column, just Add Column Ribbon --> Merge and it will already remove the nulls from my own testing.  You can then manually delete the columns you just merged.

[AlexisOlson · Accepted solution]

Add a custom column with the formula 

Text.Combine(Record.ToList(_), ",")

Each row in the table is a record like [Column1 = "cust1", Column2 = "toys", Column3 = null, Column4 = "bikes"] and Record.ToList converted this record into a list like {"cust1", "toys", null, "bikes"} and Text.Combine concatenates them.

Full sample query:

let
    Source =
        Table.FromRows(
            {
                { "cust1", "toys", null, "bikes" },
                { "cust2", null, null, "bikes" },
                { "cust3", "toys", null, null }
            },
            type table [Column1 = text, Column2 = text, Column3 = text, Column4 = text]
        ),
    #"Added Custom" = Table.AddColumn(Source, "Merge", each Text.Combine(Record.ToList(_), ","), type text)
in
    #"Added Custom"