Removing Comma after calculated column

[jtkrause14]

I am working with a matrix and created a calcaluted column for a value. The column concatenates all the companies that are in the related to the rows/columns of the matrix with a comma and a line break. however, after the last one I can't seem to find a way to not include the final comma break. 

CompanyNames = 
CALCULATE (
    CONCATENATEX (
        VALUES('Customer'[Fully_Qualified_Name]),
        'Customer'[Fully_Qualified_Name] & ", " & UNICHAR(10),
        "" 
    )

)

[saud968]

@jtkrause14 try this

CompanyNames =
CALCULATE (
CONCATENATEX (
VALUES('Customer'[Fully_Qualified_Name]),
'Customer'[Fully_Qualified_Name] &
IF (
'Customer'[Fully_Qualified_Name] <> LASTNONBLANK('Customer'[Fully_Qualified_Name], 1),
", " & UNICHAR(10),
""
),
""
)
)

[Anonymous · Accepted solution]

Hi @jtkrause14 

I’m sorry to hear that you can’t removing Comma after calculated column, I'll give you another way to do it:

Here's some dummy data

Select "Transform data" to enter the power query

Select “customerA” and “customerB”, and select “Group By” in the “Transform”

Select “Sum” and “Fully_Qualified_Name”

Modify the code

= Table.Group(#"Changed Type", {"customerA", "customerB"}, {{"CompanyName", each Text.Combine([Fully_Qualified_Name], ","), type nullable text}})

Here is the result, and select “Close & Apply”

View in Desktop

Best Regards,

Nono Chen

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

[Fowmy]

@jtkrause14 

Change the -1 in this formual to 2 if necessary 

CompanyNames = 
var __m = 
CALCULATE (
    CONCATENATEX (
        VALUES('Customer'[Fully_Qualified_Name]),
        'Customer'[Fully_Qualified_Name] & ", " & UNICHAR(10),
        "" 
    )

)
var __result =  left( __m, len(__m)-1)
RETURN
    __result

[jtkrause14]

I get this error when I try to use the left function:

[Fowmy]

Try this please:

CompanyNames = 
var __m1 = 
CALCULATE (
    CONCATENATEX (
        VALUES('Customer'[Fully_Qualified_Name]),
        'Customer'[Fully_Qualified_Name] & ", " & UNICHAR(10),
        "" 
    )

)

VAR __m = CONVERT ( __m1 , STRING )
var __result =  left( __m,  LEN(__m)-1)
RETURN
    __result

[jtkrause14]

Still getting the same error.