Results 1 to 7 of 7

Thread: Power Query / Power Pivot Limitations?

  1. #1
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365

    Power Query / Power Pivot Limitations?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm just checking...

    I have a query that takes a long time to reach a certain stage of completion. From there, I'd like to transform and save it as two completely different tables in Power Pivot. It appears, however, that...

    1. I can't park my initial results in a Power Pivot table for use by other queries.
    2. I can't create two tables from one Power Query query.

    Therefore, it appears that I have only two choices...

    A. Save my initial results in a worksheet so that two other queries could summarize that preliminary data in two different ways for use in Power Pivot.
    B. Create two completely separate queries that import the same data twice.

    Am I correct here? Or am I missing something?

  2. #2
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    Never mind. I can simply load the first-stage query as a connection-only, and then reference that query in any number of downstream queries.

  3. #3
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    Nope, that doesn't work. Referencing a query doesn't reference a cache of the query's results; it merely calls the query. So if I reference the query ten times, it apparently runs ten times.

    So, apparently, the only way to cache a query's results is to write them to a worksheet.

    Or am I STILL missing something?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Charley, what version of Excel are you running? 2016 or Office 365? If it's the former, get on the latter, as this has been fixed in version 1801, build 9001 and higher (See https://excel.uservoice.com/forums/3...tions/16852819) Prior to that build, yes, you'll be re-loading the same query every time it is called.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    Ken,

    I'm on 365 with monthly updates. I'm currently on Version 1811, build 11029.20079. I don't know what version I was running at the time of my last post.

    What was supposed to have been fixed? Are you saying that if ten queries each calls the same connection-only query during Refresh All, that the called query is supposed to run only one time?

    Thanks, Ken!

    Charley

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    It's called the caching of shared nodes. So basically, the first load should call a full refresh, but the other 9 queries should be able to refer to that child query's results.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    Thanks, Ken.

Tags for this Thread

Posting Permissions

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