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.

18 responses

  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!

  4. Pingback: Using The Invoke Custom Function Button In Power BI – Chris Webb's BI Blog

  5. Pingback: Creating A Partitioned Table In SSAS Tabular 2017 And SSDT Using M Functions – Chris Webb's BI Blog

  6. Did you find a way to pass a whole TABLE as a parameter?

    M custom functions exist which have been hand programmed to take a table as a parameter but I’d like to do it using the UI – for debugging purposes. The issue that I see is that there’s no obvious way to create a standalone Parameter of type table – and thus I can’t then replace any given table in my query with my parameter name – and thus can’t Create function from it.

    Any thoughts?

  7. Pingback: Making Sure All Columns Appear When You Combine Data From Multiple Files In Power BI/Power Query M – Chris Webb's BI Blog

  8. Hi Chris,
    thanks for the great tutorial, which solved a part of my problem. Hopefully you can provide me with an idea on how to accomplish the rest. I have a SharePoint list with approx. 1000 projects, let´s call it Overview List. Each project has a subsite with several lists holding e.g. Financial Details. The URL to the subsites is listed in one column of the Overview list. The following describes roughly the structure of the objects.

    OVERVIEW LIST

    Project URL to subsite
    Project1 http://webapp/site/project1?ID=1
    Project2 http://webapp/site/project2?ID=2
    Project3 http://webapp/site/project3?ID=3

    This is one point where I am really struggling – how to get the ID and list it in the Financial results, to indicate which figures are belonging to which project.

    PROJECT1 FINANCIAL LIST ON PROJECT1 SUBSITE

    Planned Budget Actual Budget Planned Effort Actual Effort
    10000 8000 200 180

    PROJECT2 FINANCIAL LIST ON PROJECT2 SUBSITE

    Planned Budget Actual Budget Planned Effort Actual Effort
    20000 15000 1000 900

    etc.

    Now I want to step through this Overview list, follow the Link to the each project´s subsite, read the content of the Financial List of each project and compile a table with the Financial details of each project and the corresponding Project ID.

    Would be great if you can share your ideas with me, to get this finally done.

    Thanks in advance and best regards
    Frank

    • Hi Frank, you need to create a query that works on one subsite, use a parameter to hold the url for the subsite, and then create a function from that query. You should then call the function for every url in your list. But this is more or less what’s in the blog post here, so is there something else you’re struggling with?

      • Hi Chris, thanks for the feedback. By now I did not manage to solve it, but I am not giving up – thanks to your support 🙂
        Have a great day and
        Best regards
        Frank

  9. Pingback: Combining Data From Multiple Worksheets In The Same Excel Workbook Using Power BI « Chris Webb's BI Blog

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: