Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Merge 4 Columns ONLY if not Null

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

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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"

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    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"

     

  • spinfuzer's avatar
    spinfuzer
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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.