none
Is there a quick way to change parameters within a query?

    Question

  • I currently have a spreadsheet set up with 8 different queries. The structure of these queries is identical, and the only thing that changes between them is a few parameter values (time of day that I'm filtering  my data on). My question: if I want to make changes to the parameter values in this queries (say, use a different day of the week, where it has to be changed for all 8 queries), is there a quicker way to do this than by going into each query and manually setting the new value? I guess I'm asking if within Power Query, is there a way for the query to reference a value that is defined somewhere else in the workbook or sheet?

    Friday, June 06, 2014 1:36 PM

Answers

  • You can't reference any Power Query code from within your PostreSQL, if that's what you mean. The native SQL capability is a relatively advanced feature, and largely requires that you know the dialect of SQL used by your server. That said, there are basically two different ways to parameterize a native query:

    1. Always fetch the entire data you might be interested in but then do filtering locally. This isn't always possible, and may result in downloading more data than is practical.

    2. Use string concatenation to build a SQL query dynamically and pass that as the Query parameter to PostgreSQL.Database instead of fixed text. This has the disadvantage I mentioned above, that the user will be prompted once for each unique string being executed.

    • Marked as answer by KnnNike Thursday, July 10, 2014 12:56 PM
    Wednesday, July 09, 2014 8:43 PM
    Moderator

All replies

    • Proposed as answer by Curt HagenlocherModerator Friday, June 06, 2014 1:49 PM
    • Marked as answer by KnnNike Friday, June 06, 2014 5:42 PM
    • Unmarked as answer by KnnNike Wednesday, July 09, 2014 10:23 AM
    Friday, June 06, 2014 1:49 PM
    Moderator
  • Ah, this is what I'm looking for, thanks!
    Friday, June 06, 2014 5:43 PM
  • Ok, so this was a good starting point for me...however, to get a little bit better understanding, I ended up referring to this site: http://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/

    The problem I have now is syntax. Most of the examples I have seen use SQL, and my queries are set up to pull data from PostgreSQL databases. Following the example in the website above results in syntax errors, and I don't know Postgr eSQL well enough to fix them on my own. Here is the start of my current query:

    create temp table cmd_temptable0 as
    (select event_timestamp, event_code_id, event_param from signal_controller_events.controller_event_log
     where signal_id = 4432
     and (extract(hour from event_timestamp) >= '09' and extract(hour from event_timestamp) < '15');

    I would like to parameterize the signal_id value (and I have successfully set up a query to pull this value from an Excel table called "Parameter", but according to the above example, it would be look something like:

    Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
    SignalID_Value = Parameter{0}[value],

    create temp table cmd_temptable0 as
    (select event_timestamp, event_code_id, event_param from signal_controller_events.controller_event_log
     where signal_id = SignalID_Value
     and (extract(hour from event_timestamp) >= '09' and extract(hour from event_timestamp) < '15');

    However, this just gives me syntax errors. Does anyone know the proper PostgreSQL syntax for getting this to work?




    • Edited by KnnNike Wednesday, July 09, 2014 10:34 AM
    Wednesday, July 09, 2014 10:34 AM
  • You shouldn't need to write PostgreSQL syntax directly to do this. In fact, doing so is an antipattern because it would require you to use the "native SQL" feature which will prompt you for every new query text that is generated.

    I see nothing in the oraylis.de blog entry that shouldn't work for Postgres; no embedded native SQL. What code did you end up with that didn't work, and what error did it give you?

    Wednesday, July 09, 2014 12:20 PM
    Moderator
  • So, unfortunately, my actual PostgreSQL query is pretty long and complex (what I pasted above is only maybe the first 5%); because of this, I am using the "native PostgreSQL" feature, to borrow your terminology.

    Is there a way to reference another query within a native PostgreSQL environment? Or are you saying that this will not work?

    Wednesday, July 09, 2014 5:52 PM
  • You can't reference any Power Query code from within your PostreSQL, if that's what you mean. The native SQL capability is a relatively advanced feature, and largely requires that you know the dialect of SQL used by your server. That said, there are basically two different ways to parameterize a native query:

    1. Always fetch the entire data you might be interested in but then do filtering locally. This isn't always possible, and may result in downloading more data than is practical.

    2. Use string concatenation to build a SQL query dynamically and pass that as the Query parameter to PostgreSQL.Database instead of fixed text. This has the disadvantage I mentioned above, that the user will be prompted once for each unique string being executed.

    • Marked as answer by KnnNike Thursday, July 10, 2014 12:56 PM
    Wednesday, July 09, 2014 8:43 PM
    Moderator
  • Thanks, Curt. This is disappointing to hear, but I understand why it is the case. Option #1 is definitely out for me, since the records I'm working with number in the tens of thousands. I'll play around with Option #2 and see if I can get something at least semi-functional put together.
    Thursday, July 10, 2014 12:56 PM