+ Reply to Thread
Results 1 to 2 of 2

Multiple worksheets using the same power query getting hard to manage

  1. #1
    Registered User
    Join Date
    New York
    MS-Off Ver
    Excel 2016

    Multiple worksheets using the same power query getting hard to manage

    I'm using power query to join csv files in workbooks. For example each workbook pulls a key of customer information which is then used to match up with data used in different types of other queries.

    Right now I'm just copy pasting the customer data query into a new workbook but its getting time consuming that every time a small change to the customer query needs to happen, I have to go into all the workbooks and modify it separately.

    Question is does Office have some place I can centrally have queries that each workbook references so if a change needs to occur I can just change the query that one time?

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver

    Re: Multiple worksheets using the same power query getting hard to manage

    I am not familiar with power query but I am familiar with the command string. If power query makes the same use of the command string as other queries do, then you can substitute a concatenated string into the command string.

    So it would work like this. In the workbook you will have a small VB program that can either run on open, or manually. This program goes out and finds the "gold" workbook on the network and gets the command string from it and substitutes it into the command string in the workbook that needs updating.

    I use the following bit of code to make dynamic queries in Oracle. I have an excel table with the "raw" SQL in one column, containing things like '&DATE' and a series of substitution commands in the next column over (translated SQL) so I can pass parameters to the command string. The function, SuperCat concatenates the translated SQL into a cell that I normally give a name like SQL_Data. I call SubSQL like so: SubSQl "qry_name", "SQL_Data". This concept may work for you too.

    Please Login or Register  to view this content.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  2. [SOLVED] power query
    By juanda92 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2016, 08:56 AM
  3. Can I use Power Query to pull from Pivots in multiple workbooks?
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2016, 10:24 AM
  4. VBA and Power Query
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-16-2015, 05:10 AM
  5. Using Power Query to do multiple searches on a website
    By eodsolo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-11-2014, 09:33 PM
  6. [SOLVED] How to manage tables in a query?
    By jgomez in forum Access Tables & Databases
    Replies: 6
    Last Post: 06-20-2012, 05:15 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1