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.  

         

  • nathancwatkins's avatar
    nathancwatkins
    Icon for Helper II rankHelper II
    1 year ago

    Use Split by Delimiters, and start from the end.

     

    --Nate