Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to remove leading commas from the concatenated String

Hi Team,

 

I have a concatenated text string like ",176.22.191.10,None,254.255.244.0" . This is a concantenated value comprises of certain values. Customer doesnt want to see the Leading comma at the begining of the Value. Commas can be there between two values , but not at begining. 

 

Could you please help me to remove it ? I tried the below formulae , but it didnt worked for this scenario. 

 

Comma Removal = IF(LEFT([List of Prod_Gateway_IP_Address values],1)=",",SUBSTITUTE([List of Prod_Gateway_IP_Address values],",",""),[List of Prod_Gateway_IP_Address values])

Need Help

9 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

     

    Try this:

     

    Comma Removal =
IF (
    LEFT ( [List of Prod_Gateway_IP_Address values], 1 ) = ",",
    MID (
        [List of Prod_Gateway_IP_Address values],
        2,
        LEN ( [List of Prod_Gateway_IP_Address values] ) -1
    ),
    [List of Prod_Gateway_IP_Address values]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      AlB ,

       

      Thanks for the reply. I tried your method and it is giving the same results of my method . Please find the screen shot below . It is removing the Comma, but giving results in seperate rows . So i have concatenated "Comma Removal" again and it started giving commas again. 

       

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous 

         

        I don't understand what you are showing in that table or what you are trying to do. You'll have to explain it in detail and/or share the pbix. Otherwise I cannot help. Is that a table visual? hat fields are involved? From what table(s)? what is the structure of those tables?  Is 'Comma removal' a measure? and [List of Prod_Gateway_IP_Address values]? I thought they were both calculated columns but it doesn't seem so.  

  • SharathS03's avatar
    SharathS03
    New Member
    1 year ago

    Hi All,

    Leading commas are the result of the BLANK values table values which we are trying to concatenate. For example if I am trying to concatenate values in a table column which contains BLANK, the first value in the concatenated string is blank because of which we dont see the value but just the "," (comma) after that. The solution for this is to filter out blank in the first step.

     

    Quarters

     
    Q1
    Q2
    Q3

     

    If I concatenate this Quarters column values, using the formula  

     

    CONCATENATEX( VALUES (Table[Quarters]),Table[Quarters],",")

     

    It will return ,Q1,Q2,Q3

     

    The easiest way to tackle this is to update the formula to EXCLUDE Blank values

     

    CONCATENATEX( CALCULATETABLE(VALUES (Table[Quarters]),Table[Quarters]<>BLANK()),Table[Quarters],",")

     

     This will return Q1,Q2,Q3