none
Not all Queries are refreshed on "Refresh All" RRS feed

  • Question

  • Hello friends,

    I am working with the same Queries / Model for several months

    Suddenly when I click "Refresh All" - I see that not all the Queries are refreshed.

    I needed to right-click one of them - then "Refresh" for it to refresh.

    Has anyone experienced this?

    Thanks

    Michael


    Michael

    Thursday, February 1, 2018 10:34 AM

Answers

  • Hi Michael,

    Thanks for reporting this issue.

    I have few questions:

    1. Does this issue happen if the queries are NOT checked for 'Fast Data Load'?

    2. Does this issue happen if the problematic queries are not 'Connection Only', but loaded to an Excel table for example?

    3. Is it possible to share the problematic workbook with us, stripped from all confidential information?

    4. What Excel 2016 Pro version are you using?

    Thanks,

    Dan

    Tuesday, February 13, 2018 12:56 PM

All replies

  • Hi Michael. What version of Excel/PQ are you using?

    Ehren

    Friday, February 2, 2018 12:37 AM
    Owner
  • Excel 2010 with Add-in Version: 2.44.4675.281 64-bit

    But the same thing happens when my client refreshes my file using Excel 2016 Pro

    Please help!

    Thanks

    Michael


    Michael

    Saturday, February 3, 2018 10:16 PM
  • Ok. What data source is being accessed by the query that you have to manually refresh? Is it accessing data from the current Excel workbook?

    Ehren

    Monday, February 5, 2018 6:19 PM
    Owner
  • There are many different data sources: some are API's, some are Excel files in OneDrive for Business. The one that is not always refreshing is actually an Append of two queries - Web API and Excel from OneDrive for Business. Refresh All returns no errors, that's most annoying - users are working with partial data without realizing something wrong. When I rightclick the query and manually refresh it - it updates without any problem. If it is relevant - the underlying queries of this Append are marked as "Connection only" and all the queries are checked for Fast Load. Thank you Michael

    Michael

    Wednesday, February 7, 2018 7:40 PM
  • Hi Michael,

    Thanks for reporting this issue.

    I have few questions:

    1. Does this issue happen if the queries are NOT checked for 'Fast Data Load'?

    2. Does this issue happen if the problematic queries are not 'Connection Only', but loaded to an Excel table for example?

    3. Is it possible to share the problematic workbook with us, stripped from all confidential information?

    4. What Excel 2016 Pro version are you using?

    Thanks,

    Dan

    Tuesday, February 13, 2018 12:56 PM
  • Thanks Dan,

    Unfortunately - it'll take too much time for me to reproduce the workbook without confidential info...

    Right now my client is very busy so I could check theirs Excel version only in a week or so.

    I'll update here

    Thank you very much for trying to help!

    Michael


    Michael

    Wednesday, March 7, 2018 10:33 AM
  • Hi Dan,

    a client is experiencing the same problem. Refresh All of the workbook works fine on my machine but not on his: 

    - Microsoft Office Professional Plus 2016

    - Version 1809 (Build 10827.20181 Klick-und-Los) -- German version here…

    - Monthly Chanel

    Cannot share workbook unfortunately.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, November 12, 2018 6:19 PM
    Moderator
  • Hi Imke. I've pinged Dan about this and given him your email address.

    Ehren

    Monday, December 3, 2018 7:19 PM
    Owner
  • Hello, although this is a few months old thread, I am experiencing the above problems.

    I am using Office 365 on Windows 10, all updated until today. Excel version is 1904 (Build 11601.20178 Click-to-Run) Monthly Channel.

    I have a self contained 350k xlsm file, with about 10 tables. I use PowerQuery to load 3 of the tables, 1 master table and 2 transaction tables. I append the 2 transaction tables to a new Query (Q_APPEND1) and then create my last Query (Q_MERGE1) where I join the Master table to the Q_APPEND1. This last Merge Query is the only one that is brought back to a worksheet table, all the others are Connection only. FastLoad is off for all 5.

    I then use this Q_MERGE1 table to produce my pivot reports.

    Ctrl-Alt-F5 / RefreshAll has no effect whatsoever on the Q_MERGE1 table and Pivot Data in the workbook, whenever I add lines to the 2 transaction tables. It is only after I SAVE the workbook (Ctrl-S) without needing to close it, that the Refresh takes place.

    Any ideas??


    George Thalassinos


    Wednesday, May 15, 2019 11:22 AM
  • Hi George,

    1. Are you using Excel.Workbook to access the contents of the current workbook, or Excel.CurrentWorkbook?
    2. If you're using the latter, are you doing Excel.CurrentWorkbook to access any tables that are backed by PQ queries?

    Ehren

    Wednesday, May 15, 2019 4:56 PM
    Owner
  • Hi Ehren,

    I do apologize for the delay as I must have deleted your answer by mistake on my desktop and found it on my phone during a routine checkup.

    Due to the fact that this workbook will be broken up in 3 or 4 final workbooks, in order to manage user authorizations and discrete code and data files, I use the following method for each master table I load :


    let
        WBFilePath = Excel.CurrentWorkbook(){[Name="WorkBookFilePath"]}[Content]{0}[Column1],
        Source = Excel.Workbook(File.Contents(WBFilePath & "\" & "HIPCONTROL.xlsm"), null, true),
        T_HIPPERS_Table = Source{[Item="T_HIPPERS",Kind="Table"]}[Data]
    in
        T_HIPPERS_Table

    I have found this to work regardless of whether I am developing this at home or it is in the actual production computer (not same subfolders necessarily).

    So, at this time, there is only one workbook, completely self contained.

    Could the problem arise from the fact that PQ is trying to open a second instance of my file that does not contain the new data BEFORE I save it as they are still in memory and not on disk? Now that I think of it it sounds almost definite to me.

    Excel.CurrentWorkbook will read the memory tables and Excel.Workbook will try to load them from disk not finding any differences. Once I commit the changes by saving the file, all new data are read back by Excel.Workbook, thus appearing in the Append and Merge Queries.

    Therefore I should go back to using Excel.CurrentWorkbook until I actually split the Workbook and use the Excel.Workbook reference only for the master tables residing in external workbooks.

    Right?

    George Thalassinos




    Saturday, May 18, 2019 5:45 AM
  • Excel.Workbook reads the file from disk, so it will only see the most recent changes after the file is saved.

    Since there's only one file, you should be able to work around this by using Excel.CurrentWorkbook to read the T_HIPPERS table (and any other tables currently being read via Excel.Workbook).

    Ehren


    Monday, May 20, 2019 5:25 PM
    Owner