Merge columns keep null

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

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"