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'[Colu...
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.

     

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.

     

    • anonymous111's avatar
      anonymous111
      Frequent Visitor
      2 years ago

      Didn't work-doesn't show any error as well; no changes, is it something to do with string function? trimend function doesn't show up as well.