Concatenate in Blank Fileds in Power Query

[harishs]

Hi Team,

How do I Concatenate 3 columns, filling only in NULL/BLANK Cells of other column?

In a report we work, it has 'First Name', 'Middle Name', 'Last Name' and 'FULL NAME' columns
1. 'FULL NAME' already has information, however for few people there is no data, which will be NULL/BLANK.
2. I need to concatenate, 'First Name' & 'Middle Name' & 'Last Name' together to get 'FULL NAME'.
2a. This logic should be applied only in NULL/BLANK fields in 'FULL NAME' column.

Regards,
Harish S

 

[Ron Coderre]

Using this sample data in an Excel Table named Table1

First        Middle   Last        Full
Alpha        J        Zero        Alpha Jan Zero
Bravo        F        Clark       Bravo F Clark
Charlie      Mar      Davis        
Delta        Apr      Hass        Delta A Hass
Echo         May      Forrest     Echo May Forrest
Foxtrot      Jun      Sage        Foxtrot Jun Sage
Golf         J        Berg

This query replaces nulls in the Full field with concatenated First, Middle and Last names.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"First", type text}, {"Middle", type text}, {"Last", type text}, {"FullName", type text}}),
    ReplaceNulls = Table.ReplaceValue(SetDataTypes,null,each [First] & " " & [Middle] & " " & [Last],Replacer.ReplaceValue,{"FullName"})
in
    ReplaceNulls

These are the results:

First        Middle   Last        FullName
Alpha        J        Zero        Alpha Jan Zero
Bravo        F        Clark       Bravo F Clark
Charlie      Mar      Davis       [B]Charlie Mar Davis[/B]
Delta        Apr      Hass        Delta A Hass
Echo         May      Forrest     Echo May Forrest
Foxtrot      Jun      Sage        Foxtrot Jun Sage
Golf         J        Berg        [B]Golf J Berg[/B]

Is that something you can work with?

 

[harishs]

Hi Ron,

Thanks a ton for the solution... working exactly the way it was expected.

One small upgrade is required (realised only after using the code given above).
In some cases, we found NULLs in Middle & Last Name... can we have the Nulls to be ignored in Concatenate?

I am very new to Power Query and this is my first attempt to create a database using Power Query hence this ask.

Regards,
Harish S

 

[billszysz]

Change this part

[COLOR=#333333][First] & " " & [Middle] & " " & [Last][/COLOR]

to this one

Text.Combine(List.RemoveNulls(Record.ToList(_)), " ")

 

[Ron Coderre]

Slight enhancement to Bill's code...in case your data has other fields that you don't want to concatenate:

    ReplaceNulls = Table.ReplaceValue(SetDataTypes,null,each 
        Text.Combine(
            List.RemoveNulls(
                Record.ToList(
                    Record.SelectFields(_,{"First","Middle","Last"})
                )
            )
        , " "),Replacer.ReplaceValue,{"FullName"})

 

[harishs]

Thanks a lot, Ron and billszysz for the solutions provided.

I have gone ahead with Ron's code since I was following his code.

Regards,
Harish S