Forum Discussion

anonymous111's avatar
anonymous111
Frequent Visitor
2 years ago
Solved

concatenate multiple columns null value, remove commas, DAX

Hello everyone, 

I tried to combine 6 columns using :

Column name= 'DataSource'[Column 1]&", "&'DataSource'[Column 2]'&", "&DataSource'[Column 3]&", "& 'DataSource'[Column 4]&", "&'DataSource'[Column 5]&", "&'DataSource'[Column 6]

 

Some of the column are empty/null so as a result I am getting are:

 

text,,,,,

text1,text2,,,,

text1,text2,text4,,,

 

Basically the null value from any column ends up as extra comma at the end.

How can I remove that comma from the end?

Thanks!

 

General Comment
Need Help
Show and Tell
Tutorial Requests
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi anonymous111 
    This is easier to resolve from PQ .
    You need just 3 steps :

    1. Merge columns:

    2. replace 
    '' with '

    3. extract text from the first comma:

    extract the the text before last comma

    pbix is attached

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     




  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi anonymous111 
    You can use this DAX:

    merged =
    var
    concantenated = [Column1]&","&[Column2]&","&[Column3]&","&[Column4]&","&[Column5]&","&[Column6]
    var
    replace_caommas=
    SUBSTITUTE(concantenated,",,",",")
    var
    replace_caommas_repeat=
    SUBSTITUTE(replace_caommas,",,",",")
    var
    remove_comma_first_Char =if(LEFT(replace_caommas_repeat,1)=",",
    REPLACE(replace_caommas_repeat,1,1,""),replace_caommas_repeat)
    RETURN
    remove_comma_first_Char
     

    The updated pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi anonymous111 
    This is easier to resolve from PQ .
    You need just 3 steps :

    1. Merge columns:

    2. replace 
    '' with '

    3. extract text from the first comma:

    extract the the text before last comma

    pbix is attached

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     




    • anonymous111's avatar
      anonymous111
      Frequent Visitor
      2 years ago

      Thank you for the screenshot, but data source I am working on, it doesn't allow me to access pq. 

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        2 years ago

        Hi anonymous111 
        You can use this DAX:

        merged =
        var
        concantenated = [Column1]&","&[Column2]&","&[Column3]&","&[Column4]&","&[Column5]&","&[Column6]
        var
        replace_caommas=
        SUBSTITUTE(concantenated,",,",",")
        var
        replace_caommas_repeat=
        SUBSTITUTE(replace_caommas,",,",",")
        var
        remove_comma_first_Char =if(LEFT(replace_caommas_repeat,1)=",",
        REPLACE(replace_caommas_repeat,1,1,""),replace_caommas_repeat)
        RETURN
        remove_comma_first_Char
         

        The updated pbix is attached

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.