Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, if I have columns with nulls and want to merge can I retian the null values where both columns contian nulls
example ;
let
Source = #table(
type table [Text = Any.Type, Text2 = Any.Type],
{{"a", "b"}, {"b", null}, {null, null}, {"c", "e"}, {null, null}, {"d", "d"}}
),
#"Merged Columns" = Table.CombineColumns(
Source,
{"Text", "Text2"},
Combiner.CombineTextByDelimiter(""),
"Merged"
)
in
#"Merged Columns"
so where both are null show null ?
Richard
Solved! Go to Solution.
Hi @Dicken
To make sure null values remain unchanged when both columns contain null, you can adjust your approach with conditional logic. Try the below M code it should work !
let
Source = #table(
type table [Text = Any.Type, Text2 = Any.Type],
{{"a", "b"}, {"b", null}, {null, null}, {"c", "e"}, {null, null}, {"d", "d"}}
),
#"Merged Columns" = Table.AddColumn(Source, "Merged", each
if [Text] = null and [Text2] = null then null
else Text.Combine({[Text], [Text2]}, "")
)
in
#"Merged Columns"
Output:
Appreciate a Kudos 👍
Thanks,
Proud to be a Super User! | |
Hi @Dicken ,
Yes, it’s absolutely possible to retain null values when both columns contain null while merging in Power Query. The default Table.CombineColumns function doesn't differentiate between actual nulls and empty strings, so it will merge even two nulls into an empty string, which may not be what you want. To preserve null in cases where both columns are null, you can use a conditional column instead. This approach checks if both source columns are null, and if so, sets the result to null; otherwise, it combines the values. This gives you full control and preserves data integrity in scenarios like yours.
Here’s how you can implement it:
let
Source = #table(
type table [Text = Any.Type, Text2 = Any.Type],
{{"a", "b"}, {"b", null}, {null, null}, {"c", "e"}, {null, null}, {"d", "d"}}
),
#"Merged Columns" = Table.AddColumn(Source, "Merged", each
if [Text] = null and [Text2] = null then null
else Text.Combine({[Text], [Text2]}, "")
)
in
#"Merged Columns"
Hi @Dicken
with Table.CombineColumns
= Table.CombineColumns(
Source,
{"Text", "Text2"},
each if List.NonNullCount(_)>0 then Text.Combine(_) else null,
"Merged"
)
Stéphane
Hi @Dicken ,
Yes, it’s absolutely possible to retain null values when both columns contain null while merging in Power Query. The default Table.CombineColumns function doesn't differentiate between actual nulls and empty strings, so it will merge even two nulls into an empty string, which may not be what you want. To preserve null in cases where both columns are null, you can use a conditional column instead. This approach checks if both source columns are null, and if so, sets the result to null; otherwise, it combines the values. This gives you full control and preserves data integrity in scenarios like yours.
Here’s how you can implement it:
let
Source = #table(
type table [Text = Any.Type, Text2 = Any.Type],
{{"a", "b"}, {"b", null}, {null, null}, {"c", "e"}, {null, null}, {"d", "d"}}
),
#"Merged Columns" = Table.AddColumn(Source, "Merged", each
if [Text] = null and [Text2] = null then null
else Text.Combine({[Text], [Text2]}, "")
)
in
#"Merged Columns"
Thank you,
Hi @Dicken
To make sure null values remain unchanged when both columns contain null, you can adjust your approach with conditional logic. Try the below M code it should work !
let
Source = #table(
type table [Text = Any.Type, Text2 = Any.Type],
{{"a", "b"}, {"b", null}, {null, null}, {"c", "e"}, {null, null}, {"d", "d"}}
),
#"Merged Columns" = Table.AddColumn(Source, "Merged", each
if [Text] = null and [Text2] = null then null
else Text.Combine({[Text], [Text2]}, "")
)
in
#"Merged Columns"
Output:
Appreciate a Kudos 👍
Thanks,
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
7 | |
7 | |
6 | |
6 |