Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

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

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Syndicated - Outbound

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"

 

View solution in original post

2 REPLIES 2
spinfuzer
Solution Sage
Solution Sage

Syndicated - Outbound

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

 

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

 

spinfuzer_0-1701470652827.png

 

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
Super User
Super User

Syndicated - Outbound

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"

 

avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)