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

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)," & ")
Dclunie's avatar
Dclunie
Frequent Visitor
3 years ago

Hi All

Does anyone know how I can get this code to work only to show the unique entry Power bi desktop?

Table.AddColumn(#"Removed Columns1", "Combined Deps",
each Text.Combine(List.Select({[#"Assign Dept 1 "],[#"Assign Dept 2 "],[#"Assign Dept 3 "],[#"Assign Dept 4 "]},
each _<> "" and _ <> null),","))

As expected, I got duplicates.

I need to combine ten columns that could have essentially the same department assigned a task.I only want to see one unique entry if data is found.


thanks in advance