Excel Power Pivot & Power Query For Dummies, 2nd Edition
Book image
Explore Book Buy On Amazon
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"

About This Article

This article is from the book:

About the book author:

Michael Alexander is a Microsoft certified application developer and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community.

This article can be found in the category: