Remove trailing commas

[ExcelKnut]

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 help is greatly appreciated!!!

Jeff

[dufoq3 · Accepted solution]

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

[v-vpabbu]

Hi @ExcelKnut,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @ExcelKnut,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @ExcelKnut,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[SundarRaj]

Hi @ExcelKnut , you could probably give a go at this solution and see if it suits you. I'll leave the code and output below. Thanks

[bhanu_gautam]

@ExcelKnut Add a new custom column with the following formula:

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

[nathancwatkins]

Use Split by Delimiters, and start from the end.

--Nate

[ExcelKnut]

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]

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

[ExcelKnut]

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.  

[v-vpabbu]

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]

Thanks Vinay, 

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

Jeff