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