Building a Parameter Table for Power Query

2014-11-26
Ken Puls
Categories: Excel, Power BI, 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: If you want an easier way to install this into your solution, check out my Monkey Tools add-in. The Parameter Table & Function Monkey creates the table and injects the fnGetParmeter function in two clicks... even on a Free license!

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) =>
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:

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:

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

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.

196 thoughts on “Building a Parameter Table for Power Query”

Jimmy on January 26, 2015 at 1:25 pm said:

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.
Ken Puls on January 26, 2015 at 2:15 pm said:

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...
Amit on January 29, 2015 at 1:49 pm said:

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!
Ken Puls on January 29, 2015 at 1:52 pm said:

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
Anand on February 13, 2015 at 5:35 am said:

Hi Ken,
Can I paas the parameter to SQL Table as well. Please throw some light.
Ken Puls on February 13, 2015 at 8:50 am said:

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?
Thomson on March 3, 2015 at 3:51 pm said:

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 !!!!
Ken Puls on March 3, 2015 at 4:32 pm said:

Glad you found it useful!
Pingback: Power Query Errors: Please Rebuild This Data CombinationThe Ken Puls (Excelguru) Blog
Pingback: Bob The (Proto) Builder | Bob's BI Ramblings
Martin on March 13, 2015 at 2:21 pm said:

Great article, thank you!
Pete on April 9, 2015 at 2:30 am said:

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 🙂
Ken Puls on April 9, 2015 at 7:58 am said:

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")
Talha on April 20, 2015 at 6:43 am said:

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!
Ken Puls on April 22, 2015 at 10:39 am said:

Hi Talha,

Great question, and I've put together a post to answer it: https://excelguru.ca/2015/04/22/native-database-query-security-prompts/
AlexJ on April 26, 2015 at 9:17 am said:

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?
Ken Puls on April 27, 2015 at 9:41 pm said:

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.
Thomson on May 3, 2015 at 4:45 pm said:

Now you can disable this warming message in PowerQuery Options (version 2.22.4007.242) !!!
Ken Puls on May 4, 2015 at 8:43 am said:

Thomson is referring to the Native Database Query prompt, which you can read more about here: https://excelguru.ca/2015/04/22/native-database-query-security-prompts/
Dave Thayer on May 14, 2015 at 6:11 am said:

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
Ken Puls on May 20, 2015 at 9:17 am said:

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.
Dave Thayer on May 21, 2015 at 12:50 pm said:

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.
Ken Puls on May 21, 2015 at 5:15 pm said:

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?
Dave Thayer on May 22, 2015 at 5:21 am said:

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
Ken Puls on May 22, 2015 at 7:29 am said:

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 & "
Tom De Cort on May 26, 2015 at 5:49 am said:

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"
Dave Thayer on May 26, 2015 at 5:54 am said:

O.K. - I got it. Thanks.
Ken Puls on May 26, 2015 at 7:52 am said:

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.
Tom De Cort on June 26, 2015 at 6:04 am said:

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
Ken Puls on June 26, 2015 at 7:51 am said:

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.
Chris on June 30, 2015 at 10:03 am said:

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!
Ken Puls on July 1, 2015 at 9:35 am said:

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?
Chris on July 1, 2015 at 9:58 am said:

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!
Chris on July 1, 2015 at 10:17 am said:

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...
Ken Puls on July 1, 2015 at 10:23 am said:

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"
lekfir on September 13, 2015 at 12:10 am said:

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!
Ken Puls on September 14, 2015 at 9:21 am said:

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.
Pingback: Share and Refresh Power Query with Local Links - PowerPivotPro PowerPivotPro
Mike on October 6, 2015 at 9:09 am said:

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.
Ken Puls on October 6, 2015 at 10:08 am said:

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,
Mike on October 7, 2015 at 5:20 am said:

Perfect. Not sure how it works but it works. Hopefully, this will be covered in detail in your M book. Thanks Ken.
lekfir on October 8, 2015 at 1:56 pm said:

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:
https://excelguru.ca/2014/11/26/building-a-parameter-table-for-power-query/

any idea?
Well appreciated!
Lekfir
Ken Puls on October 8, 2015 at 2:03 pm said:

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!
lekfir on October 10, 2015 at 2:48 pm said:

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
Ken Puls on October 10, 2015 at 2:57 pm said:

Try converting the first line to a number:

LimitValue=Number.From(fnGetParameter("TheLimit")),
lekfir on October 11, 2015 at 6:03 am said:

Unfortunately, I get the same error...
lekfir on October 13, 2015 at 2:11 pm said:

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
Clive on October 22, 2015 at 5:43 am said:

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
Ken Puls on October 22, 2015 at 8:10 am said:

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"?
Ken Puls on October 22, 2015 at 8:11 am said:

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!
Fred on October 22, 2015 at 3:41 pm said:

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
Ken Puls on October 23, 2015 at 4:15 pm said:

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.
Fred on November 11, 2015 at 9:09 pm said:

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.
Fred on November 11, 2015 at 10:47 pm said:

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.
Fred on November 12, 2015 at 2:04 pm said:

Ah right, I'm trying to import 12 sheets of data not just one, so this is all wrong.
Back to square one.
Fred on November 12, 2015 at 2:31 pm said:

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!
Ken Puls on November 16, 2015 at 8:17 am said:

Fred, sorry for the late reply (busy week last week), but I'm glad you got it sorted.
Pingback: Using Parameter Tables To Control Data Refresh In Power BI | Chris Webb's BI Blog
James on January 22, 2016 at 3:23 pm said:

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?
Ken Puls on January 22, 2016 at 3:39 pm said:

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.
James on January 22, 2016 at 3:44 pm said:

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!")")
James on January 22, 2016 at 3:45 pm said:

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.
Ken Puls on January 22, 2016 at 3:50 pm said:

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...
James on January 22, 2016 at 3:55 pm said:

Thanks. I will give that a bash!
Ross Palmer on February 19, 2016 at 2:29 am said:

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
Alberto Balañá on February 26, 2016 at 4:49 am said:

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,
Ken Puls on February 26, 2016 at 12:35 pm said:

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. 🙁
Jeff on February 27, 2016 at 2:29 pm said:

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!
Ken Puls on February 29, 2016 at 6:01 pm said:

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?
Charles on March 14, 2016 at 3:42 pm said:

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
Ken Puls on March 14, 2016 at 5:52 pm said:

Thanks Charles!
Brandon Rademan on April 6, 2016 at 6:50 am said:

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
Jeff Baisch on April 6, 2016 at 10:37 am said:

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
Ken Puls on April 6, 2016 at 12:34 pm said:

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")
Ken Puls on April 6, 2016 at 12:39 pm said:

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"
Jeff Baisch on April 7, 2016 at 8:07 am said:

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?
Ken Puls on April 7, 2016 at 8:15 am said:

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)?
Jeff Baisch on April 7, 2016 at 8:34 am said:

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
Ken Puls on April 7, 2016 at 8:47 am said:

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"
Jeff Baisch on April 7, 2016 at 9:10 am said:

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
Ken Puls on April 7, 2016 at 9:34 am said:

Wrap it in Date.From() That will convert a DateTime to a Date.
Jeff Baisch on April 7, 2016 at 9:46 am said:

What am I wrapping in the Date.From()?
Ken Puls on April 7, 2016 at 9:47 am said:

SDate = Date.ToText(Date.From(fnGetParameter("SDate")),"mm/dd/yyyy"),
Jeff Baisch on April 7, 2016 at 9:57 am said:

= 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
Ken Puls on April 7, 2016 at 10:08 am said:

Hey Jeff,

Can we pick up further follow up on this in the forum at http://www.excelguru.ca/forums/forumdisplay.php?19-Power-Query-(Get-amp-Transform)

I think that's the more appropriate place to do this. I'll watch for the post there.
Harry on April 18, 2016 at 8:11 pm said:

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
Ken Puls on April 19, 2016 at 7:20 am said:

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.
Harry on April 19, 2016 at 8:41 pm said:

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.
Janet Charbula on May 3, 2016 at 6:49 am said:

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!
Janet Charbula on May 3, 2016 at 7:08 am said:

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!
Ken Puls on May 3, 2016 at 9:00 am said:

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
Janet Charbula on May 3, 2016 at 10:10 am said:

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
Ken Puls on May 3, 2016 at 10:28 am said:

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.
Janet Charbula on May 3, 2016 at 11:37 am said:

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!
Janet Charbula on May 3, 2016 at 12:19 pm said:

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
Ken Puls on May 4, 2016 at 3:18 pm said:

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!
Janet Charbula on May 5, 2016 at 6:56 am said:

Thanks for all your help. I figured out a solution. I really appreciate it! I will visit the forums.
Ken Puls on May 10, 2016 at 10:17 am said:

Hey Ross,

Sorry for the late reply on this. See this post for an answer to your question: https://excelguru.ca/2016/04/28/pass-parameters-to-sql-queries/
Pingback: Power Query and the Abyss | brentpearce
Savin on July 2, 2016 at 10:44 am said:

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.
Ken Puls on July 2, 2016 at 2:33 pm said:

This may help: https://excelguru.ca/2016/04/28/pass-parameters-to-sql-queries/
Pingback: Quick Filter for Unique Values in Power Query and Power BI | Excel Inside
Martin on September 13, 2016 at 3:12 am said:

This was a big help! Thanks very much!
Jeff Garland on October 3, 2016 at 9:11 am said:

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.
Ken Puls on October 3, 2016 at 3:45 pm said:

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.
Quentin on November 28, 2016 at 2:27 am said:

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.
Details:
Value=(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")

Any thoughts please on how to resolve?

Thank you,
Q
Ken Puls on November 29, 2016 at 2:25 pm said:

Hey Quentin, can you take this one over to the forum at http://www.excelguru.ca/forums ? I think that's a better place to answer it as you can upload a workbook there so we can see exactly what is going on.
Steve Bergman on December 20, 2016 at 8:03 am said:

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:

let
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]))
in
#"Added Custom"
Ken Puls on December 20, 2016 at 8:18 am said:

Hey Steve,

Give this a read, as I think it will answer your question: https://excelguru.ca/2016/04/28/pass-parameters-to-sql-queries/
nale on February 16, 2017 at 6:25 am said:

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.
Ken Puls on February 16, 2017 at 9:35 am said:

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. http://www.excelguru.ca/forums
Ari on February 24, 2017 at 9:44 am said:

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!
Ken Puls on February 27, 2017 at 9:50 am said:

Hi Ari,

Can I get you to take this question to our forum at http://www.excelguru.ca/forums ? We can help you debug the finer points there, as there could be a few things.
Jon on March 28, 2017 at 1:50 am said:

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.

Thanks

Jon
Ken Puls on March 28, 2017 at 9:13 am said:

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.
Jon on March 28, 2017 at 12:12 pm said:

Thanks Ken. Hope PQ can handle it soon.
John Laub on April 27, 2017 at 6:42 pm said:

Thanks for taking the time to explain this, it was very helpful.
Rebekah Sax on May 1, 2017 at 12:08 pm said:

Thanks John, so glad to hear it was helpful!
Kees Kuip on June 1, 2017 at 12:55 am said:

Can't u use:

(ParameterName)=>
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
Value = ParamSource{[Parameter=ParameterName]}[Value]
in
Value
Ken Puls on June 3, 2017 at 7:51 am said:

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.)
Hans on September 13, 2017 at 6:35 pm said:

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?
Ken Puls on September 14, 2017 at 9:03 am said:

Hi Hans,

That's a new one to me, I'm honestly not sure as I've never used ODBC-SQLite, sorry!
Hans on September 15, 2017 at 11:33 am said:

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.
Greg on November 4, 2017 at 5:19 pm said:

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.
?
Ken Puls on November 6, 2017 at 10:22 am said:

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 http://www.excelguru.ca/forums and we can get it working for you.
Greg on November 8, 2017 at 8:35 pm said:

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.
Ken Puls on November 10, 2017 at 7:01 pm said:

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.
ADELSON SILVA on December 20, 2017 at 2:03 pm said:

Wow !!! Thanks friend Ken !!!
Fantastic solution
Alwyn Schutte on December 26, 2017 at 3:03 am said:

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:
let
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)
in
#"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.
Alwyn
Ken Puls on December 27, 2017 at 10:14 am said:

Hi Alwyn, can you post this in our forum at http://www.excelguru.ca/forums ? That way you can upload sample files so that we can walk through what's really going on. Thanks!
Rebekah Sax on January 29, 2018 at 2:01 pm said:

Thank you for reading, Adelson - glad you found the article helpful!
Mike Bayuk on February 2, 2018 at 10:54 pm said:

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

My code for Table 1 is:
let
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"))
in
#"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.
Ken Puls on February 5, 2018 at 8:23 am said:

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.
Ben on April 17, 2018 at 2:13 am said:

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:
let
sStartDate = fnGetParameter("StartDate"),
sEndDate = fnGetParameter("EndDate"),
Source = Sql.Database.....[Query=....WHERE T3DATE BETWEEN 'sStartDate' AND 'sEndDate' .....

fnGetParameters:
(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

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.
Ken Puls on April 19, 2018 at 9:16 am said:

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!
Frank on May 28, 2018 at 9:18 am said:

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.

C:\UserName'\FolderName
PowerBI_PracticeFile.pbix
C:\UserName\FolderName\DataFiles
DataFile1
DataFile2

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

C:\UserName'\NewFolderName
PowerBI_PracticeFile.pbix
C:\UserName\NewFolderName\DataFiles
DataFile1
DataFile2

Thanks in advance,
Frank
Dan Bliss on June 3, 2018 at 6:00 am said:

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:

https://useraccount-my.sharepoint.com/ 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:

C:\Users\Dan\OneDrive\MyFiles
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
Ken Puls on June 3, 2018 at 10:22 pm said:

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.
Dan Bliss on June 4, 2018 at 3:43 am said:

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?
Ken Puls on June 5, 2018 at 12:44 am said:

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.
Jan on June 9, 2018 at 3:00 am said:

Is it possible to do the same in Power BI?
Ken Puls on June 12, 2018 at 10:13 am said:

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.
Ken Puls on June 13, 2018 at 9:58 am said:

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.
Mike White on June 16, 2018 at 11:55 am said:

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("127.0.0.1,48002", "my_db_name",
[Query="#(lf)
#(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("127.0.0.1,48002", "my_db_name",
[Query="#(lf)
#(lf)DECLARE @Due_Date VARCHAR(15) = fnGetParameter("Due_Date")
#(lf)DECLARE @Company01 VARCHAR(50) = fnGetParameter("Company01")
#(
-------
Ken Puls on June 18, 2018 at 9:38 am said:

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.)
Mike White on June 19, 2018 at 5:47 am said:

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.
Ken Puls on June 19, 2018 at 8:59 am said:

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.
Tim Hoogenboom on June 29, 2018 at 11:34 am said:

=LEFT(CELL("filename",B6),FIND("[",CELL("filename",B6),1)-1) is BLANK until you save the workbook.
Ken Puls on June 29, 2018 at 1:26 pm said:

Very true, Tim!
Brian on October 9, 2018 at 6:39 am said:

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.
Ken Puls on October 9, 2018 at 8:24 am said:

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.
John Harp on December 18, 2018 at 7:54 am said:

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

"Guru" is appropriate.

Thx
Pingback: Creating Dynamic Parameters in Power Query - The Excelguru BlogThe Excelguru Blog
Alexander Karpychev on October 7, 2019 at 9:48 am said:

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?

Thanks.
Ken Puls on October 7, 2019 at 9:54 am said:

Unfortunately not. This is still the only way to make it happen. 🙁
Peter Rogers on October 28, 2019 at 5:38 pm said:

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...
Ken Puls on October 29, 2019 at 3:53 pm said:

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. 🙂
Bill Krolicki on January 25, 2020 at 11:46 am said:

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?
Ken Puls on January 27, 2020 at 9:42 am said:

Hi Bill,

You need to convert it to be a date:
Date.From(fnGetParameter("My Date"))
Pingback: Parameter Tables in Power BI / Power Query — Powered Solutions
Yohavst on February 11, 2020 at 2:43 am said:

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:
let
ListofValues = Text.Combine(table1[columnanyway],",")
Source = Sql.Database("Servername ", "DBname", [Query="SELECT * FROM dbo.table2 where Column1 in ("&ListofValues &")" ])
in
Source

I get the following error :
Expression.SyntaxError: Token Comma expected.
Ken Puls on February 11, 2020 at 8:43 am said:

Hi Yohavst,

Yes, you're missing a comma at the end of the ListofValues line. It should read:
ListofValues = Text.Combine(table1[columnanyway],","),
Yohavst on February 12, 2020 at 10:11 pm said:

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.
Ken Puls on February 13, 2020 at 8:57 am said:

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...
Yohavst on February 16, 2020 at 4:01 am said:

Hi Ken
I have already created blank query which transform a column into a string (comma separated) with the operator:
"Text.Combine(table1[columnanyway],",")"
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.
Jason Tan on March 28, 2020 at 7:44 am said:

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 "https://d.docs.live.net/......." 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.
Ken Puls on March 30, 2020 at 9:39 am said:

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 d.docs bit) has no Power Query connector. It's maddening!
Alicia on April 22, 2020 at 6:20 am said:

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: https://excelunplugged.com/2018/01/30/dynamic-calendar-with-power-query-or-power-bi-take-2/

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: https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

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.
Ken Puls on April 22, 2020 at 3:11 pm said:

Hi Alicia,

Try this one: https://excelguru.ca/2015/06/24/create-a-dynamic-calendar-table/

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 https://www.skillwave.training/shop/power-query-recipes/
Ellen on April 23, 2020 at 3:36 am said:

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
Alicia on April 24, 2020 at 6:48 am said:

Oh, good to know - thank you!
Ken Puls on April 24, 2020 at 8:40 am said:

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 https://www.excelguru.ca/monkeytools. 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.
Eric on July 6, 2020 at 5:48 am said:

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?

Thanks.
Ken Puls on July 7, 2020 at 8:55 am said:

Hi Eric,

For sure... the easiest way to solve it is probably to download an install my Monkey Tools add-in, as we can just inject a "Smart Folder" query right into your solution. (You can get a free trial at https://www.excelguru.ca/monkeytools)

Full steps on working with the SmartFolder function are covered starting on page 17 of the documentation guide as well. https://www.excelguru.ca/software/monkeytools/monkey_tools_features_guide.pdf
Andreas Müller on August 25, 2020 at 8:08 am said:

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.
Hagai Lerer on February 19, 2021 at 11:33 am said:

Thank Ken.
This was very helpful.
Tomasz Grudzinski on November 13, 2022 at 8:11 am said:

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?
Ken Puls on November 14, 2022 at 4:14 pm said:

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.
Pingback: Power Query “Folder Import” auf Basis einer Excel-Fileliste realisieren – Linearis :: Self-Service Business Intelligence
Tomasz Grudzinski on December 4, 2022 at 9:34 am said:

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.
Rohini Kasturi on January 24, 2023 at 6:40 am said:

Hi Ken,

When I click on invoke function I'm getting a null value. Not sure why ? I did exactly what you did.
Ken Puls on January 24, 2023 at 9:06 am said:

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.
Dan on June 7, 2023 at 10:16 am said:

I tried this process on a new workbook, using the fnGetParameter as a connection only blank query then adding a new query from a folder and I continue to receive an error when attempting to combine new files. The error indicates an Excel tab named "Parameters" could not be found. Any help would be appreciated.
Ken Puls on June 7, 2023 at 10:20 am said:

Hi Dan,

Check that table name. There are points for both spelling and casing. It must be exactly "Parameters" (without the quotes).

Alternately, if you want to make it really easy on yourself, download an install a copy of my Monkey Tools add-in. The free version contains a way to insert both the table and the function in a click in order to get them exactly right and save you time.
Dan on June 7, 2023 at 11:20 am said:

It doesn't appear to work either way. The errors seem to be looking for the Parameters table in the sample file. I copied the logic from another workbook and check the names are exactly the same. Is there anything else that I should look for?
Ken Puls on June 7, 2023 at 11:30 am said:

Hard to say without seeing the file. The usual suspects are that the table must exist and have the same name as what is declared inside the Power Query function. (Monkey Tools uses a slightly different table name than Parameters, but the injection should take care of that.) If you are copying in from another file... any number of things can go wrong there. Probably the best thing to do is post in the Power Query forum at https://forums.excelguru.ca/forums/power-query-get-transform-m-code.19/. If you can include a copy of the workbook, that is best, but if not, I would suggest providing the M code for your queries.
Dan on June 7, 2023 at 11:36 am said:

Should the power query already exist prior to inserting the file path M code at the beginning or should this work on a blank query. I see that in my previous workbook I had inserted it in after the fact.
Gaston Huot on June 27, 2023 at 8:28 am said:

It is really a fantastic solution!. Just too sad that we have this issue with the "firewall" matter.

I don't really understand how to avoid this problem and be able to enjoy the function.
Ken Puls on June 27, 2023 at 1:18 pm said:

Hi Gaston,

For debugging specific issues, I recommend posting in our Power Query forum at https://forums.excelguru.ca/forums/power-query-get-transform-m-code.19/ (You may also find this useful: https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/)
Peter on January 9, 2024 at 12:49 am said:

Hi Ken, out of interest, is there any difference in overhead between putting the parameter value in a variable, so that it is retrieved just once, or including the dynamic reference to the parameter value?

I am thinking specifically of a step such as this:
#"Next Step" = Table.ReplaceValue(#"Previous Step", null, Table.SelectRows(#"Parameter Table", each [Parameter] = "Terms of Payment Key"){0}[Value], Replacer.ReplaceValue, {"Field Name"})

Is it possible that the Table.SelectRows bit is being evaluated many times due to the way it is being used in the Table.ReplaceValue function?
Table.SelectRows(#"Parameter Table", each [Parameter] = "Terms of Payment Key"){0}[Value]
Ken Puls on January 9, 2024 at 11:17 am said:

Hi Peter,

It really depends on how you call your function. If you are just using fnGetParameter to make a single call to get a path the way I have done, then Power Query should only need to execute it once. It will build its query plan at runtime, then execute it. If you call fnGetParameter from each row of a table (say via Invoking a function on a column), then it would need to execute it for each row.

Will it be slower to read the parameter from the Excel worksheet than using a simple query to hold a static value? Yes, as it does have to read/execute the steps from a data source. But going the "variable" route of using a query to hold the value results in making a static query that needs to be updated. The benefit of the fnGetParameter function is that it allows you to read values that change dynamically in the workbook without having to modify the queries in any way at runtime.
Peter on January 10, 2024 at 1:59 am said:

Thanks Ken! Appreciate the help.
Pingback: Power Query Errors: Please Rebuild This Data Combination
Pingback: Sharing Power Query Solutions
Pingback: Native Database Query Security in Power Query
Pingback: Creating Dynamic Database Queries in Power Query