Forum Discussion

niark's avatar
niark
Frequent Visitor
9 years ago
Solved

Combine columns if not null or empty

Hi there,   I have some difficulties to combine text columns  with the result I want. If someone could help me to point me to the right direction :)   So I have this table : Col1         Col2   ...
  • niark's avatar
    niark
    9 years ago

    Good catch Marcel but I have other columns :)

     

    Thanks for your help, you pointed me to the right way !

     

    here the solution :

    = Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))

     

    Thanks again

  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Pleased you solved your own issue.

     

    I'd rather had shortened the code a bit:

     

    = Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

If you have no other columns, you use the query editor to add a custom column with formula:

 

= Text.Combine(List.Select(Record.FieldValues(_), each _<> "" and _ <> null)," & ")

 

  • niark's avatar
    niark
    Frequent Visitor
    9 years ago

    Good catch Marcel but I have other columns :)

     

    Thanks for your help, you pointed me to the right way !

     

    here the solution :

    = Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))

     

    Thanks again

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Pleased you solved your own issue.

       

      I'd rather had shortened the code a bit:

       

      = Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
      • niark's avatar
        niark
        Frequent Visitor
        9 years ago

        Yes you are right. I'm frustrated how it seems so easy :)