- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
User | Count |
---|---|
8 | |
5 | |
5 | |
5 | |
4 |
User | Count |
---|---|
12 | |
11 | |
9 | |
7 | |
6 |