Forum Discussion

jtkrause14's avatar
jtkrause14
New Member
2 years ago
Solved

Removing Comma after calculated column

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

)

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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's avatar
      jtkrause14
      New Member
      2 years ago

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

       

       

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

  • saud968's avatar
    saud968
    Memorable Member
    2 years ago

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