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)," & ")
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 :)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello MacelBeug

     

    Would you be so kind, if possible, to give me a hint to solve my problem. It is very close to the one you solved here. There's no concatenation, and it is very close to the problem of union of sets in Set Theory...

     

    There are sometimes I get the value for a key column using some join. Sometimes you look in serveral sources to get the values. In some sources you get the value but not in others.... At the end you only wish a column with a key value

    Well I believe the best is to show a sample. I would like to get the red values in "column e"
    Thank you very much in advance

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Here is the M code i used

       

      let
          Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}, {"Text4", type text}}),
          #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Text1", "Text2", "Text3", "Text4"}),
          #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "Merged", each Text.Combine({[Text1], [Text2], [Text3], [Text4]}, ""), type text),
          #"Split Column by Position" = Table.SplitColumn(#"Inserted Merged Column", "Merged", Splitter.SplitTextByPositions({0, 1}, false), {"Merged.1", "Merged.2"}),
          #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1", type text}, {"Merged.2", type text}}),
          #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2"})
      in
          #"Removed Columns"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you very  much! It works fine! I'm trying to learn M, and this a is a very good and really useful example!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    MarcelBeug 

     

    Do u know how to achieve the same result in DAX. I also go the same question. Since i am using direct query I cannot do any transormations using M query.

     

    Regards,

    Maddy         

  • 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