Custom functions are a great feature available in the query editor (Power BI or Excel), and allow you to simplify your code and/or re-use it to save time across multiple files/models. Power BI provides the Combine & Transform feature to easily combine the data from a folder of files after performing a custom transformation on each. While this feature enables new users to quick get started, it does have some limitations dealing with files that have small differences (e.g., sheet or table names) and automatically generates queries, a function, and a parameter that confuses some. The approach described in this article isn't really for new users either, but is a simple way to convert any query into a function at any step.

The approach is demonstrated with a query used to combine files, as that is a common scenario involving custom functions, but can be applied to many others. After using either the Folder or SharePoint Folder connectors, if the user chooses "Transform Data" (instead of the highlighted "Combine" button), a table with the metadata for each file in the folder (or site) is seen in the query editor. In this example, a folder with three mock Excel files, each containing data formatted as a table on a single worksheet, is used.

The [Content] column contains the Binary file content. Before describing the custom function approach, at this point, one could add a Custom Column that uses Excel.Workbook([Content]) to see the workbook contents in table form (e.g., sheets and tables), or even drill into the workbook with a more advanced formula like Excel.Workbook([Content]){0}[Data] to get the data table found in the Data column for the first record. Or one could transform the [Content] column with a similar formula (to avoid having to remove the original [Content] column with the Binary content later. See the video for a demonstration of the above. You can also perform simple data transformations by wrapping the above M code with functions like Table.PromoteHeaders(Table.RemoveFirstN(Excel.Workbook([Content]){0}[Data])). This works well for a small number of simple transforms (and if you are comfortable editing in the Formula Bar), but for more complex transformations a custom function is needed.

It is possible to automatically convert a query into a function by right clicking on it in the query editor. However, if your query contains no parameter (usually at the Source step), then no parameter is available in the created function. Also, the function begins at the first step of the query. When combining files, there are usually a few steps needed to get to the view above where the custom function is needed (i.e., to accept and transform the Binary data for each file in the [Content] column). This is addressed with the native Combine & Transform functionality through the automatic creation of a parameter, a #"Sample File" query, a #"Transform Sample File" query, and its linked #"Transform File" function. However, that can also be achieved as described below.

Step 1 – Right click and duplicate the original query, and rename it something that reflects it is (will be) a function.

Step 2 – Drill down to the contents of one of the files, either by clicking on the word "Binary" or by right clicking in one of those cells and choosing "Drilldown". This will add two steps to the new query. The key one is the Navigation step. The #"Imported Excel" step was automatically added by the query editor, since an Excel file was detected in the previous step. Leave the #"Imported Excel" step there, as it is needed as part of the function.

Step 3 – Open the Advanced Editor, note that the Navigation step is actually called "Content", and add a duplicate Content step with the following M code, and "comment it out" by hitting Ctrl + / while the mouse is on that line. You can uncomment it out by clicking Ctrl + / again. The (filecontent) is defining a parameter with that name to be passed into the function and the => is the syntax to create a function. Note that two steps cannot have the same name, so one of them must be commented out at a time.

(filecontent)=> let Content = filecontent in Content

At this point, the Advanced Editor window looks like this (query mode).

Step 4 – Leave the new line commented out, close the Advanced Editor, and make additional data transformations. When done, re-open the Advanced Editor, comment out the lines up until the new line, and uncomment out the function line. Close the Advanced editor to see that the query has been converted to a function.

In this case, two steps were added, one to drill into a table in the Excel workbook names "Table1" and another to convert one of the column to a percentage data type. The Advanced Editor now looks like this (function mode).

Step 5 – In the original query that was duplicated, on the Add Column tab, click on "Invoke Custom Function", choose the new function and pick the [Content] column as the source to be passed into the filecontent parameter. Hit OK and then expand the new column with all the "Table"s shown below (Remove the original [Content] column either before or after this step).

Step 6 – As needed, re-open the Advanced Editor to comment out the function step and uncomment the original lines to make further data transformations. When done, convert it back to a function.

I use this approach now whenever I need a custom function, and hope it helps you out too. Please add comments below with any question/feedback.

Here is a link to the pbix file used in this article and the video.