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.  I'll get this sorted out, but in the mean time, just make sure you replace all "curly quote" characters with straight quotes.

To do this successfully, we need to 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”.

SNAGHTML192eb95

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:

SNAGHTML195b12d

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()

SNAGHTML197a4ef

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) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
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:

image

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:

SNAGHTML1a313c0

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!

image

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”)

image

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.  I.e. 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:

let
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")
in
#"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.  Doh!

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:

let
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")
in
#"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.

103 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.

    Thanks!

  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],
    vFromDate=DateTime.ToText(Parameter{0}[Value],"yyyy-MM-dd"),
    vToDate=DateTime.ToText(Parameter{2}[Value],"yyyy-MM-dd"),
    ===============================================
    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) =>
    let
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")
    in
    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:
    ------------------------------------------------
    NATIVE DATABASE QUERY

    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?

    Thanks!

  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:
    let
    MYEDP = fnGetParameter("EDP"),
    Source = Oracle.Database("10.1.1.24:1521/RJSM032", [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"])
    in
    Source

  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:
    WHERE A.ITEM_EDP_ID = (MYEDP)
    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:

    let
    MYEDP = Number.From(fnGetParameter("EDP")),
    Source = Oracle.Database("10.1.1.24:1521/RJSM032", [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
    Source

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

    let
    MYEDP = Number.From(fnGetParameter("EDP")),
    Source = Oracle.Database("10.1.1.24:1521/RJSM032", [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
    Source

    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:
    let
    Source = Oracle.Database("mySchema.server"),
    tmp = Source{[Schema="mySchema",Item="OrganizationView"]}[Data],
    #"Filtered Rows" = Table.SelectRows(tmp, each ([Period] = 20150331))
    in
    #"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?

    let
    Source = Oracle.Database("server"),
    Organization = Source{[Schema="mySchema",Item="OrganizationView"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Organization, each ([Period] = (fnGetParameter("Period")))
    in
    #"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,
    Tom

  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:

    let
    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))
    in
    #"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?
    Thanks!

  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:
    myid=999,
    myproject=111,
    -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),

    Thanks.

  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. Hi Ken,
    Following your answer, I did the following:

    let
    LimitValue=fnGetParameter("TheLimit"),
    Source = ...[Query="select * from table1 limit LimitValue"])
    in
    Source

    But I get this error:
    Message=Undeclared variable: LimitValue
    ErrorCode=-2147467259

    Maybe because it should return a number and not Text?
    I did all the steps as described here:
    http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

    any idea?
    Well appreciated!
    Lekfir

  36. 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!

  37. Thank you Ken!

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

    And I get the error:

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

    Please help 🙁

    Thanks again

  38. 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

  39. 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

  40. 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"?

  41. 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:

    LimitValue=Text.From(fnGetParameter("TheLimit")),

    Hope that fixes it up for you!

  42. 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

  43. 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.

  44. 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.
    Done.
    Hmm, modified 4 rows....
    let
    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.
    Details:
    Value=Table
    Type=Type

    I'm so hopeless at this.

  45. Also tried...
    Let
    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.

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

  47. 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!

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

  49. 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?

  50. 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.

  51. 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!")")

  52. 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.

  53. 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...

  54. 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:
    let
    SnapshotStart = fnGetParameter("Parameter"),
    Source = Sql.Database("UKCHCRMSQL01.global.spiraxsarco.com\uksalescrm", "UKSalesDataWarehouse",
    [Query="select * from UKSalesDatawarehouse.[dbo].[fOpportunitySnapshotTable] (SnapshotStart)"])
    in
    Source

    Any help would be hugelyappreciated

  55. 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:
    Query1
    let
    servername = () =>
    let
    Origen = "XXXXX.database.windows.net"
    in
    Origen
    in
    servername

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

    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,

  56. 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. 🙁

  57. 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.

    Thanks!

  58. 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?

  59. 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.

    Charles

  60. 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:

    let
    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&')"])
    in
    Source

    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..

    Regards,

    Brandon

  61. 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.

    let
    Source = Oracle.Database("BRIAD.DB.CT", [Query="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('03/21/2016','mm/dd/yyyy')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    in
    #"Changed Type"

    Here is the changed query:
    let
    SDate = fnGetParameter("SDate"),
    Source = Oracle.Database("BRIAD.DB.CT", [Query="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')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    in
    #"Changed Type"

    and the returned results:

    DataSource.Error: Oracle: ORA-00904: "SDATE": invalid identifier
    Details:
    DataSourceKind=Oracle
    DataSourcePath=briad.db.ct
    Message=ORA-00904: "SDATE": invalid identifier
    ErrorCode=-2146232008

    Any help would be appreciated. Thanks

  62. 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")

  63. 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,
    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')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    in
    #"Changed Type"

  64. 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?

  65. 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)?

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

    let
    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,
    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')"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POS_DATE", type date}})
    in
    #"Changed Type"

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

    Details:
    DataSourceKind=Oracle
    DataSourcePath=briad.db.ct
    Message=ORA-01858: a non-numeric character was found where a numeric was expected

    ErrorCode=-2146232008

  67. 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:

    let
    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}})
    in
    #"Changed Type"

  68. 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.
    Details:
    Value=04/01/2016 12:00:00 AM
    Type=Type

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

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

    Details:
    DataSourceKind=Oracle
    DataSourcePath=briad.db.ct
    Message=ORA-01843: not a valid month

    ErrorCode=-2146232008

  70. 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?

    Thanks.

    Harry

  71. 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.

  72. Thanks for your reply Ken.

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

    Or

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

    Cheers.

  73. 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:

    let
    Period=(fnGetparameter("Term")),
    Source = Oracle.Database("//auxdb.victoriacollege.edu:1521/odsp.victoriacollege.edu", [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.
    Details:
    Value=201610
    Type=Type
    (Just fyi...my 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!

  74. 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!

  75. 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,

    Ken

  76. 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.

    let
    Period=201610,
    Source = Oracle.Database("//auxdb.victoriacollege.edu:1521/odsp.victoriacollege.edu", [Query="select * from instructional_assignment where academic_period='&Period&'"])
    in
    Source

  77. Curious, try this:

    let
    Period=Text.From(fnGetparameter("Term")),
    qryOriginal = "select * from instructional_assignment where academic_period='201610'",
    qrySQL = "select * from instructional_assignment where academic_period='" & Period &"'",

    Source = Oracle.Database("//auxdb.victoriacollege.edu:1521/odsp.victoriacollege.edu", [Query=qrySQL])
    in
    Source

    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.

  78. 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!

  79. 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.

    let
    Period=Text.From(fnGetparameter("Term1")),
    Period2=Text.From(fnGetparameter("Term2")),
    Period3=Text.From(fnGetparameter("Term3")),
    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("//auxdb.victoriacollege.edu:1521/odsp.victoriacollege.edu", [Query=qrySQL])
    in
    Source

  80. 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 excelguru.ca/forums ? That would be a better place to continue on with this.

    Thanks!

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

  82. Pingback: Power Query and the Abyss | brentpearce

  83. 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.

    HELP!!!

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

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

Leave a Reply

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