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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are welcome.  If my reply helped, please mark it as Answer.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I have run yor M code and it works, but when I analyzed the code I realized you took advantange that every cell with data in my table has lenght just one (it just for a brief example) and you split column by position. In this case position "1" always works.

       

      Imagine that in the cells of a certain row were just a word like "Luke",  now the split position  would be the length of the word "Luke". In the other rows could be another different length...

       

      I suppose that an algorithm following your idea would be: calculate the maximum of the lenghts of each items on each row, maybe you have to use a dummy column with a value to concatenate further....  or maybe just a different approach to solve the problem!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        There should be a solution to that as well.  Please take a practical example and show the expected result.