Help writing M code to concatenate 2 columns with an if statement

[murphm6]

Hi all,

I'm stuck on this and need some help writing an M statement..

I have 3 columns - FirstName, MiddleName, LastName. FirstName and LastName are 100% populated. Some people don't have MiddleNames - leaving it blank

I've tried writing a statement to concatenate all 3 columns with a space - but the statement only works when all three columns are populated.

How can I write a statement that says if MiddleName is blank, then concatenate FirstName and LastName, otherwise concatenate FirstName, MiddleName, and LastName? 

here is what i tried: =

Table.AddColumn(#"Filtered Rows1", "FirstNameLastName", each [FirstName] & if [MiddleName] <> null and [MiddleName] <> "" then " " & [MiddleName] & ". " else " " & [LastName])

[Greg_Deckler · Accepted solution]

@murphm6 You could do this:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Middle", each if [MiddleName] <> null and [MiddleName] <> "" then [MiddleName] & ". " else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [FirstName] & " " & [Middle] & [LastName])