Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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

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

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"

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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