Merge columns keep null

[Dicken]

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 

[Jai-Rathinavel · Accepted 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,

Jai Rathinavel | LinkedIn

[slorin]

Hi @Dicken 

with Table.CombineColumns

= Table.CombineColumns(
Source,
{"Text", "Text2"},
each if List.NonNullCount(_)>0 then Text.Combine(_) else null,
"Merged"
)

Stéphane 

[rohit1991 · Accepted solution]

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"

[Dicken]

Thank you, 

[Jai-Rathinavel · Accepted 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,

Jai Rathinavel | LinkedIn