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
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

      • ExcelKnut's avatar
        ExcelKnut
        Frequent Visitor
        1 year ago

        Thanks Vinay, 

         

        I'll give that a try.  I appreciate your time.

         

        Jeff