Forum Discussion

AC246's avatar
AC246
Frequent Visitor
1 year ago
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...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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's avatar
    AC246
    1 year ago

    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
    )

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

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's avatar
    AC246
    Frequent Visitor
    1 year ago

    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's avatar
      v-saisrao-msft
      Community Support
      1 year ago

      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's avatar
        AC246
        Frequent Visitor
        1 year ago

        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
        )