• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Duplicate vs Reference Query -- Difference?|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Duplicate vs Reference Query -- Difference?|Power Query|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryDuplicate vs Reference Query -- Dif…
sp_PrintTopic sp_TopicIcon
Duplicate vs Reference Query -- Difference?
Avatar
DigaloGuapo
Member
Members
Level 0
Forum Posts: 23
Member Since:
February 23, 2017
sp_UserOfflineSmall Offline
1
March 11, 2017 - 9:00 am
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4371
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 11, 2017 - 8:19 pm
sp_Permalink sp_Print

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

Avatar
DigaloGuapo
Member
Members
Level 0
Forum Posts: 23
Member Since:
February 23, 2017
sp_UserOfflineSmall Offline
3
March 12, 2017 - 8:11 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
March 12, 2017 - 4:37 pm
sp_Permalink sp_Print

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.

Avatar
DigaloGuapo
Member
Members
Level 0
Forum Posts: 23
Member Since:
February 23, 2017
sp_UserOfflineSmall Offline
5
March 14, 2017 - 12:21 pm
sp_Permalink sp_Print

Thanks Catalin. Sometimes my ideas get ahead of what's actually possible.

i appreciate your feedback

Avatar
Nathan Watkins

New Member
Members
Level 0
Forum Posts: 2
Member Since:
June 24, 2018
sp_UserOfflineSmall Offline
6
June 24, 2018 - 12:07 pm
sp_Permalink sp_Print

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?

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Dieneba NDIAYE
Guest(s) 106
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Richard Adams
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31571
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.