Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
anonymous111
Frequent Visitor

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!

 

4 REPLIES 4
Ritaf1983
Super User
Super User

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

1. Merge columns:

Ritaf1983_0-1707878281629.png

2. replace 
'' with '

Ritaf1983_1-1707878432609.png

3. extract text from the first comma:

Ritaf1983_2-1707878700205.pngRitaf1983_3-1707878769392.png

extract the the text before last comma

Ritaf1983_4-1707878858494.png

pbix is attached

 

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

 




Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

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
 
Ritaf1983_0-1707881735812.png

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.

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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. 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.