Pass an Excel Parameter to Power Query - Excelerator BI

Pass an Excel Parameter to Power Query

Where has Matt been?

Power BI book cover 150Regular readers of my blog may have noticed I have been missing in action over the last month.  I have been super busy with the PUG World Tour, delivering a lot of training, finalising my new book Supercharge Power BI, and also taking a couple of weeks vacation.

Side Story: Power BI Training Has Never Been More Popular

I have been delivering Power Pivot/Power BI training for almost 4 years now and the demand has never been greater.  My last 5 live training courses in Australia have all had record numbers of participants, and my current semester of live online training (Supercharge Power BI) has 34 active participants.  I’m super excited what the future holds for this fantastic product, and it just keeps getting better and better.

Pass an Excel Parameter to Power Query

I was delivering training yesterday in Sydney and one of my students (Stephane) asked me how to pass a parameter from Excel to Power Query.  I have written previous articles about this principle at PowerPivotPro.com but thought it would be good to blog about how simple it is to do this.

Sample Scenario – Extract Invoices for Selected Date

I am going to demonstrate the concept using the Adventure Works database.  In the example below, I have set up a single cell in Excel that contains a date which is then passed to Power Query.  When I refresh the query in Excel, the results table is updated with all the orders from the database for the selected date.  Note:  There are other ways to do this, such as loading all records into Power Pivot and extracting them from there on demand.  However there may be people that want to do it this way for what ever reason.

Set up a Parameter Table

The first thing to do is set up a single cell table that contains the date of the orders to extract.  Then click in the cell (1 below), select data (2), from Table/Range (3).  Note this is Excel 2016; the UI is slightly different for Excel 2010/2013.

image

The next step is super simple and is the secret sauce to this process.  As you probably realise, the data loaded is a table with a single column and a single row (see below).  The table therefore has a single cell that contains a single date.  It is not possible to use the date in this tabular format, so it needs to be changed before the date can be used in another query.  To do this, simply right click on the cell (1 below) and then select drill down (2).

image

After completing this step, the table is removed and the actual value is returned by this query (see below).

image

Now that this is a value (the scalar value shown in 1 above), this query name (2 above) can be directly used inside another query.

Extract the Required Data for a Random Date

The next thing is to write a query to extract the required data from the database.  First set up the query as required, then apply a filter from the drop down box (1 below) for any random date (2 below).

image

This creates a query step as shown below – the query step directly references the date selected above.  Note: the formula bar needs to be turned on to see this (View\Formula Bar).

image

It is now very simple.  Just manually edit the hard coded date in the code above with the name of the query that returns the date coming from Excel.

image

Now close and load.  Set the DateSelected query so it doesn’t load and the Sales query so it loads to the original spreadsheet below the date parameter table.

image

To refresh and extract a different date, simply change the date in the parameter table (1 below), then right click in the results table (2) and then click refresh (3).

image

The query then runs and the new data is returned to Excel.

image

A Final Word

This is the most simple parameter table you can create – a single value.  Once you understand the principle, you can let your imagination run wild with other things you can do here.  If you want to fast track your learning so you can leverage all the power of Power Query, take a look at my self paced online Power Query training course.  This training has over 7 hours of video explaining concepts like this.  Once you know how, you will be able to save time and do fantastic things you never dreamed possible.

32 thoughts on “Pass an Excel Parameter to Power Query”

  1. Just wanted to say a huge thank you for this, after spending half of the day looking at people advising to invoke functions and copy this code this one post was not only very easy to understand (I’m teaching myself and have only been at it a day) but did exactly what I needed it to do.

  2. Hi Matt.

    Thank you for this and got it to work on the one query that only required the date filter. I have another query that also has a from and to date but are used within the query in more ways than just a filter and need to add “Parameters”. The issue I am having is that the date format is the American format 3/31/2018 M/D/YYYY and in SQL the date format I have is 2018-03-31. I tried to use the convert(varchar,@[email protected],23) but still did not work.

    Maybe a way to take your Date table and define it as a Parameter?

  3. I’ve followed all the steps here. But when I click Close & Load, I get an error
    “This won’t work because it would move cells in a table on your worksheet”

    thoughts?

    1. I’m sorry, I figured that out…but what I haven’t figured out is this
      “Set the DateSelected query so it doesn’t load”
      I cannot find the setting which tells a query NOT to load to an excel sheet…

  4. Hello, thanks for your post. I’m trying to come up with a statement when I need two or more records as criteria. If one record we use {0} in a statement. What if I have two rows I need to use for criteria, i.e. both {0} and {1}. Or bigger range. Thank you so much

    1. Sort the list in the order you want, then use “keep top rows”. You can use this technique for the first row too of course, but you miss out on learning about the M language if you do that.

  5. Thanks a lot for the tutorial!

    I would like to filter on the date only if there is a value in the cell, otherwise I’d like not to apply the filter.
    How would you do in your example?

    Thanks in advance.

  6. Matt, Thanks for the tip. I am using this to filter by processor. The processor picks their name from a drop down box, which is the table that is passed to the power query. I works great. However i would also like to have the full results (see all processors). When i use a blank or All, its reading it as text, and nothing comes back. Is there something that would pull all records? The formula from power query is : Table.SelectRows(#”Added Custom11″, each ([Processor] = RASelect)), with RASelect as the input.
    Thanks,
    Brett

    1. I’m sure it can be done, but it depends on the query and how it works. I guess it would involve an if statement. If your parameter currently filters a list, then the if should not complete any filter if blank. If the parameter is needed to contain a list of values of the items, you would need to build that list into the query and switch to it in the event the parameter is blank.

      1. I was thinking the same thing. I tried: if RASelect
        = null then Table.SelectRows(#”Added Custom11″, each ([Consultant] = RASelect)) else null. I would get an error if RASelect was empty. Is there a function that instructs it to do nothing if null?

        1. To me, the if statement looks fine. There is also a try/otherwise statement that can be used to trap an error. You could look into that.

  7. Excellent tip Matt, let me share my experience trying to use this tip but put the excel cell value as a parameter inside an SQL query.
    Hopefully it helps people, since didn’t find so much about this specific usage of PQ parameters.
    If excel cell value is a text like Sparepart no problem, the following works:
    = Sql.Database(“databaseserver”, “dbname”, [Query=”SELECT * from mytable where Description = “& Dateselected &” “])
    If the excel cell value is a date like 20/10/2018, the following doesn’t work:
    = Sql.Database(“databaseserver”, “dbname”, [Query=”SELECT * from mytable where Date >= “& Dateselected &” “])
    Since SQL expects dates as ’20/10/2018′ I need to convert 20/10/2018 to this format (it took me ages to think about..PQ error messages are crazy….;-)
    I didn’t find any direct command in PQ to add the ‘ at the beginning and end of the date, so finallly did the following:
    – Created a second column in PQ based on the first one with 20/10/2018, but with an insert at first char, so now have ’20/10/2018
    each (Text.Insert(Text.From([firstcolumn]),0,”‘”)
    – Created a third column in PQ based on the second one with ’20/10/2018, but with an insert at eleventh char, so now have ’20/10/2018’
    each Text.Insert(Text.From([secondcolumn]),11,”‘”)
    Once I got the final format, removed the first and the second colum and Drill down and now the SQL query works !!
    Don’t know if there is an easier way to convert the date format, I’m PQ beginner, you know?
    Thanks

  8. Error:
    DataSource.Error: Microsoft SQL: Invalid column name ‘Startdate’.
    Details:
    DataSourceKind=SQL
    DataSourcePath=gje-dwht-sql01.gje.osl.basefarm.net\testsql;edw
    Message=Invalid column name 'Startdate'.
    Number=207
    Class=16

    SQL:

    declare @P1 as int, @P2 as int, @P0 as int
    set @P1 = 201801
    set @P2 = [Startdate]
    set @P0 = 201801

  9. Hi,
    I want to pass comma separated values as input to parameter table in Power Query and generate the report. How to do that?

  10. Hi all.
    Can we do the same thing but with an vba code?
    For instance, I want to select a file and pass that file path to the parameter “File”

    Thx

  11. While I didn’t get a solution from this page that would work for me, the hint from Ivan led me in the right direction. With an Advanced Query (after building a mock up) I came up with the following Power Query language code to query my view X_AllTransactionLines based on a date in the date range:

    let
    dtmStart = Excel.CurrentWorkbook(){[Name=”rngStartDate”]}[Content]{0}[Column1],
    dtmEnd = Excel.CurrentWorkbook(){[Name=”rngEndDate”]}[Content]{0}[Column1],
    Source = Sql.Database(“lca07”, “exo_lca_live”, [Query=”select * from X_AllTransactionLines”]),
    #”Filtered Rows” = Table.SelectRows(Source, each [TransDate] >= dtmStart and [TransDate] <= dtmEnd)
    in
    #"Filtered Rows"

    I don't mind getting out of a windows UI and into "code", and with it being this easy and understandable I thought I'd share

  12. I’ve used this trick before. However one problem I have (for Excel 2010) is with large tables. The refresh still loads everything and only then filters. It doesn’t let my database do the filtering (which would be faster).

  13. Matt,

    When I selected ‘Drill Down’ to convert the ‘Date’ field (and remove the table),
    1) Power Query returned formula “= #”Changed Type”[Date]”, not “#”Changed Type”{0}[Date]” as shown; missing the {0} value
    2) the parameter did not work when retrieving data
    3) After adding the {0} to the formula everything worked as you described

    Does changing the date value from a table to a scalar require any special steps to include the {0} in the new formula?

    Thank you.

    1. The {0} is a row indicator (zero based). So {0} means the first row, {1} means the second row etc. If you right click on a cell, then drill down, I expect that the {0} will be added (assuming it is the first row). I can’t be sure why you are getting a different experience. Any chance of emailing me a video of what is happening?

  14. I have a function which is part of my standard Library called fGetCellVal
    You can use it in Excel to read a named range or in Power BI /Excel to read from a Table
    let
    fGetCellVal=(tbl as any, optional row as number, optional col as text)=>

    // fGetCellVal
    let
    fGetCellVal=(tbl as any, optional row as number, optional col as text)=>

    let
    Source = try Excel.CurrentWorkbook(){[Name=tbl]}[Content] otherwise tbl,
    rNo = if row = null then 0 else row,
    col = if col = null then Record.FieldNames(Source{0}){0} else col,

    Value = Record.Field(Source{rNo},col)
    in
    Value
    in
    fGetCellVal

    Sample usage in Excel : fGetCellVal(“fPath”)
    Returns the contents of a Cell named fPath
    Sample Usage in Power BI – containing a Table called P having 2 Fields Parameter and Value and having Text “File Path” in the First row under column Parameter and “C:\Data” in column Value
    fGetCellVal(P,0,”Value”)
    Returns C:\DATA

  15. Thanks Matt, it is a very useful technique. In some cases, we don’t even need tables, enough to have NamedRange, let’s say CELLNAME. Then use “shortcut”
    = Excel.CurrentWorkbook(){[Name=”CELLNAME”]}[Content]{0}[Column1]
    in Power Query to get content of range with name CELLNAME. I use it very often, prefer this method to Parameters, because it is easier to change value in cell than in PQ parameter. Moreover, value in cell can be result of a formula, this adds flexibility.

    1. Ivan, I really like that idea. I guess it is a bit more manual coding so not as accessible to beginners or intermediate people learning the tool, but certainly a great approach that streamlines the source location so it doesn’t have to be a table.

    2. Thanks for this! I found using the normal method of creating a parameter didn’t work because I was getting the error “Query ‘Billing With Descriptions’ (step ‘Grouped Rows’) references other queries or steps, so it may not directly access a data source” which is caused when you try to mix an external query with a local query. (here’s more info about that: https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-not/td-p/18619)

      I worked around this by adding this line in the Advanced editor to directly get the parameter:
      FY=Number.ToText(Excel.CurrentWorkbook(){[Name=”NamedCell”]}[Content]{0}[Column1]),

      Also note: Use Number.ToText to use the number in the SQL string.

      Hope this helps someone!

  16. Great technique, Matt! I’ve been doing start date and end date in a table, unpivoting them, and then getting the List.Min() and List.Max() as parameters for my date range query.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top