Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;
Solved! Go to 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
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.