If you find you’re always doing the same transformation or calculation, why not turn it into a function? This can simplify the steps in your query and allow you to re-use a solution.
The Problem
A while ago, I wrote a post on 4 ways to remove unwanted space characters from text data. Two solutions I looked at were the TRIM function in Excel and using Power Query. Excel’s TRIM function is easy to use and does a great job. It will remove all space characters from the start and end of a text string, as well as remove excess spaces between words. So when there’s more than one space between a word, it will replace them with a single space. There is also a Text.Trim function in power query, but it only takes off spaces from the start and end of a text string. No spaces between words are removed. My solution in the post was to apply the Trim command then use the Replace Values command to replace instances of double spaces with single spaces. Then repeat the process until there are no more double spaces. Not a great solution to be honest. It means repeating a step which takes a bit more effort. But more importantly, if new data comes in then the number of Replace Values steps might not be sufficient anymore. So we’re going to create a power query function to replicate Excel’s superior TRIM function.
Video Tutorial
M Code For The Query Function
This function takes a string of text and replaces double spaces with single spaces and repeats the process recursively until it doesn’t find any more double spaces. Then it applies the Text.Trim function to take care of any leading or trailing space characters. Creating a recursive function solves the problem of manually applying repeated Replace Values steps to remove double spaces and is a much more elegant and re-usable solution.
Create A Blank Query
The first step to creating a query function is to create a new blank query. This is where we will place the M code for our function.
Go to the Data tab ➜ then go to Get Data ➜ Choose From Other Sources ➜ then choose Blank Query. This will open up the power query editor.
We can give the query a name using the Name box in the Properties section. This is what will be used to reference the query function, so we should give it a good descriptive name.
Add M Code To The Advanced Editor
Now we need to add the M code to the query and we can do this in the advanced editor. To open the advanced editor go to the Home tab and press the Advanced Editor button.
Copy and paste the code into the editor and press the Done button. We can then Close & Load the query to save the query function.
Using The Query Function
After we close and load the query function, we will see the query listed in the Queries & Connections window pane. The icon will will be a small fx symbol to denote it’s a function. Now, how can we use this function? We can use it in any query by creating a custom column.
Go to the Add Column tab and select Custom Column.
We can give our new column a name and in the formula editor we can add our formula. This will apply the fExcelTrim query function to each row of the [Text to Trim] column.
Conclusions
Building query function in power query is a great way to simplify and re-use queries. We can also build solutions which just aren’t possible with the power query editor UI alone. With the UI, there is no way to add transformation steps recursively, but this is fairly easy in M code and functions.
