Forum Discussion
Conditional M statement to concatenate two columns with additional text only where both are not null
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.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
)
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.
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.
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
)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