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:
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:
This is all very straightforward; here’s the query that you’ll get:
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:
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:
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:
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:
Here’s the original M code for the query with the parameterised Source step highlighted:
[sourcecode language=”text” padlinenumbers=”true” highlight=”2″]
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"
[/sourcecode]
Here’s the M code for the new query created after Create Function has been selected:
[sourcecode language=”text” highlight=”2,3″]
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
[/sourcecode]
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:
…then point Power Query to the folder containing the names of all of the Excel files:
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:
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:
[sourcecode language=”text”]
GetSheet1([FullPath])
[/sourcecode]
Last of all, click on the Expand icon in the right-hand corner of the new column:
…and you have a table that contains all of the data from Sheet1 on all of the files in the folder:
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.
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.
Understood and thanks again!
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.
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!
Interesting – those ABC-123 columns have the data type “Any” which I know Power Pivot doesn’t like.
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?
You’re right, there is no way of creating a parameter that returns a table. Of course you can create functions that takes a table as a parameter fairly easily by writing M code but I agree it would be nice if we could do this just using the UI.
And yet there does appear to be something very close to it in New Parameter.
Type -> Any
Suggested values -> Query
Although how you fill in the rest of the fields isn’t obvious.
Suggested values is something different – see https://blog.crossjoin.co.uk/2016/08/30/data-driven-power-bi-desktop-parameters-using-list-queries/
But yes, thinking about it you could use the Any type to pass a table as a parameter. Hmm, I’ll do a bit more exploration here perhaps…
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
Hi Chris
Great blog, always very insightful and useful!
Power Query function and Excel, generating different MDX:
I have a strange problem with a parameterised MDX query. It uses an Excel table (called Parameters) to hold the Cube name I want some metadata on. The problem is if I use a literal I get the measure list etc. But if I use the parameter from a function then I get a totally different MDX query sent to OLAP (seen through profiler).
Any ideas what could be going on here or what I’ve done wrong in M?
Many Thanks
Jonathan
m code
Using Literal
let
CubeName = fnGetParameter(“Cube”),
MDXQuery=”SELECT [CATALOG_NAME] as [DATABASE], CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE], [MEASURE_IS_VISIBLE] FROM $SYSTEM.MDSCHEMA_MEASURES where CUBE_NAME = ‘theCube'”,
Query1 = AnalysisServices.Database(“biolap”, “daily models”, [Query=MDXQuery, Implementation=”2.0″])
in
Query1
Using parameter
let
CubeName = fnGetParameter(“Cube”),
MDXQuery=”SELECT [CATALOG_NAME] as [DATABASE], CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE], [MEASURE_IS_VISIBLE] FROM $SYSTEM.MDSCHEMA_MEASURES where CUBE_NAME = ‘” & CubeName &”‘”,
Query1 = AnalysisServices.Database(“olap”, “cube db”, [Query=MDXQuery, Implementation=”2.0″])
in
Query1
MDX sent to OLAP for each query
Query 1 – literal cube name
SELECT [CATALOG_NAME] as [DATABASE], CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE], [MEASURE_IS_VISIBLE] FROM $SYSTEM.MDSCHEMA_MEASURES where CUBE_NAME = ‘thecube’
Query 2 – using CubeName parameter
select [CUBE_NAME], [BASE_CUBE_NAME], [CUBE_CAPTION] from $system.mdschema_cubes where [CUBE_SOURCE] = 1