Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.
Useful Microsoft Power Query text functions
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“ |
Useful Microsoft Power Query date functions
The following table contains Excel date functions that help do things like add months, pull out date parts, and get the number of days within a given time period.
| Function | What It Does and How to Use It |
|---|---|
| Date.AddDays | Increments a given date value by a specified number of days. This example returns a date that is seven days from the date in Column1: Date.AddDays([Column1], 7) |
| Date.AddMonths | Increments a given date value by a specified number of months. This example returns a date that is three months earlier than the date in DateColumn1: Date.AddMonths([DateColumn1], -3) |
| Date.AddWeeks | Increments a given date value by a specified number of weeks. This example returns a date that is 12 weeks from the date in DateColumn1: Date.AddWeeks([Column1], 12) |
| Date.AddYears | Increments a given date value by a specified number of years. This example returns a date that is one year earlier than the date in DateColumn1: Date.AddYears([DateColumn1], -1) |
| Date.Day | Returns the day number for a given date value: Date.Day([DateColumn1]) |
| Date.DayOfWeek | Returns a number between 0 and 6 representing the day of the week from a date value: Date.DayOfWeek([DateColumn1]) |
| Date.DayOfYear | Returns a number that represents the day of the year from a date value: Date.DayOfYear([DateColumn1]) |
| Date.DaysInMonth | Returns the number of days in the month from a date value: Date.DaysInMonth([DateColumn1]) |
| Date.Month | Returns the month number from a DateTime value: Date.Month([DateColumn1]) |
| Date.WeekOfMonth | Returns a number for the count of week in the current month: Date.WeekOfMonth([DateColumn1]) |
| Date.WeekOfYear | Returns a number for the count of week in the current year: Date.WeekOfYear([DateColumn1]) |
| Date.ToText | Returns the text representation of a given date. The following example returns the 3-character month name for the date in DateColumn1: Date.ToText([DateColumn1],“MMM“) The following example returns the full month name for the date in DateColumn1: Date.ToText([DateColumn1],“MMMM“) |
