locked
Best way to scale queries? RRS feed

  • Question

  • So I have a query that I need to run multiple times in my spreadsheet with slightly different parameter values each time. I know that I do a second query to dynamically change the parameter values, but can I use the same method for actually building the query text itself?

    The reason I ask is because I got to thinking, "What if I need to make a structural change to the query?" Rather than have to go back and change the same query in 8 different sheets, I would like to have the query text in a central spot that I can just grab each time I need to run the query.

    I hope this makes sense...

    Wednesday, June 11, 2014 9:10 PM

Answers

  • For the specific requirement that you have, you could build one query and turn that into a function, with a parameter built in that requires a file location.  Then have eight other queries that invoke that function and each one parses through the location of the eight sheets that you have.  Your function would then only need to be changed to affect the eight other queries.

    There are a few Power Query function blogs around the web that are worth looking at, especially (as it seems) you are looking to use Power Query to its fullest.  Here is one to get you started from Chris Webb's blog (with plenty more articles on his web site as well!)


    Regards

    Thursday, June 12, 2014 2:59 PM

All replies

  • For the specific requirement that you have, you could build one query and turn that into a function, with a parameter built in that requires a file location.  Then have eight other queries that invoke that function and each one parses through the location of the eight sheets that you have.  Your function would then only need to be changed to affect the eight other queries.

    There are a few Power Query function blogs around the web that are worth looking at, especially (as it seems) you are looking to use Power Query to its fullest.  Here is one to get you started from Chris Webb's blog (with plenty more articles on his web site as well!)


    Regards

    Thursday, June 12, 2014 2:59 PM
  • That's a great answer Laurence. And while this won't help you immediately, we're working on ways to make this experience MUCH simpler. (Though what you write today should keep working in the future too.)

    PS KnnNike, more than 10% of our team is made up of Purdue grads! :)

    Thursday, June 12, 2014 6:38 PM