Building a Parameter Table for Power Query

One of the things that I’ve complained about in the past is that there is no built-in way for Power Query to be able to pull up the path for the current workbook.  Today I’m going to show you how I solved that problem by building a parameter table for Power Query in Excel, then link it into my queries.

Quick Note!  WordPress is determined to drive me crazy and is replacing all of my straight quotes (SHIFT + ') with curly quotes.  Since curly quotes are different characters, they will cause issues if you copy/paste the code directly. Please make sure you replace all "curly quote" characters with straight quotes.

To do this successfully, we need two pieces: an Excel Table, and a Power Query function.  So let’s get to it.

Building a Parameter Table

The table is quite simple, really.  It’s a proper Excel Table, and it has a header row with the following two columns:

  • Parameter
  • Value

Once created, I also make sure that I go to the Table Tools tab, and rename the table to “Parameters”.


Pretty bare bones, but as you’ll see, this becomes very useful.

Now we add something to the table that we might need.  Since I’ve mentioned it, let’s work out the file path:

  • A8:     File Path
  • B8:     =LEFT(CELL("filename",B6),FIND("[",CELL("filename",B6),1)-1)

Now, as you can see, column A essentially gives us a “friendly name” for our parameter, where the value ends up in the second column:


While we’re here, let’s add another parameter that we might have use for.  Maybe I want to base my power query reports off the data for the current day.  Let’s inject that as well:

  • A9:     Start Date
  • B9:     =TODAY()


Good stuff.  We’ve now got a table with a couple of useful parameters that we might want when we’re building a query.

Adding the Parameter Function

Next, we’re going to add the function that we can reference later.  To do that:

  • Go to Power Query –> From Other Sources –> Blank Query
  • Go to View –> Advanced Editor
  • Replace all the code with the following:

(ParameterName as text) =>
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")

  • Click Done
  • Rename the function to “fnGetParameter”
  • Go to Home –> Close & Load To…
  • Choose to create the connection only, avoiding loading to a table or the Data Model

And just in case you think this means it didn’t work, we expect to see that it didn’t load in the queries window:


Making Use of the Parameter Table

Okay, so now that we have this in place, how do we go about using it?

Let’s create a ridiculously simple table:


Now, click in the table and go to Power Query –> From Table.

We’ll be taken into the Power Query window and will be looking at our very simple data.  Let’s pull the contents of my parameters into columns:

  • Go to Add Column –> Add Custom Column
  • Change the title to “File Path”
  • Enter the following formula: =fnGetParameter("File Path")

Check it out!


Do you see that?  This is the path to the folder that holds the workbook on my system.  The formula we used in the table retrieves that, and I can pass it in to Power Query, then reference it as needed!

How about we do the same for our date?

  • Go to Add Column –> Add Custom Column
  • Change the title to “First date”
  • Enter the following formula: =fnGetParameter("Start Date")


The key here is to make sure that the value passed to the parameter function is spelled (and cased) the same as the entry in the first column of the parameter table.  This means you could use “FilePath”, “File Path”, “Folder”, “My File Path” or whatever, so long as that’s the name you gave it in the first column of the Parameters Excel Table.

And what happens if you pass an invalid value?  Say you ask for fnGetParameter("Moldy Cheese") and it’s not in the table?  Simple, you’ll get null returned instead.  🙂

Implications of Building a Parameter Table for Power Query

The implications for this technique are huge.  Consider this scenario… you create your workbook, and store it in a folder.  But within that folder you have a subfolder called “Data”.  Your intention is to store all of your csv files in that folder.  And, for argument’s sake, let’s say that it’s a mapped drive, with the path to your solution being “H:\My Solution\”

No problem, you build it all up, and it’s working great.  You keep dropping your text files in the data folder, and you can consolidate them with some M code like this:

Source = Folder.Files("H:\My Solution\Data"),
#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(#"Imported CSV")
#"First Row as Header"

Things run along for ages, and that’s awesome, but then you need to go on vacation.  No worries, it’s Power Query and easy to use, you can just get your co-worker to update it… except… on your co-worker’s machine that same drive is mapped not to the H:\ drive, but the F:\ drive.  D'oh!

We could recode the path, but what a pain.  So how about we use the parameter table to make this more robust so that we don’t have to?  All we need to do is modify the first couple of lines of the query.  We’ll pull in a variable to retrieve the file path from our parameter table, then stuff that into the file path, like this:

SolutionPath = fnGetParameter("File Path"),
    Source = Folder.Files(SolutionPath & "Data"),
#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(#"Imported CSV")
#"First Row as Header"

How awesome is that?  Even better, the SolutionPath shows as a step in the Applied Steps section.  That means you can select it and make sure the value is showing up as you’d expect!

Practical Use Case

Several months back I built a solution for a client where we stored his Power Query solution in a folder, and we had data folders that were created on a bi-weekly basis.  Each of those folders were named based on the pay period end data (in ISO’s yyyy-mm-dd format), and were stored in a path relative to the master solution.

Naturally, I needed a way to make the queries dynamic to the master folder path, as I did the development in my home office, then emailed the updated solution files to him in his New York office.  He had different drive mappings than I do, and his team had different drive mappings than he did.  With half a dozen Power Queries in the solution, having them manually update the code each time a new user wanted to work with the file just wasn’t an option.

This technique became invaluable for making the solution portable.  In addition, by having a formula to generate the correct end date, we could also pull the data files from the right place as well.

I still want a Power Query CurrentWorkbook.FilePath method in M, but even if I get it this technique is still super useful, as there will always be some dynamic parameter I need to send up to Power Query.

I hope you find this as useful as I have.



182 thoughts on “Building a Parameter Table for Power Query

  1. This is fantastic! Figuring this out how to do this in PQ had been on my to do list for a while. Thanks!!!

    FYI, copying and pasting fnGetParameter did not play nice because Excel did not always see the quotes as quotes.

  2. Hi Jimmy,

    Glad this helped. Yes, sorry on the quotes. It's a known irritation with WordPress. Still trying to figure out how to change this...

  3. Looking for some Power Query help.

    Is there as way to easily replace value based on the value in another field. For example:
    Say I have 2 Columns in my power query results, Col1 and Col2
    Col1 has values A, B, C & D
    Col2 has values 1, 2, 3 & 3

    I want to replace value in Col2 to 4 whenever value in Col1 is D.


  4. Hi Amit,

    The easiest way - if it's only the one value you want to replace - is to add a new column and provide a formula like this:
    =if [Col1] = "D" then 4 else [Col2]

    The remove Col2

  5. Anand,

    Are you asking if you can dynamically change the SQL that is manifested in the M code before it's executed against the SQL database? We can absolutely do that. Can you share a bit more on what you're trying to accomplish?

  6. Thanks for your great tips!
    I am using this function method to replace the following code
    Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
    Your code is more portable and I only need to setup once and able to use it for all my PQ !!!!

  7. Pingback: Power Query Errors: Please Rebuild This Data CombinationThe Ken Puls (Excelguru) Blog

  8. Pingback: Bob The (Proto) Builder | Bob's BI Ramblings

  9. Hello Ken!,

    I believe it's a fantastic piece of work. Unfortunately, when I try to copy-paste the section:
    (ParameterName as text) =>
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")

    I receive an error saying to invoke the ParameterName function.

    Quite new with Power Query, so I am not sure what I screwed up 🙂

  10. Are you getting the error when you try to call the fnGetParameter function from another query then? Did you call it like so: =fnGetParameter("name from first column of table")

  11. Hey Ken,

    I appreciate all the effort you put in writing this.

    I had a question. The value I grab from my parameter table, I store it in a variable and use it in my SQL query. However, every time I change the parameter, I get the following prompt:

    You are about to run a native database query. Native queries can make changes to the database. Would you like to run the following query?
    Is there any way I can make this go away?


  12. Hi Ken. I'm using this technique - thanks, it's a really good way to adress this. As part of my learning, I tried a case where I use this technique to set the value in an added column. The column is added to a worksheet table loaded to PQ using the [From Table] function.
    This works fine when the new column is added directly to the From Table query. The problem is that if I use [Reference] to the From Table query and add the parameter to a new column, I get the formula firewall rebuild error message.
    I find this confusing because I have been trying to follow the method that Chris Webb recommends to land the data in PQ first, and then use references to feed the data into subsequent transforms.
    Any idea where I have gone wrong?

  13. On occasion you need to load the parameters into variables into the beginning of the query, then use those variables throughout your code. I've also seen where that still isn't enough and you need to pull the parameters into the base (staging) query. Would have to see your workbook to know for sure which is hitting you. If you'd like me to take a look, shoot it to me at ken at this site dot ca.

  14. I am new to all of this, but I am trying to implement this into a custom SQL statement. I have the basics set up, but when I try to use the variable MYEDP in the SQL statement, I get ORA-00904:invalid identifier. Here is the code:
    MYEDP = fnGetParameter("EDP"),
    Source = Oracle.Database("", [Query="SELECT A.SALES_ORDER_NR AS ""Order #"",#(lf)B.OFFER as ""Offer"",#(lf)A.SALES_ORDER_ITEM_QTY as ""Qty Sold"", #(lf)A.TOTAL_SALES_ORDER_ITEM_PRICE as ""Total Price"",#(lf)B.SALES_ORDER_DEMAND_DATE as ""Demand Date"",#(lf)C.ITEM_LONG_NAME as ""Item-Description"",#(lf)D.PERSON_FAMILY_NAME AS ""Last Name"",#(lf)D.PERSON_GIVEN_NAME AS ""First Name"",#(lf)D.ADDRESS_STREET_NAME AS ""Street"",#(lf)D.ADDRESS_CITY_NAME AS ""City"",#(lf)D.ADDRESS_STATE_NAME AS ""State"",#(lf)D.ADDRESS_POSTAL_CODE AS ""Zip"",#(lf)A.ORDER_ITEM_STATUS as ""Status"",#(lf)D.CUSTOMER as ""Customer"",#(lf)D.CUSTOMER_EDP_ID as ""Customer EDP"",#(lf)E.EMAIL_ADDRESS_UPPERCASE as ""E-Mail""#(lf)FROM RSIDBA.CV_SALES_ORDER_ITEM A#(lf)left outer join RSIDBA.CV_SALES_ORDER B on B.SALES_ORDER_NR = A.SALES_ORDER_NR and B.DELETION_INDICATOR = 'N'#(lf)left outer join RSIDBA.CV_ITEM C on C.ITEM_EDP_ID = A.ITEM_EDP_ID and C.DELETION_INDICATOR = 'N'#(lf)left outer join RSIDBA.CV_CUSTOMER D on D.CUSTOMER_EDP_ID = B.CUSTOMER_EDP_ID and D.DELETION_INDICATOR = 'N'#(lf)left outer join RSIDBA.CV_CUSTOMER_EMAIL_UPPERCASE E on E.CUSTOMER_EDP_ID = B.CUSTOMER_EDP_ID and E.DELETION_INDICATOR = 'N'#(lf)inner join#(lf) (SELECT #(lf) A.SALES_ORDER_NR#(lf) FROM RSIDBA.CV_SALES_ORDER_ITEM A#(lf) left outer join RSIDBA.CV_SALES_ORDER B on B.SALES_ORDER_NR = A.SALES_ORDER_NR#(lf) WHERE A.DELETION_INDICATOR = 'N'#(lf) and B.DELETION_INDICATOR = 'N'#(lf) and A.ORDER_ITEM_STATUS NOT IN ('A','D','H','U','U1')#(lf) --and B.SALES_ORDER_DEMAND_DATE between TO_CHAR(SYSDATE-14, 'yyyy/mm/dd') and TO_CHAR(SYSDATE, 'yyyy/mm/dd')#(lf) and A.ITEM_EDP_ID = (MYEDP) group by A.SALES_ORDER_NR) W1 on W1.SALES_ORDER_NR = A.SALES_ORDER_NR#(lf)WHERE A.DELETION_INDICATOR = 'N'#(lf)--and B.SALES_ORDER_DEMAND_DATE between TO_CHAR(SYSDATE-14, 'yyyy/mm/dd') and TO_CHAR(SYSDATE, 'yyyy/mm/dd')#(lf)order by A.SALES_ORDER_NR, C.ITEM_LONG_NAME"])

  15. Hi Dave,

    Honestly, I don't think I can debug your SQL statement. A bit long for me. What I'd suggest though, is to avoid using a custom SQL statement at all. Use the UI to browse to the table(s) directly, then filter the records in Power Query. Power Query will then fold the steps and send a SQL statement to SQL server (this is called Query folding). As soon as you send the statement above to the server you break any further query folding capabilities.

  16. I didn't want to write the SQL either - but it won't fold on its own (maybe because it is oracle as opposed to mssql?). I have an invoice header record and multiple detail records. I want to get the records for a particular item. If I PQ the detail and filter for the item, I get 127 rows (out of 20,000,000) in 2 seconds. If add the header in PQ and then merge the 2 by invoice number, it tries to get 10,000,000 headers and then filter - so no folding is taking place. The sql doesn't matter - this is what fails:
    even though I prefaced the code with:
    MYEDP = fnGetParameter("EDP"),
    and in PQ, MYEDP is getting populated correctly.

  17. Hi Dave,

    By prefacing the code with a power query function, you break the ability to fold the query. Every command that you issue (connect, filter, sort, group) can be folded until the first line of code (used or not) in your query that doesn't have a native database structure.

    On why it isn't going into your query correctly, it looks like you're passing an ID. Is it a number in SQL or text? Can you convert the type on MYEDP?Text.From(MYEDP) or Number.From(MYEDP)

    Does that change things?

  18. The EDP is numeric, but that is not the issue. Here is the simplest possible query. Somehow the parameter MYEDP is not properly passed to the oracle query. The oracle error is that it does not know what MYEDP is at all:

    MYEDP = Number.From(fnGetParameter("EDP")),

  19. Oh, wait... you're passing the name of the variable. Try this:

    MYEDP = Number.From(fnGetParameter("EDP")),
    Source = Oracle.Database("", [Query="SELECT A.SALES_ORDER_NR,#(lf)A.SALES_ORDER_ITEM_QTY#(lf)FROM RSIDBA.CV_SALES_ORDER_ITEM A#(lf)WHERE A.ITEM_EDP_ID = (" & MYEDP & ")"]) in

    Notice that I wrapped MYEDP with " & MYEDP & "

  20. Hi Ken,

    Loved to find your post and use it in all my workbooks ever since.

    Don't know if you ran into the same issue or not:
    I have the following m query which works fine:
    Source = Oracle.Database("mySchema.server"),
    tmp = Source{[Schema="mySchema",Item="OrganizationView"]}[Data],
    #"Filtered Rows" = Table.SelectRows(tmp, each ([Period] = 20150331))
    #"Filtered Rows"

    As from the moment I try to apply a parameter with the function instead of hardcoded, I get an error message which states that there was an error while buffering the data for step Organization. It seems the query folding isn't working properly anymore or am I wrong?

    Source = Oracle.Database("server"),
    Organization = Source{[Schema="mySchema",Item="OrganizationView"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Organization, each ([Period] = (fnGetParameter("Period")))
    #"Filtered Rows"

  21. Hi Tom,

    Absolutely correct. This isn't quite perfectly correct, but in layman's terms query folding only works on commands that are issued through the Power Query UI. As soon as you introduce a line of manual code, the query folding breaks right there, and Power Query picks up with local processing.

  22. Hi Ken,

    Thanks for confirming what I was afraid for 🙂
    Although I can't see the logic behind the idea...
    Let's hope Microsoft changes that in a next release.

    Have a good weekend,

  23. Hi Tom,

    Just to clarify that a bit... it's not the fact that it's UI driven that makes things work or not. That's a bit of a simplification. If it's built via the UI though, there's a much greater chance that the Power Query engine will be able to translate the code into something that SQL understands.

    By contrast, when we go and add custom code as intermediary steps, the Power Query engine can't immediately translate it. Variables are one such items, and function calls another. That's what's killing it is that today the engine isn't robust enough to look a couple of steps down the line to fold the results in, then pass that back to the processing engine.

    As much as I'd love to see it just work, I think that it's a pretty complicated engineering issue to be able to try and parse the free form code we are writing back into first Power Query language, then back into SQL. I can see why they haven't got there yet.

  24. I am a complete novice to SQL and power query (DAX and VBA are more my bag), but I have been trying to do something kind of similar in regards to the sheet name. I have a file in which the name for Sheet1 is constantly changing (externally, can't be helped). Is there a way to set the query to pull first sheet regardless of its name? If not, the first 6 characters are always the same, can one use a wildcard for the remaining characters in the source item?

    I realize this may not directly apply, but this was the most engaging and helpful article I've read in using excel sources based upon user variables. Your assistance is much obliged!

  25. If the first 6 characters are always the same, I would probably filter the column to show only sheets that match it. I guess the question is though... are you getting this data from an external Excel file, or trying to trigger it from within the same workbook?

  26. You sir, are awesome and I could just buy you a scrumptious cake right now. Thank you so much for your assistance, as I can't even begin to explain how much easier this will make my life.

    I can't believe I had such a hard time with this, and it was just as simple as adding a begins with text filter to the Source step.

    I have just started studying SQL itself, since it's blatantly lacking from my skills. I will be following this blog to learn all the neat tricks. Looking forward to reading more of your work!

    Thanks again!

  27. Just out of curiosity, is it possible to select a sheet by the sheet index, rather than the sheet name? For instance, the prior issue is always the first sheet in the workbook, can you code it to pull for the Sheet Index 1? Granted I'm still thinking in VBA mentality...

  28. Not by the VBA Index, no. But I *think* the sheets are listed in the index number. So you could try adding an index column, then pulling up the first one:

    Source = Excel.Workbook(File.Contents("D:\DataFile.xlsx"), null, true),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index",each ([Index]=0))
    #"Filtered Rows"

  29. Hi Ken,
    Your article is great. Still I am struggling to find a solution to my challenge:
    I want to use parameters in my query.. "Filtering solution" is not an option since the table is very big.
    So for example, I have:
    select *
    from Test where id=999 and project= 111
    and I want to define the id and project numbers in a parameters table.
    Is it possible?

  30. Hi there,

    Even though the table is large, it would still be more efficient to pass the queries back to a database server. The built in query folding then processes it on the server (instead of your workstation), bringing less data over the network to you.

    If you can't, then using a getParameter function can work. The steps I'd use to implement this:
    -Build the parameter table (as described in other posts on this site)
    -Build a query that uses your custom SQL statement
    -Edit the M code and, right after the "let" line, add two new lines:
    -Edit the SQL query line in the M code to replace 999 with myid and 111 with myproject
    -Close the editor and test it
    -Edit the code and sub the fnGetParameter call in place of the 999 and 111 in the code

    Hope this helps.

  31. Pingback: Share and Refresh Power Query with Local Links - PowerPivotPro PowerPivotPro

  32. fnGetParameter("FileName") correctly returns C:\Test_10022015.xlsx

    How do I incorporate this function in the first line of the Advanced Editor (source) for a given query?

    First line in the Advanced Editor:
    Source = Excel.Workbook(File.Contents("C:\Test_10022015.xlsx"), null, true),


  33. Right after the let statement, adjust it to read as follows:
    filepath = fnGetParameter("FileName"),
    Source = Excel.Workbook(File.Contents(filepath), null, true),

    Hope that helps,

  34. Perfect. Not sure how it works but it works. Hopefully, this will be covered in detail in your M book. Thanks Ken.

  35. Try this:

    Source = ...[Query="select * from table1 limit "&LimitValue])

    You were treating LimitValue like text in that line. You need to close the text and append the variable instead.

    Hope this helps!

  36. Thank you Ken!

    I wrote:
    Source = MySQL.Database("...Query="select * from table1 limit "&LimitValue])

    And I get the error:

    Expression.Error: We cannot apply operator & to types Text and Number.
    Left=select * from table1 limit

    Please help 🙁

    Thanks again

  37. Hi Ken,
    Do you have any working example that the I can use.
    Best would be with Numbers and parameter from the query.
    Thanks again

  38. Hi Ken, I'm getting the same response as Pete posted on April 9. The query wants to Invoke. The load is disables never appears. Rather it says Connection only. Would love to get this working. Thanks . Clive

  39. Hi Clive,

    The "Load is Disabled" terminology is gone, and has been replaced with "Connection only". (This is a good thing, as the load was never truly disabled, it was just a connection.) I'm not quite following what you mean by "the query wants to invoke"?

  40. Hey Lefkir,

    I'm sorry, I think I messed that up and went the wrong way. In order to join two pieces of text together, they must both be text. So the correct approach would be this:


    Hope that fixes it up for you!

  41. Hello Ken,
    Just for fun, I'm trying to build a dashboard with a link to a data file on my home computer. I plan to email it to my son, so both of the files will have a different path.
    So where do I create the Parameters Table and where do I put the script. I'm guessing that if both files reside in the same folder, I'd put them in the Dashboard file. Fred

  42. Okay, so save the workbook in the same folder as your data file. Put the parameters script in that new workbook. Then, email both files to your son. As long as he saves them in a directory together as well, things should work.

  43. Thanks Ken,
    Been kept real busy by 'she who must be obeyed', finally getting back to this.
    So new parameters tab with table, in Dashboard file. Modify the existing script.
    Save in same folder as the Data file.
    Hmm, modified 4 rows....
    SolutionPath = fnGetParameter("File Path"),
    Source = Folder.Files(SolutionPath & "Dashboard"),
    #"DataFile" = Source("QC DAILY MONTHY REPORT 30092015.xlsm"[Content]),
    #"Imported Excel" = Excel.Workbook(#"DataFile"),

    now getting error....
    Expression.Error: We cannot convert a value of type Table to type Function.

    I'm so hopeless at this.

  44. Also tried...
    SolutionPath = fnGetParameter("File Path"),
    Source = Folder.Files(SolutionPath & "Dashboard" & "QC DAILY MONTHLY REPORT 30092015"),
    #"DataFile" = Excel.Workbook(Source[Content]),
    #"Imported Excel" = Excel.Workbook(#"DataFile"),

    but get ....
    Expression.Error: We cannot convert a value of type List to type Binary.

  45. Ah right, I'm trying to import 12 sheets of data not just one, so this is all wrong.
    Back to square one.

  46. Found it! I knew I'd find the right script to start me off in one of your blogs.
    All sorted.
    Hmm, guess I'll have to mow the lawns now, bugger!

  47. Pingback: Using Parameter Tables To Control Data Refresh In Power BI | Chris Webb's BI Blog

  48. Hi Ken

    I am wanting to pass an array of parameters into a native SQL statement using the WHERE IN method.

    Source = Odbc.Query("Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=JDE;";Initial Catalog=S109F05T", "SELECT * from f42119 where f42119.sddgl >114001 and f42119.sditm in (549790,685417,643129)"),

    Is it possible to do this?

  49. James,

    I'm going to say "quite likely", but it could depend on how the query is set up due to formula firewall restrictions. To that end, you may need to flip the setting in Query Options --> Privacy to "Ignore Privacy options"

    Regardless, what I would do is still try to set up the parameter query. In order to pull in those parameters to the query you want to use to build the SQL though, I would suggest you don't just use the fnGetParameter function inside the Source line to build the query. Instead, I'd set up a new variable at the beginning of the code like this:

    account1 = fnGetParameter("Account1"),
    account2 = fnGetParameter("Account2"),

    and then nest it in the Source call like this (untested and written in a browser)
    Source = Odbc.Query("Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=JDE;";Initial Catalog=S109F05T", "SELECT * from f42119 where f42119.sddgl >" & account1 & " and f42119.sditm in (" & account2 & ")"),

    Or you could build a formula in an Excel cell to return the entire Select statement and just use a single call.

  50. Hi Ken

    Thanks for the quick reply. I forgot to add that the values need to come from a table inside of excel.

    Source = Odbc.Query("Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=JDE;";Initial Catalog=S109F05T", "SELECT * from f42119 where f42119.sddgl >114001 and f42119.sditm in ("somehow reference the table contents here!")")

  51. So I have used your code to get a single parameter before. Works great. But in this case its an array not a single parameter.

  52. Yeah, that's no problem... if you can write the Excel formula to concatenate it, then you just do that in the parameter table and you're good to use the steps above.

    If you're trying to say... take all rows from a column of table, join them with commas, then import that, then that gets a bit trickier, especially if the number of rows can change...

  53. Hi Ken, I am trying to use this to pass a date into a table-valued function within an SQL query in Power Query and I cannot get it to work:
    SnapshotStart = fnGetParameter("Parameter"),
    Source = Sql.Database("\uksalescrm", "UKSalesDataWarehouse",
    [Query="select * from UKSalesDatawarehouse.[dbo].[fOpportunitySnapshotTable] (SnapshotStart)"])

    Any help would be hugelyappreciated

  54. Dear Ken,
    Thank Very Very much for your work. We really appreciate your great posts and books very much.

    The problem we are encountering is that while Power BI Desktop refresh data perfectly with parameteric Power Queries, Power BI online does not give the option to refresh, and it doesn’t detect the source of data.

    We have created a query with a SQL access defined by a parameter such as:
    servername = () =>
    Origen = ""

    Origen = Sql.Database(a, “ASFdataXXX”, [Query="select * FROM leads"])

    We guest it is related to the previous answer you gave to Tom De Cort in this post and the folding process.

    • Do you know if there is such problem of the Power Query Desktop + Power BI Online?
    • Do you know any workaround to get a parametric SQL or how to get a parametric SQL source just using UI?

    Thank you very beforehand much for your time and interest.
    Best Regards,

  55. Hey Alberto,

    My understanding is that, unfortunately, Power BI online doesn't accept parameterised queries. When Power BI refreshes the query, it scans the M code and takes everything at it's literal value, which means you can't pass dynamic parameters in this medium. 🙁

  56. Awesome book Ken! I have question:

    How do I create a function that uses a column name as a parameter. For example, within my let expression I have...

    #"Filtered Rows" = Table.SelectRows(ParamSource, each ([Carrier] = CarrierName) and (column= Lookupvalue)),

    So basically I'm trying to use a filter parameter,"Lookupvalue", and a column parameter ,"Carrier", in my function but I keep getting an error message.


  57. Just to clarify here... you're king of trying to do an intersect by providing the value from your first specified column and the column name of the second colum, is that correct? Like... if the Carrier column had "Verizon" and "AT&T" in it, plus there was a "Quantity" column, you want to get the intersect of "Verizon" and "Quantity"? Did I get that right?

  58. Super Cool Solution ... thank you very much. I just purchased your book "M is for (DATA) MONKEY. Looks like your Blog is also a wonder resource.


  59. Hi Ken,

    Thanks very much for your Posts they have helped me a lot.
    I am Battling with the code below, it works when I hard code the values as in Example 1:

    StartYear = fnGetParameter("StartYear"),
    EndYear = fnGetParameter("EndYear"),
    GrowerCode =fnGetParameter("GrowerCode"),
    Source = MySQL.Database("Server", "Database", [Query="select * from summerq_db2.TCalc where GrowerID_FK =('18') and HarvestDate >= ('2016/01/01') and HarvestDate = ('&StartYear&') and HarvestDate <= ('&EndYear&')"])

    It returns no values.... I am sure it has something to do with formatting or Data Types. The MySql Servers Date Formatting is YYYY/MM/DD and my computer is DD/MM/YYYY.

    I have tried different formatting in the Parameters Table in excel.

    Any help would be much appreciated..



  60. Ken,
    I have several queries that I run using PLSQL against an Oracle database. I am very new to Power Query and would like to use this instead of copying and pasting the results manually. I have put the query in Power Query and it runs fine, however, I have to manually change the date in the query. I found your article and I am having issues with the query picking up the date. Here is the query that works and the changes I made to try and get your function to work.

    Source = Oracle.Database("BRIAD.DB.CT", [Query="select s.location_id,s.POS_date, s.pos_file_name,
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date('03/21/2016','mm/dd/yyyy')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    #"Changed Type"

    Here is the changed query:
    SDate = fnGetParameter("SDate"),
    Source = Oracle.Database("BRIAD.DB.CT", [Query="select s.location_id,s.POS_date, s.pos_file_name,
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date(SDate,'mm/dd/yyyy')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    #"Changed Type"

    and the returned results:

    DataSource.Error: Oracle: ORA-00904: "SDATE": invalid identifier
    Message=ORA-00904: "SDATE": invalid identifier

    Any help would be appreciated. Thanks

  61. Hi Brandon,

    So I think the best route is just to reformat the StartYear and EndYear using something like this:
    StartYear = Date.ToText(fnGetParameter("StartYear"),"yyyy-mm-dd"),
    EndYear = Date.ToText(fnGetParameter("EndYear"),"yyyy-mm-dd")

  62. Hi Jeff,

    You have two (potential) issues here. The first is that you missed wrapping the date with the ' character. The second that may show up (as with Brandon's above) is the date format if your table actually holds a real date. If it's text, then ignore the modifications I made to the sDate line.

    SDate = Date.ToText(fnGetParameter("SDate"),"mm/dd/yyyy"),
    Source = Oracle.Database("BRIAD.DB.CT", [Query="select s.location_id,s.POS_date, s.pos_file_name,
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date("'"&sDate&"'",'mm/dd/yyyy')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    #"Changed Type"

  63. Ken,
    I am still having issues. I have tried your modifications, messing with the quotes and the format of the SDate value on my cover sheet and now I am getting errors. I am getting Expression.SyntaxError: Token Eof expected between line one and line 2.

    Any ideas what else I can check?

  64. Okay, so typically when we store the date in the parameter table, we store it as a proper date (i.e. a number which is formatted using one of Excel's number formats.) The reality here is that the date is actually a serial number representing (approximately) the number of days since Jan 1, 1900. The key I'm trying to impress here is that the formatting you apply to this cell will make no difference to Power Query whatsoever, as it will recognize the numeric serial number no matter which date format you apply. The only way to change that is to actually make it a text date, but I do NOT recommend that. Leave it as a number.

    When we try to pass it to the SQL query though, we need it formatted as text, and Power Query doesn't do implicit conversions. For that reason, we need to coerce it. That's what this piece is about:

    SDate = Date.ToText(fnGetParameter("SDate"),"mm/dd/yyyy"),

    With a token EOF message, I'd expect that you're probably missing a closing ) somewhere in the code. Can you post the current query (in it's entirety)?

  65. Here is the code and the error, which is different now:

    SDate = Date.ToText(fnGetParameter("SDate"),"mm/dd/yyyy"),
    Source = Oracle.Database("BRIAD.DB.CT", [Query="select s.location_id,s.POS_date, s.pos_file_name,
    from t_inv_hdr_pos s
    join t_location l on s.location_id = l.location_id
    where s.POS_date > to_date('SDate','mm/dd/yyyy')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    #"Changed Type"

    DataSource.Error: Oracle: ORA-01858: a non-numeric character was found where a numeric was expected

    Message=ORA-01858: a non-numeric character was found where a numeric was expected


  66. Okay, so see where you used 'SDate' in the second line? It's within the overall quotes, so it's being treated as the literal text characters, not putting in the variable.

    Try this query. This breaks the actual query into a separate step, so that you can look at and tinker with the query till you get it looking the same as the non-parameterized version:

    SDate = Date.ToText(fnGetParameter("SDate"),"mm/dd/yyyy"),
    varQuery = "select s.location_id,s.POS_date, s.pos_file_name, l.location_code,l.location_name from t_inv_hdr_pos s join t_location l on s.location_id = l.location_id where s.POS_date > to_date('" & SDate & "','mm/dd/yyyy')",
    Source = Oracle.Database("BRIAD.DB.CT", [Query=varQuery]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    #"Changed Type"

  67. I think I am closer. I did verify the parameter value is set to be formatted as a Date.
    Here is the error now:
    Expression.Error: We cannot convert the value #datetime(2016, 4, 1, 0, 0, 0) to type Date.
    Value=04/01/2016 12:00:00 AM

  68. = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})

    DataSource.Error: Oracle: ORA-01843: not a valid month

    Message=ORA-01843: not a valid month


  69. Hi Ken,

    Excellent article mate. looking for this solution for a long time.
    Just a question... is it possible to include "Column Name" from Parameter table (dynamically) and use that variable in PowerQuery SQL? I have tried but it didn't work. 🙁 I am new to PowerQuery.

    The solution I am looking for is that end user can select which column they want to filter and which value inside that column they want to filter too. e.g. My Data have 4 columns.... Country, State, City, Population..... User can select which column they want to filter between country, state or city and they can also filter their value too....

    is it possible?



  70. It depends on whether you know the names of the columns in advance. If you do, then you should be able to. If not then no, as you'd need to pull the query to find out what columns are needed.

  71. Thanks for your reply Ken.

    Do you have piece of code which I can leverage off some ideas.


    send me an email on my email ID and I can send you my row data file for your input.


  72. Hi Ken,

    So I've read through all these threads and I still can't seem to get my code to work.
    I'm starting very simple. My goal is to pass a parameter from a table in my worksheet through my query in Power Query.
    Here is my code:

    Source = Oracle.Database("//", [Query="select * from instructional_assignment where academic_period=('&Period&')"])
    in Source

    I get the following error: Expression.Error: We cannot convert the value 201610 to type Function.
    (Just value in my Term paramater is 201610 and it should be passed into the query in single quotes so if I were just typing it, it would be '201610' which works by the way.)
    Thanks in advance!

  73. Hi Again,

    So I was able to resolve the error by rebuilding the function and not invoking it. When it runs, it is showing the 201610 as the value in Period, however, my query still returns 0 rows. I'm pretty sure the problem is in my select statement where I define period, i.e. ('&Period&') but no matter what I try to change it to, I get a different error. Help!

  74. Hi Janet,

    Yes, the invocation actually changes the function, so you don't want that to happen.

    One thing you might try is forcing that Period to show up as text:

    Period = Text.From(fnGetparameter("Term")),

    That will solve any issues with trying to combine numbers and text in your query.

    Hope that helps,


  75. Yes, I tried that too. I don't have any errors but my results are empty. If I substitute with the actual text of '201610' in the code, I get results. I'm pretty sure it has something to do with the value types but I can't figure out how to resolve it. I've also tried another query where I just define the variable and not use the parameter as you suggested to someone earlier and it also gives me an empty result set. I can't seem to define the variable as text in this situation either.

    Source = Oracle.Database("//", [Query="select * from instructional_assignment where academic_period='&Period&'"])

  76. Curious, try this:

    qryOriginal = "select * from instructional_assignment where academic_period='201610'",
    qrySQL = "select * from instructional_assignment where academic_period='" & Period &"'",

    Source = Oracle.Database("//", [Query=qrySQL])

    Select the qryOriginal step, then the qrySQL step. They should be identical in order for this to work. (And if you want to execute it using the original to test, then just change the qrySQL to qryOriginal in the Source line.) Once they are the same, the dynamic query should execute, at which point you can delete the qryOriginal line.

  77. That worked! This code appears to be identical, except that we are using the "Query="
    Is that what did it?
    Now to take it one step further, what if I want to use an "in" statement so that regular sql would look like:
    "select * from instructional_assignment where academic_period in ('201610','201620', '201630')
    Would I put all of the numbers in one cell? Or do I need to create an array Parameter set? Thanks again.
    You are aresome! Thanks!

  78. OK, so I got it to work by just adding lines to my parameter table, i.e. Term1,Term2,term3
    I then added a variable for each one when reading the parameters in.
    I then updated my sql statement with an "or" adding the additional terms. It works but my problem will be if I don't need all three terms. My goal here is to have something so we can just update the parameter table when we need the report and not have to jump into the code.

    qryOriginal = "select * from instructional_assignment where academic_period='201610'",
    qrySQL = "select * from instructional_assignment where academic_period='" & Period &"' or academic_period='" & Period2 &"' or academic_period='" & Period3 &"'",

    Source = Oracle.Database("//", [Query=qrySQL])

  79. Hi Janet,

    Not, the Query= wasn't it. I think you were missing some " characters in the right place. I find it much easier to debug by breaking it into the separate steps though, as you can compare the before and after to ensure they are the same.

    With regards to your question, can I get you to post it in our forum at ? That would be a better place to continue on with this.


  80. Thanks for all your help. I figured out a solution. I really appreciate it! I will visit the forums.

  81. Pingback: Power Query and the Abyss | brentpearce

  82. You seem to have a solution for my problem, but I was wondering if there was a better approach.

    PROBLEM: I am getting data from a SQL table but I need to pass a variable to the query for the billing-month.

    I can either have the user change a drop down in a cell or prompt them for the bill month. I would then like to fire the query but with the variable embedded in the query.

    When I go to the Query Editor, Advanced Editor I can see the syntax of the full query but I'm unsure of how to replace the constant with a variable. It would be great if we could just replace the constant with a cell reference. But I'm not sure if that is possible for if it is I have been unable to gues the corret syntax so far.


    In the old days I could have used a ? in the Excel Query. But that no longer seems to work.

  83. Pingback: Quick Filter for Unique Values in Power Query and Power BI | Excel Inside

  84. Ken:

    Looks like you're ramping up on Power BI Desktop. I'm evaluating moving from Excel to Desktop. Nice feature of Desktop is the ability to define parameters. So based on this post I just create a parameter(s) that match what I had in the worksheet, open the advanced editor for the query that utilized the function, remove the line that calls the function and it works.

    What's interesting is in originally importing the Excel workbook into Power BI Desktop it recognizes that the queries depend on the worksheet and it asks if I want to copy the data or keep a connection to the original workbook. I chose the later and haven't tested it yet, but with the fix above I assume that I can still keep relying on refreshing the workbook via Power Update, start evaluating Desktop and keep the two in sync.

    Pretty neat.

  85. Hey Jeff,

    For reference, there is actually now the ability (at least in Excel 2016's subscription first release channel) to use Parameters as well, just like in Desktop.

  86. Hi,

    I got the following error when attempting to retrieve the file path parameter.

    "Expression.Error: We cannot convert the value "(ParameterName as te..." to type Function.
    Value=(ParameterName as text) =>
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")

    Any thoughts please on how to resolve?

    Thank you,

  87. Ken,

    If you were to run a Stored Procedure to populate power query. And you want to pass a value from Excel which populates the parameter for the stored Procedure. As follows :
    EXEC sp_RPT_CCN_Departmental_Stats_201611 [MonthYear] where MonthYear is a value in the original excel sheet.

    I am trying to run this from the optional SQL statement screen on the data connection. (I have provided SERVER, DATABASE and am trying to feed the Optional SQL statement part.

    This is my advanced Editor Source:

    Source = Sql.Database("CARDIACDB", "TWDAT", [Query="EXEC sp_RPT_Departmental_Stats_201611 'December2016'#(lf)"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProcedureDate", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CITY", "CaseStatus", "CASE_TYPE_ID", "CASE_TYPE_LABEL", "PHYSICIAN_ID", "ReferralSource", "PCI_PROC_TYPE_DRP"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [ProcedureDate] >= #date(2016, 12, 1) and [ProcedureDate] <= #date(2016, 12, 30)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ProcedureDate", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Week#", each Date.WeekOfMonth([ProcedureDate]))
    #"Added Custom"

  88. Hi Ken
    This excellent post helped me to do a rather simple task that I want to avoid doing manually: that is to append/merge several excel tables into just one (using Append option with Power Query) and be able to share it with other peoples regardless the path where they are copying my file.

    However, I've found a few issues and I thought that maybe you can help me (I apology in advance if this is not the correct place for this question, so I will post it to the Forum also, just in case):

    - As I need to Apply a Pivot Table to the merged table after the query, I found that my original file size increases dramatically (even just after the result of the append query alone, the size is doubled, and after I create the Pivot table, final size is even bigger)

    - If any source table is updated, then I need to update the query for the table and also the Append query, manually. As I have several tables being updated daily, the advantage of using PowerQuery is shadowed by the need to manually update each and every query on it. Not mentioning the fact that the file must be saved before updating the queries.

    So the question is: Do you know if is there a workaround to use relative path using plain old MS Query?

    Even when Power Query is way more powerful than MS Query, I feel that is too much for my basics needs, and my only problem with MSQuery is that I cannot share the file (or even move it to another folder in my computer) without losing the ability to update the query due to the "hardcoded" file path on the SQL expressions (and connection string)

    Hope you can help me with this. Thanks a lot.

  89. Honestly, MS Query is dead to me.

    Re the file size, I'd have to question if you are loading all your data to individual tables in the workbook and then appending them in another query? If so, then yes, your file size is going to get huge.

    Not knowing where your data source is, it's hard to advise on how to fix it for you, but if it's coming from external files, then I'd suggest that you're probably tackling this the wrong way and should be reading from folder, consolidating it into a single table, then building your Pivot off that.

    What I'd suggest is posting in our forum to get more specific help with your question.

  90. Hi Ken,

    This is a great tutorial. I am trying to pass text from the Parameters table into either a WHERE clause or a DECLARE statement. However, after I follow the steps to create fnGetParameter, Power Query in Excel 2016 wants me to "Invoke" the function and the "Close and Load to" option is greyed out until I press Invoke. If I do that, I get a Type=Type error when trying to refer to fnGetParameter in my SQL statement. Any help is appreciated!

  91. Hi Ken

    I have a couple of excel files in a folder that are all password protected. When I go into Power Query New Query -> From File -> From Folder then select the folder then Add Column using Excel.Workbook([Content]) the new column has an Error. However when I remove the password from the excel file it works fine.

    When I click on the Error i get the following message

    DataFormat.Error: External table is not in the expected format.

    How do I get it so I can still have a password on the file but still be able to bring in all the files.



  92. Sorry Jon, but passwords pretty much kill you here. Even if you were using VBA you'd have to un-protect each workbook first. There's no facility that I'm aware of yet that can handle this in PQ.

  93. Can't u use:

    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    Value = ParamSource{[Parameter=ParameterName]}[Value]

  94. Hi Kees, indeed you can. 🙂 Much cleaner than the version I built. (Mine was originally built via the UI ages ago, and I've never looked back to see if I can improve on it.)

  95. I need a real number in the query of an ODBC-SQlite-statement.
    So I cannot use Number.ToText() or Text.From().
    Then I also cannot use "&" to concatenate the query.

    How to query with numbers/integers and false conditions (!=) ?
    Power Query doesn't do implicit conversions.
    How can we hint this?

  96. Just for other readers. My assumption was wrong. There is no need for real integers- implicit conversions in a SQlite query from within Power Query, as long as we don't use single quotes.

  97. Hi Ken,
    I'm new in power query world and i would appreciate if you can please explain how can i update the data in power query by typing a new path. I followed your steps, but unfortunately it is not working for me.
    Let's say I built a parameter query as you suggested and have a file path of the location outside of this current file. Then I bring that table from outside. Then I change the path to the following month. And refresh the power query. It's not working. What am I missing here. Any references or videos you can point to would be great to see. Thank you Ken.

  98. Hi Greg,

    Out of curiosity, did you end your path with a trailing \ ? That's one thing I can think that might cause the issue. Overall though, the method you are using SHOULD work. If you need help getting it sorted I would suggest posting in our forum at and we can get it working for you.

  99. Hi Ken,
    I'm trying to replicate the workbook as what you have but with some different features:
    - In my case I have a data source file which gets updated every month and gets saved in a new folder (Jan, Feb etc.). Beside the fact that I make some change steps to it (which power query remembers), I need to update this query by changing the location every month. How would it be different in this case?
    I noticed you are using a Filename formula. This formula will point on a location of the existing file (where formula resides). But I have a workbook with one or more worksheets that I need to connect to a source file which will be changing every month. I tried different ways, so far not sucessful. I appreciate your help.

  100. Hi Greg,

    If your filename is changing every month, then I would do this:
    -Connect to the root folder where you store your data
    -Filter to the "most recent" date for created or modified or whichever one filters down to the last created file for you
    -Drill in to your file and adjust the path to be using something like Excel.Workbook([Content]{0})

    If you need help implementing it, I'd suggest posting in our forums as experts are there more frequently than I can get to blog comments.

  101. Hi Ken
    I am trying to change a static filter to a dynamic filter for both the column name to be filtered and the value.
    So far I have managed to create a parameter table in excel and make the following changes to the syntax:
    Source = Excel.Workbook(File.Contents("C:\Data YM\All members profile data YM.xlsx"), null, true),
    Search_criteria_Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
    pColumn_name = Search_criteria_Parameter{0}[Name of parameter],
    pColumn_value = Search_criteria_Parameter{0}[Value],
    #"Data YM_Sheet" = Source{[Item="Data YM",Kind="Sheet"]}[Data],
    #"Promoted Headers1" = Table.PromoteHeaders(#"Data YM_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Web_Site_Member_ID", Int64.Type},
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each pColumn_name = pColumn_value)
    #"Filtered Rows"
    I get no errors but no resultant rows are displayed.
    When I replace pColumn_name with [Web_Site_Member_ID] the query works.
    When I step through the applied steps the value for pColumn_name displays correctly as Web_Site_Member_ID.

    Can you please assist- first time using power query.

  102. Hi, Ken. Thank you for your article. I'm very new to Power Query. I tried to replicate your example and received "Expression.Error: The name 'fnGetParameter' wasn't recognized. Make sure it's spelled correctly." This error is referring to = Table.AddColumn(#"Changed Type", "File Path", each fnGetParameter("File Path")).

    My code for Query1 is:
    (fnGetParameter as text) =>
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameters] = fnGetParameter)),
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")

    My code for Table 1 is:
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MyTable", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "File Path", each fnGetParameter("File Path"))
    #"Added Custom"

    Are you able to determine the error in my code that is causing the error message and my table to not load? Thank you.

  103. Hey Mike,

    You called the parameter Query1? Just rename that query (above the applied steps window on the right) to fnGetParameter and you should be fine.

  104. Hi Ken,

    I hope you can advise me. I have a rather long SQL (which works) but I am focusing on changing the Date range to be defined as Parameters. I am currently getting an error: "Conversion failed when converting date and/or time from character string", which i presume is to do with some date formatting or conversion but I dont know where the problem is.

    My Query is:
    sStartDate = fnGetParameter("StartDate"),
    sEndDate = fnGetParameter("EndDate"),
    Source = Sql.Database.....[Query=....WHERE T3DATE BETWEEN 'sStartDate' AND 'sEndDate' .....

    (ParameterName as text) =>
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")

    My Parameters Table is formatted to be "dd/mm/yyyy hh:mm" at this moment (but have tried may formats).

    Can you advise me ? Thank You.

  105. Hi Ben,

    I really recommend that you break your query into separate parts like I did in my post, building the SQL query separately. This way you can click on the query step and see the exact text you are sending. As written, yours is literally sending "WHERE T3DATE BETWEEN 'sStartDate' AND 'sEndDate'" to the query, not "WHERE T3DATE BETWEEN '1/1/2010' AND '12/31/2010'".

    You will need to break up your query so things look a bit more like this:

    WHERE T3DATE BETWEEN '" & sStartDate & "' AND '" & sEndDate & "'

    Hope that helps!

  106. Hello Ken,

    I've used this function fnGetParameter successfully with Excel files and Power Query. Thanks a lot. Now, I am learning Power BI.

    How would you implement this feature in Power BI to be able to move the PBIX files and the DataFiles from one folder to another (or rename folders along the path), without having to update the DataFiles path within the PBIX file manually?

    Specifically, if I have the files like this.


    And I rename the folder or the path, or move to another drive or computer...


    Thanks in advance,

  107. Hi Ken.
    Recently I ran into a snag:
    When the Excel file is on a OneDrive and is connected (logged on), the output of CELL("filename",A1), produces a URL file: personal/username_useraccount_onmicrosoft_com/Documents/MyFiles

    I read that when off-line OneDrive accesses it's locally cached OneDrive, which produces a result like the following:

    following Microsoft's UNC (uniform naming convention) format.

    If OneDrive generates a UNC, then the PowerQuery M function Folders.File(SolutionPath) has no problem. But when OneDrive generates a URL, the M function throws an error.

    Any suggestions?

    Thanks, Dan

  108. Yep, that's irritating, isn't it? I run into this frequently when I open the file from my local version, but the OneDrive logs the url instead of the original file path.

    The only solution I know of is to recode it to pull from a sharepoint folder (that's what OneDrive is) so that it can always see the data and not get tripped up by the path.

  109. I converted the front end of my "load" sequences to handle both situations, so if I'm not logged onto OneDrive/sharepoint/on the cloud, then CELL("filename",A1), returns a C:/XXX.XXX.xlsx UNC, but if I'm logged on, it returns a https://my-sharepoint URL - either way I get all files in a folder. But I think don't understand why Microsoft's engineers think users would be OK with this complexity - making them handle the UNC/URL flipflop - building it into user steps or function, building a wrapper. And it seems counterintuitive - the local drive is faster than cloud access for file downloads, which is one big reason why users all over the world have OneDrive loaded on their local drive, right? My question for you, being an expert Excel guy, is: if logged on to Office 365/OneDrive, and user clicks on an Excel file listed in directory at C:/users/me/myfile.xlsx, is Excel loading the file directly from the cloud? Or is it loading the local copy, knowing that Sync has the most updated version. Perhaps there's a function other than CELL("filename",A1) that can reference the UNC of the local xlsx?

  110. I believe it looks at the local file copy, not the remote. And while faster, there could be 3 people editing it at this point. So in truth, it's probably better to call from the cloud path at that point.

  111. Hi Jan,

    Sort of. You can create a table in Power BI and use this technique. What you can't do is create a dynamic file path there.

  112. Hi Frank,

    Unfortunately there is no way to pull a dynamic file path in Power BI Desktop. The trick we use in Excel leverages a hack to read from an Excel function and pull that into Power Query. Power BI doesn't have that same ability. I wish it did.

  113. i Ken,

    I'm almost there but not quite as I keep getting an "Expression.SyntaxError: Token Comma Expected" error.

    The scenerio is that I have a query stored which begins
    = Sql.Database(",48002", "my_db_name",
    #(lf)DECLARE @Due_Date VARCHAR(15) = 'June 18'
    #(lf)DECLARE @Company01 VARCHAR(50) = 'TestCompany01'
    If I leave the above @Due_Date and @Company01 hard coded everything works exactly as expected.

    I have created the fnGetParameter function as described and when tested it pulls back the Correct values for each of the above as defined in the Parameter table.

    Changing the code to the below is when I get the error, even if (for example) I only use it for the Company name.

    = Sql.Database(",48002", "my_db_name",
    #(lf)DECLARE @Due_Date VARCHAR(15) = fnGetParameter("Due_Date")
    #(lf)DECLARE @Company01 VARCHAR(50) = fnGetParameter("Company01")

  114. So the issue is that your query is actually sending "fnGetParameter("... which is not what you want.

    Try making this modification:

    VARCHAR(15) = " & fnGetParameter("Due_Date") & "

    Notice the quotes before and after the call. You're basically concatenating text strings here, and need to terminate the string, add the fnGetParameter and then open the text string again.

    Having said this, I assume that this will most likely violate the formula firewall. I'd suggest moving the fnGetParameter calls up to assign them to variables before you call the Sql.Database, then refer to the variables. In fact, if you can avoid using a sql query in this way, you'll allow query folding to continue. (You'll break it right away in this manner, which could cause performance issues for you.)

  115. Thanks Ken.

    Ok the good news is if I make that modification (plus wrap the text in the Parameter with ' then the Native Query Editor window I can see it being passed through exactly as I expect.

    You are quite correct though, regardless of what I set the Privacy level too then I get the following message.

    "Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."

    It does work however If I elect to Ignore Privacy levels (which I accept isn't ideal)

    TBH I'm very much a n00b when it comes to this sort of thing, learning by trial & error (and a lot of help from Google lol)

    NB Am aware that there is a way of doing it using a stored procedure however I have Read Only acces to the DB I am querying so can't make use of that method.

  116. Okay, so to be honest, I wouldn't actually feed this a native query at all. Here's what I would do:
    -Create a worksheet that holds your parameter cells
    -Create a query for each parameter that drills down to one row with one column, right click it and choose Drill Down to get to the specific value. Name this and load it as connection only
    -Create a new query to connect to your database using the browser windows, then use the Power Query commands to filter down to the data you want. (Avoid writing SQL as Power Query does this for you.)
    -Edit the query in the Advanced Editor and add lines to refer to each of your variables. Something like:
    Var1 = VariableOneName,

    Then replace the values you used for your filters with Var1 and so on.

    You will still get prompted about your data privacy, but if you set them to organizational, this should avoid both the native query and formula firewall prompts, as well as run more efficiently than your original query as you can keep query folding alive.

  117. =LEFT(CELL("filename",B6),FIND("[",CELL("filename",B6),1)-1) is BLANK until you save the workbook.

  118. Hi Ken,
    First off - thank you for your blog and great book - M is for Data Monkey. I've been using power query to harness data in my company's ERP database and it's fantastic.

    I have Office 365 and in that there is an Invoked Function, which I see you have addressed already in a previous post.

    When I try to apply the value to create a custom column in another query, I keep getting a firewall error. Effectively I am trying to apply a date column to a table, and that date will be dynamic.

    Could you please advise the best way to navigate this.

  119. Hey Brian, this is a real pain, to be sure. The first thing I would suggest is to assign the fnGetParameter calls to variables before you do anything else in the query. If that doesn't work, the only way to "fix" the issue is to turn off the privacy settings for the workbook. I wish I had a better answer.

  120. My quest is over. This solves my greatest challenge in one specific solution.

    "Guru" is appropriate.


  121. Pingback: Creating Dynamic Parameters in Power Query - The Excelguru BlogThe Excelguru Blog

  122. Hi Ken.

    Thank you very much for this solution. I've run into this problem today and couldn't for the life of me figure out why PQ is throwing the error of "supplied file path must be a valid absolute path", while this same path worked totally fine in a simple query, not a function. This trick solved my problems. Good thing I decided to re-read your book!

    My question is - five years later, is there still no built-in way to pull path for the current workbook?


  123. So I loved this method until I looked at the Query Dependencies chart and I couldn't understand the spiderweb that fnGetParameter created as a function.

    Instead I've become fond of putting in a one line step that is a direct query such as:

    #"PARAMETER Folder" = Table.SelectRows(Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content], each ([Parameter] = "Folder")){0}[Value],

    I then reference this step like a variable...

    Source = Folder.Files( #"PARAMETER Folder" & "Trxns"),

    This allows me to pull directly from the tblParameters like in the guide above, circumvent the function fnGetParameter, keeping the dependency illustration clean, while still maintaining some resemblance of dynamic.

    Although I've hard-coded the name of the Parameter, it's no more hard-coded than say fnGetParameter(ParamName) would be...

  124. Hi Peter,

    That works, for sure. Personally, I've just copied and pasted the function, and for me it's a lot easier to remember fnGetParameter() than the code you have above. (Albeit, that could be done with a copy/paste as well.) I haven't really worried about the dependency view, but I certainly get your point. 🙂

  125. I am trying to bring in a date from the parameter table, but it looks like it comes in as date-time.
    After I bring in the parameter, how do I convert the parameter to date and not date-time?

  126. Pingback: Parameter Tables in Power BI / Power Query — Powered Solutions

  127. I am trying to create a query in SQL that takes a list of parameter that is in an other power BI table (table1)
    I am trying to use in the advanced query editor with the following statement:
    ListofValues = Text.Combine(table1[columnanyway],",")
    Source = Sql.Database("Servername ", "DBname", [Query="SELECT * FROM dbo.table2 where Column1 in ("&ListofValues &")" ])

    I get the following error :
    Expression.SyntaxError: Token Comma expected.

  128. Hi Yohavst,

    Yes, you're missing a comma at the end of the ListofValues line. It should read:
    ListofValues = Text.Combine(table1[columnanyway],","),

  129. Hi thanks for this solution
    but now I have to deal with the following:

    Formula.Firewall: Query 'QueryName' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

  130. Did you nest the fnGetParameter() call inside the Source step, or create a separate step first the way I showed in the example? I find it better to separate these...

  131. Hi Ken
    I have already created blank query which transform a column into a string (comma separated) with the operator:
    I called it "Myquery".

    I do would create a separate step first but I didn't find the way to do it with a separated function and it seem that it is what you are suggesting.
    Unfortunatelly I cannot use prameter from an excel table.

  132. Hi Ken, BIG THANKS for sharing the fnGetParameter() function. It has been a life saver for me here 🙂 I been using it for past 6 months, however ~2mths ago, I encountered below error when refreshing my local copy of OneDrive for Business data folders:

    "We couldn't refresh the connection 'Query - MonthlyData' Here's the error message we got: [DataFormat.Error] The supplied folder path must be a valid absolute path."

    The "=LEFT(CELL("filename",B6),FIND("[",CELL("filename",B6),1)-1)" returned the value as "" instead of "D:\OneDrive\MonthlyData\"

    After months of searching for solution, I finally found out the root caused with OneDrive settings. I accidentally turn on the "Use Office applications to sync Office files that I open" (Settings > Office > File collaboration > [Use Office applications to sync Office files that I open)

    After I unchecked the above option, and reopen the Excel, it now shown the expected "D:\OneDrive\MonthlyData\" instead of "https:..."

    I hope this helps as I saw similar problem reported earlier. Thanks.

  133. Hi Jason,
    The main issue at play here is that the OneDrive setting is ridiculously heavy handed about injecting the URL, not the file path. If you were in OneDrive for Business, there is a workaround for this, but for OneDrive personal (which appears to be what you're using based on the bit) has no Power Query connector. It's maddening!

  134. I didn't read through all of the comments, so apologies if I'm restating something that someone else already did. What I had been looking for was to figure out a way to make a Calendar table that was truly dynamic (both start and end dates) to the Transactions data I was bringing in (which was a report coming into PowerQuery from a web service report). This data was being refreshed every month to bring in the next months' worth of data. Thus, I didn't have a local file path to list, and I also didn't want to have to keep updating a separate spreadsheet.

    What I ended up doing was duplicating an existing table in PowerQuery containing the Transactions data, removing all the other columns except for date, removing duplicates, and then using that as the base to perform the rest of the instructions that listed on this site:

    I then followed the steps listed on the following website to build out the calendar metadata (years, months, month-year, etc.): Search for the "Add Calendar Metadata" section on this page:

    What I have now is a calendar table that is completely dynamic based on the data I'm already bringing in, and will be updated/refreshed every time I refresh the other query. The start and date dates of the Calendar table are the same as the start and end dates on that Transactions report I'm pulling in - this is exactly what I wanted! I hope this is helpful for someone else.

  135. Hi Alicia,

    Try this one:

    It's a pattern I've been using for years, which works really well. One big change I'd make in your scenario... reference, don't duplicate. A reference will re-use the query you are calling it from, rather than getting the data again. For what it's worth, we have tons of patterns (including the calendar ones) available in our recipe card sets at

  136. Hello Ken,
    Thanks for this! I'm totally new to Power Query so I don't quite understand the syntax yet. After I copy pasted the code to the Advanced Editor, It brought up an Invoke Function step where it asks for ParameterName. Is this where I put fnGetParameter? I did that but the next step when I try to do the fnGetParameter("File Path") in the Custom Column threw up an error: "Expression.Error: The name 'fnGetParameter' wasn't recognized. Make sure it's spelled correctly.". Not sure what I'm doing wrong

  137. Hi Ellen,

    Nope, just ignore what's in the centre of the screen. You can rename it on the right side, then go to Home --> Close & Load. At that point it will create the fnGetParameter function for you, and you can follow the rest of the article.

    Alternately, if you're on Excel 2016 or higher, download a copy of That will let you just inject the query into your workbook correctly, and the user guide (which you can get from the Help menu) teaches you how to implement it into your query.) There's a free two week trial of the tool, and there is no requirement to keep it installed after you use it to inject your queries.

  138. Ken, this is a fantastic Function....use it for a lot of things. We recently started to migrate to OneDrive and when I try to use this to get the file path, I get the error: "The supplied folder path must be a valid absolute path.". As noted about, you mention that OneDrive injects the URL (which is does for sure). You then mention a work around for OneDrive for Business (which is what I use) but didn't mention what it is...can you give me a pointer on a work around for this please?


  139. Dear Ken
    My heart is full of thankfulness for this GREAT article.
    It helped me to get my Excel workbook connected with Coinbase's public API for price updates by requesting candle data.
    All the best four you from Berlin.

  140. Hi Ken,

    I am trying to make this solution work on Mac - I need the solution that works for both PC & Mac. On Mac however I am still getting a message about violating the privacy levels and there is no privacy level settings window/tab there (also no Power Query editor). Is there a way around these privacy firewall on Mac and still use relative paths for queries as well?

  141. Hi Tomasz,

    Are you trying to call the function inside a step? I prefer to set up a new line - immediately after the let line - like this:
    variable_name = fnGetParameter("Parameter Name"),

    I then refer to the variable_name inline when I call for my file/folder. That usually avoids the Formula Firewall in Excel on Windows, and should be treated the same on Mac.

  142. Pingback: Power Query “Folder Import” auf Basis einer Excel-Fileliste realisieren – Linearis :: Self-Service Business Intelligence

  143. Hi Ken,

    Mac is much more strict on the firewall formula and still throws an exception, even though Windows does not. However, I managed to get by this issue by using macros and setting a parameter in PQ, then using parameters in Source part of the Query. This does not trigger firewall, but still I cannot get it working on Mac ad I get "Access to the file is denied" - looks like accessing other files in PQ on Mac does not work? I get similar error even if I do this manually... I gave up for now - the solution's working fine on Windows and we'll manage for some time with it while waiting for PQ editor to be officially available to Mac users.

  144. Hi Ken,

    When I click on invoke function I'm getting a null value. Not sure why ? I did exactly what you did.

  145. Hi Rohini, did you get a prompt to fill in the value you are looking for? If so, check that it is cased the same as what is in your table. Power Query is case sensitive.

Leave a Reply

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

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

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »