Forum Discussion

ExcelKnut's avatar
ExcelKnut
Frequent Visitor
1 year ago
Solved

Remove trailing commas

If values in a column contain a trailing comma, I need to remove it.  However, commas separating values within that column need to be kept.  I tried using Text.TrimEnd but that didn't work.     Any...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi ExcelKnut, what do you mean that Text.TrimEnd didn't work?

     

    Before

    After

    let
    Source = #table(null, {{"Native American,"}, {"Asian, Black,"}}),
    Custom1 = Table.TransformColumns(Source, {{"Column1", each Text.TrimEnd(_, ",")}})
in
    Custom1
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

ExcelKnut Add a new custom column with the following formula:

= Text.Replace(Text.TrimEnd([YourColumnName], ","), ",,", ",")

ExcelKnut's avatar
ExcelKnut
Frequent Visitor
1 year ago

Thanks for your quick response.  Unfortunately, that didn't seem to solve the issue.  

 

Formula used:  Text.Replace(Text.TrimEnd([Race], ","), ",,", ",")

 

Example #1:   Native American,

Example #2:  Asian, Black,

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Use this m
    = if Text.EndsWith([YourColumnName], ",") then
    Text.Start([YourColumnName], Text.Length([YourColumnName]) - 1)
    else
    [YourColumnName]

    • ExcelKnut's avatar
      ExcelKnut
      Frequent Visitor
      1 year ago

      Great formula but for some strange reason it's not removing the trailing comma. 

       

      I copied the table into Excel and it shows the last character is a comma.

       

      However, in Power Query, I added a column to just get the last character in that column, the commas do not appear.    Then, when I get the last two characters, the commas appear.  

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi ExcelKnut,

         

        It looks like your data might have non-breaking spaces or invisible characters after the comma, which Power Query isn't recognizing as a simple trailing comma.

         

        Try by removing removes leading and trailing spaces.

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


        Regards,
        Vinay Pabbu