When cleaning dirty data, you might have used the Trim-function (Text.TrimStart or Text.TrimEnd) to delete leading or trailing whitespace from your strings. However, did you know that you could use these functions as well to delete any other characters as well from the start or end of a string? Trimming text with custom characters is pretty straightforward:
Task: Trimming text with custom characters
Say you have a column with values like so
Trimming text with custom characters
and want to delete every number at the end and also every “-” that is directly connected with a number. So that the final output shall look like so:
Trim custom characters at the end of a string.
Optional parameter
By default, you feed just one argument into the Text.TrimStart or Text.TrimEnd function: The string whose whitespace characters shall be removed.
Text.TrimEnd(text as nullable text, optional trim as any) as nullable text
But the second argument lets you define a list of your own characters to be removed respectively. So I can create a list with all the characters that shall be removed from the end like so:
{"0".."9"} & {"-"}
This concatenates 2 lists: The first list contains 10 elements: All numbers as strings. The second list has just one element in it: “-“. I have to put this element into a list as well for being able to use the ampersand (“&”) as an easy concatenator here.
So the full expression for the “Add custom column” dialogue looks like so:
Text.TrimEnd( [MyColumnName], {"0".."9"} & {"-"} )
To see this in action, you can simply paste this code into the advanced editor and follow the steps:
Enjoy and stay queryious 😉
The post Trimming text with custom characters in Power BI and Power Query appeared first on The BIccountant.