August 28, 2020
Power Query: M functions
For Power BI version 2.76.5678.782 (December 2019).
M language enables us to create powerful transformations in Power Query editor. We use custom functions for steps that repeat often in data editing. We already saw how to create custom function in Power Query in Power Query Objects and Dynamic Removal of Rows in Power Query for All the Files in a Folder articles. Let’s look at how to create a function that takes the data from a file and returns an edited table.
We often save data in multiple, almost identical fields. In cases like this it is useful to edit a single file, save the steps into a function and use it to edit the all the other files.
In our example we’re dealing with multiple files containing online ads of used vehicles. We use the following steps.
1. Import data to Power BI with Get data.
2. Select Transform Data.
3. A file preview is opened in Power Query.
4. Edit the table. In our case we remove redundant column with Remove Columns and change the column order.
Query is now ready to be turned into a function.
Creating a function
We turn a query into a function. Function can then be used on all the remaining files. We use the following steps.
1. Select the query, in our case named fn.
2. Open Advanced Editor.
3. To the start of the code we add (SourceFileName)=>. We replace name of the file with SourceFileName all throughout the code. This is now the input of the function.
4. Confirm with Done.
5. Query is now a function. Icon of the query changes. Name is now written in italic letters, meaning the query will not load to data model.
Calling the function
We can now use the function to edit the remaining files. We enter the full path to the next file and confirm with OK. Function edits the data and returns the finished table.