I'm trying to figure out the difference between duplicating and referencing a query. but I guess i should start with what I'm trying to do.
I'm using Blockspring to extract data from various APIs into Excel. The Blockspring queries have parameters that can be manipulated to change the data. So when I'm getting data from the same source, I'm trying to build a table that will automatically update, appending new data with each new query. I'm trying to use Power Query to accomplish this.
Being the novice that I am, the only solution I have now is rather complicated.
1. Each BS query returns data in an array that can't be modified. However, I can get around this by creating a named range that captures the array. For example, if the data retrieved is contained in B2:H30, I'll create a named range called BlockspringData for A1:I50
2. I can create a PQ query to take the data from the named range and add it to a new table.
3. Now here's why I'm asking the original question.... for every new Blockspring query, I want to add this data to PQ. I want to know the best way to do this, by referencing or duplicating the query?
I've tried duplicating the queries which does kinda work. It creates a new query each time and I'm assume I could append them all together at some point. But I'd prefer this to be automated as much as possible, where the data is automatically appended to the table.
All ideas would be gladly welcomed. Thanks!
Hi Digalo,
I'm not clear on how you're getting your data, whether it's going into multiple sheets in the file or multiple files etc. An example file would help.
That said, if you refernce a query then the new query will pick up any changes to the original query. If you make changes to the steps in the original query then they will flow through. Whereas a duplicated query stands alone. It's similar to copying an Excel workbook. Changes made in the original workbook don't affect the duplicated workbook, so to speak.
Mynda
Hi Mynda,
Thanks for responding. I'm attaching an excel file that will hopefully make my question more clear.
In the attachment, The 'Blockspring Data' worksheet contains the information that I'm retrieving. I've creating a named range called 'GetBSData' to surround all of capture all of the Blockspring data and add it to Power Query.
'BlockspringQuery' retrieves the data and adds it to a new table in the 'PQ Results' worksheet.
My original questions pertains to the 'Blockspring Data' worksheet. I can modify this information very easily by changing parameters D5 & D6. Ideally what I'd like to do, is to append this data to 'PQ Results' table each time there is a new Blockspring query. I've not figured out if this is even possible with Power Query. The only partial success I've had is to create new tables by duplicating the query and merging them all.
I was just wondering if referencing the query would bring me closer to what I want.
Hi Digalo,
Power Query will not work like that. Even if you duplicate the Query and the duplicated query will return the results in a different table, if you simply refresh the first query, it will update with the same results as the second query.
There is no way to "always append" instead of updating the query. I understand that you want power query to keep old data but also add new data in the same query whenever you change the data. Not possible.
A workaround is to keep the file with your blockspring data in a folder, duplicating the file for each new parameters. This way you will have a file for each new set of data.
In a new file, you will be able to add a query to append data from all files in that folder, with a single query.
Thanks Catalin. Sometimes my ideas get ahead of what's actually possible.
i appreciate your feedback
Could you load all of the data that you’ve already received to an Excel table file (say “HistoricalData”), then make a query that uses that aggregated table as the source, and append the new data to that query?
