Forum Discussion
Remove trailing Semicolons - Varied Amount
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; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;
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
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.
1 Reply
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
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.