The Microsoft Power Query functions in the table that follows are helpful text functions to know. You can use them to clean and manipulate textual strings.
| Function | What It Does and How to Use It |
|---|---|
| Text.Contains | Returns true if a specified value is found within a given text field. Use this function with the If function to return a value based on a condition: if Text.Contains([Column1], "usd") then "US" else "Canadian" |
| Text.EndsWith | Returns true if a specified value is found at the end of a given text field. Use this function with the If function to return a value based on a condition: if Text.EndsWith([Column1], "est") then "Eastern" else "Central" |
| Text.Insert | Inserts a specified value at a given position into a given text field. This example inserts two dashes starting at the 5 character in the field: Text.Insert([Column1], ,5,"--") |
| Text.Length | Returns the number of characters in a given text field. Use this function with the If function to return a value based on a condition: if Text.Length([Column1]) >5 then "US Zip" else "Canadian Postal" |
| Text.PadEnd | Appends a specified value to the end of a given text field until it is at least a given length. This example inserts enough zeros at the end of the field to make the length at least 10 characters: Text.PadEnd([Column1], 10, "0") |
| Text.PadStart | Appends a specified value to the start of a given text field until it is at least a specified length. This example inserts enough zeros at the start of the field to make the length at least 10 characters: Text.PadStart([Column1], 10, "0") |
| Text.Remove | Removes all occurrences of a specified character or list of characters from a given text field. This example removes all occurrences of the non-alphanumeric character in the specified list (enclosed in curly brackets): Text.Remove([Column1],{"/", "\","?",":","|", "",">", "*"}) |
| Text.StartsWith | Returns true if a specified value is found at the start of a given text field. Use this function with the If function to return a value based on a condition: if Text.StartsWith([Column1], "Uncle") then "Brother" else "Sister" |
