Remove trailing Semicolons - Varied Amount

[Anonymous]

How can I remove the trailing;;;; of the names in a column. As the below example of 2 cells, there could be any number of Semicolons.

Anywhere from 1 to 36. How can I remove them?

SEMIGIS, Satina; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; 
KIEGELWYN, Bigny; NIBBS, Lem; MAUR, Kani; KRAGAN, Len; BINGH, Bendy; CRYERS, Lilly; MERMANER, Fruti; ORCHARD, Apple; SERAH, Vee; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Use Text.Remove

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Data], {";"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"})
in
    #"Removed Columns"

This query loads data from a table named Table1 with your text in a column called Data - see file below.

Then adds a custom column which calls Text.Remove.

Finally, delete the original column to leave you with your cleaned text.

Please see file linked to below.  This is an Excel workbook but you can copy/paste the query into the PBI advanced query editor.  Or just copy/paste the query above and modify to suit your data source location.

Phil

Text.Remove.xlsx 

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.