Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
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"
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.
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"
User | Count |
---|---|
19 | |
10 | |
10 | |
9 | |
7 |
User | Count |
---|---|
23 | |
19 | |
16 | |
13 | |
11 |