Creating M Functions From Parameterised Queries In Power BI

Query parameters are, in my opinion, one of the most important features added to Power BI recently. The official blog post describing how to use them is great (read it if you haven’t done so already) but it misses out one other cool new feature that I only discovered by accident the other day: it’s now super-easy to create M functions from parameterised queries.

Why is this important? In Power BI (and indeed in Power Query), M functions are the key to combining data from multiple data sources that have the same structure. For example, if you have a folder of Excel workbooks and you want to read the data from Sheet1 in each of them to create a single table for loading into Power BI, functions are the key. Here are some blog posts with examples:

Matt Masson on iterating over multiple web pages:
http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

Ken Puls on combining data from multiple Excel workbooks:
http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

My M function showing a generic function for combining any data from Excel:
https://blog.crossjoin.co.uk/2014/11/20/combining-data-from-multiple-excel-workbooks-with-power-querythe-easy-way/

All of these examples involve writing M code manually. The big change in the latest version of Power BI Desktop is that you can do the same thing using just the UI.

Let’s take the classic example of combining data from multiple Excel workbooks and update it to show how things work now.

Say you have a folder containing three Excel workbooks containing sales data for January, February and March and you want to load data from all three into a single table into Power BI. The first thing to do is to create a new parameter in Power BI Desktop that returns the filename, including path, of one of the Excel files. Call it ExcelFilePath and configure it as shown here:

image

Next, you need to create a query that connects to the Excel file whose filename is used in the parameter and load the data you want from it. In this case let’s say you want to load the data from Sheet1:

image

This is all very straightforward; here’s the query that you’ll get:

image 

Unfortunately, at the time of writing, the Excel source doesn’t support using parameters when creating a new query, so you have to create a query and then edit it to use the filename that the parameter returns. You can do this by clicking on the gear icon next to the Source step in the query:

image

In the dialog that appears, click the icon next to the File Path property and choose Parameter, then from the dropdown menu choose the name of the parameter you created earlier:

image

Now here comes to the good bit. In the Queries pane on the left-hand side of the screen, right-click on the name of the query you just created and select Create Function:

image

This option allows you to take any parameterised query and create a function from it. When you do this, you’ll see a dialog asking for the name of the new function to be created (I’ve called my function GetSheet1) and allowing you to change the name of the parameters:

image

Here’s the original M code for the query with the parameterised Source step highlighted:

let
    Source = Excel.Workbook(File.Contents(ExcelFilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
    {{"Product", type text}, {"Month", type text}, 
    {"Units", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"

Here’s the M code for the new query created after Create Function has been selected:

let
    Source = (ExcelFilePath as text) => let
    Source = Excel.Workbook(File.Contents(ExcelFilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
    {{"Product", type text}, {"Month", type text}, 
    {"Units", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"
in
    Source

Where the original query points to the workbook whose path is returned by the ExcelFilePath query parameter, the new function takes a parameter (also called ExcelFilePath) to which any other query can pass any text value.

Now you have your function, the final step is to call the function on a table containing the names of all of the Excel files in your folder. Create a new query using the From Folder source:

image

…then point Power Query to the folder containing the names of all of the Excel files:

image

Remove all the columns in the table except Folder Path and Name, drag the Folder Path column before the Name column, then select both remaining columns, right-click and select Merge Columns to create a single column (called FullPath here) containing the full path of all the Excel files:

image

image

Next you need to click the Custom Column button and call the GetSheet1 function for the text in the FullPath column for each row in the table. Here’s the expression to use:

GetSheet1([FullPath])

image

Last of all, click on the Expand icon in the right-hand corner of the new column:

image

…and you have a table that contains all of the data from Sheet1 on all of the files in the folder:

image

Now for the bad news: queries that use functions like this can’t be refreshed after they have been published to PowerBI.com (see also this thread for more details). This could be why the functionality wasn’t publicised in the post on the Power BI blog. Hopefully this will change soon though…?

So, to sum up, it’s the early stages of an important and powerful new piece of functionality. In the past, a lot of the times when I found myself writing M code it was to create parameterised queries and functions; in the future I’m going to be writing a lot less M code, which is great news. I can’t wait to see how this develops over the next few months and I hope it turns up in Power Query too.

8 thoughts on “Creating M Functions From Parameterised Queries In Power BI

  1. Hi Chris,

    I might be missing something here. Why not import the file list from the folder option? Then use the Excel.Workbook() function against the binaries (e.g., Excel.Workbook([Content])).

    Thanks as always for your great contribution to to Excel Science

    Dave

    • Well, I could have done but this blog post was more about the new way to create functions than the specific problem of importing from multiple Excel workbooks.

  2. Please help. I created a query to import Excel files from a older and it works great. but when (Excel 2010) I import the query to Power Pivot from the “Existing Connections” tab, only the Date fields come through. None of the text fields. I deleted the Power Pivot table and re-accessed the Table Import Wizard. The Validate and Design tabs show everything I want to import. All columns are there. but when I “Finish” all that appear are the date fields. I tried again but replaced the * in the SQL statement with a full list of all the fields :[Incident Number],[Submitted Date],[Modified by],[Old Queue],[New Queue],[Assigned To],[Transferred Date],[Submit Date],[Modify Date] and ran the Verify. The full SQL statement was valid, but still all I got was the four date fields. How can I fix this?

    • So you’re trying to use the output of the query as a data source in Excel 2010, and not loading the data into a table on the worksheet first? If so, that’s not supported in Excel 2010 (although there are various blog posts showing how it has worked in the past – maybe it’s broken now?) and you can only officially do that in Excel 2013/6.

  3. That’s Great news! :< Maybe that's why I occasionally get Corrupted Data Errors. ya-think? I did find a solution that appears to work though, I found that the text columns had the ABC-123 declaration. When I specifically declared them as 'Text", "Whole Number", etc, they all showed up in the report. Just to be safe, I will load the data into the worksheet as well as the data model. Thanks for the rapid response!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s