Conditional M statement to concatenate two columns with additional text only where both are not null

[AC246]

I'm an 'M' novice hoping for your kind help with a Power Query issue. I have some data in which each row may have one, two, or no identifiers, currently housed in two separate columns (let's call them 'Identifier 1' and 'Identifier 2'), as per the below screenshot example.

I'm trying to create a new column which lists all available identifiers for each row, with multiple values separated by a comma and space where relevant. Or in other words:

• If (Identifier 1 column) has a value AND (Identifier 2 column) has a value, then return the text string "[Identifier 1 value], [Identifier 2]".
• If only (Identifier 1 column) has a value, then return [Identifier 1 value].
• If only (Identifier 2 column) has a value, then return [Identifier 2 value].
• If neither column has a value, return null.

I've been trying to write this as an 'If, then' statement but that first scenario, checking if both columns have a value, seems to be causing problems. I keep getting values in my final column along the lines of ", " or ", [Identifier 2 value]", so my M statement seems to be applying the text string output to any scenario, not just to the scenario I want (both Identifier 1 and Identifier 2 having a value).

Might anyone have ideas to help with this? Additionally, if you have the time, a quick explanation as to why/how your solution works (or a link to further information) would be helpful just to assist me in learning. Thank you!

My draft statement looks something like this:

= Table.AddColumn(#"Renamed Columns1", "Combined identifiers list", each if ([#"Source Name.Identifier 1"] <> null and [#"Source Name.Identifier 2"] <> null) then [#"Source Name.Identifier 1"] & ", " & [#"Source Name.Identifier 2"] else if [#"Source Name.Identifier 1"] <> null then [#"Source Name.Identifier 1"] else if [Source Name.Identifier 2] <> null then [Source Name.Identifier 2] else null)

[v-saisrao-msft · Accepted solution]

Hi @AC246,

I have replicated your scenario using the sample data provided. Below is the output I obtained, and I have also attached the pbix file for your reference.

Hope this helps
Thank you.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(List.RemoveNulls(Record.ToList(_)), each Text.From(_)),", "))
in
    #"Added Custom"

Hope this helps.

[pankajnamekar25]

Hello @AC246 

Try this M code

= Table.AddColumn(
#"Renamed Columns1",
"Combined identifiers",
each
if [Identifier 1] <> null and [Identifier 2] <> null then
Text.From([Identifier 1]) & ", " & Text.From([Identifier 2])
else if [Identifier 1] <> null then
Text.From([Identifier 1])
else if [Identifier 2] <> null then
Text.From([Identifier 2])
else
null
)

[AC246]

Thank you for this code; however unfortunately I'm still encountering issues in the results. I'm still getting just ", " in the output column in some rows, along with some ", 1234" and "5678, " type values. I'm not sure if I'm failing to properly translate your logic into my own statement, or if there's still something else going on.

[v-saisrao-msft · Accepted solution]

Hi @AC246,

I have replicated your scenario using the sample data provided. Below is the output I obtained, and I have also attached the pbix file for your reference.

Hope this helps
Thank you.

[AC246 · Accepted solution]

Thank you for this, it's very helpful!

In the meantime, some attempts with ChatGPT yielded a statement which also works quite well:

= Table.AddColumn(#"Renamed Columns1", "Combined identifiers", each
let
id1 = Text.Trim(Text.From([#"Data source.Identifier 1"])),
id2 = Text.Trim(Text.From([#"Data source.Identifier 2"]))
in
if id1 <> "" and id2 <> "" then
id1 & ", " & id2
else if id1 <> "" then
id1
else if id2 <> "" then
id2
else
null
)

[v-saisrao-msft]

Hi @AC246,

Thank you for sharing the method you tried. By converting and trimming the values first, it consistently manages nulls and blanks, making the logic more dependable.

Thank you