locked
Power Query General Question : How it refresh all the linked data RRS feed

  • Question

  • Hi All,

    I have around 10 power query output tables. some of them linked to direct excel files and sheets in local desktop , some linked to files exist in particular folders. after wards some of files I loaded only as connection and Pivot tables also created.

    Now my query is I want to refresh all the Power query table and currently using "Refresh All" option under Data tab.

    And afterward I noticed all the power query tables not refreshing....

    1) Can any body tell me what is the best way to do to refresh all power query table at once ?

    2) Also want to know what are the best practices if power query taking too much time to refresh all the tables ?

    3) Sometime When I clicked on Refresh All option under data tab, I am getting error message "invalid reference". Can anybody tell me also How to trouble shoot the same, from which Power query table or Pivot table this error is linked ?

    Regards,

    Rajender

    Regards,

    Rajender


    Rajender

    Tuesday, March 12, 2019 6:15 PM

Answers

  • "Refresh All" in the PQ Editor window only refreshes the previews that appear when you click on any given query or step in the PQ Editor. "Refresh All" in the Data tab actually triggers a re-load of all the data into Excel worksheets and the Excel Data Model.

    Which one were you clicking on when seeing the issue you described above (the data not updating as expected)?

    Ehren

    • Marked as answer by AskQuery1984 Tuesday, March 26, 2019 7:00 AM
    Monday, March 25, 2019 9:39 PM

All replies

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

    Ehren

    Thursday, March 14, 2019 9:32 PM
  • Hi Ehren,

    I am using Office 365 professional Plus. version 1803. Windows 7 16GB RAM.

    Also one more thing I noticed. When I am running one of my Power queries in my above mentioned configuration it takes apporx. 2-3 min to refresh and fetch the data from SharePoint files.

    And when the same Power query refreshed by my team member in Windows 10 (office 365 professional Plus version 1803) then it takes 15 min to refresh the same query. Could you please suggestion what can be the possible reasons for this delay in power query refresh ?

    Thanks once again for your valuable feedback. 

     



    Rajender

    Thursday, March 21, 2019 7:14 AM
  • Hi Rajender. Are these on-prem SharePoint files, or cloud?

    Ehren

    Thursday, March 21, 2019 11:31 PM
  • Hi Ehren,

    I asked to my IT team will get back to you. Also want to know if I can check myself for How to check if its on-prem or Cloud ?


    Rajender

    Saturday, March 23, 2019 12:15 PM
  • Hi Ehren,

    IT's Cloud base.


    Rajender

    Monday, March 25, 2019 7:40 PM
  • There was a bug recently that caused long delays when connecting to SharePoint Online and entering credentials for the first time. Can you ask your associate to try again and see if things work more quickly?

    Ehren

    Monday, March 25, 2019 7:43 PM
  • Hi Ehren,

    May be this is the possible reason. while trying to connect SharePoint site with Power Query it asking for login and showing incorrect password error as well , but where as when directly opening the SharePoint sites it was working fine.

    I will check it out once again and get back to you. 

    Also regarding my above query What is the difference between "Refresh All" option showing in Power Query Window and "Refresh All " under data tab ? 

    if in both cases it refresh All the data from Multiple and different sources at once ?


    Rajender

    Monday, March 25, 2019 9:03 PM
  • "Refresh All" in the PQ Editor window only refreshes the previews that appear when you click on any given query or step in the PQ Editor. "Refresh All" in the Data tab actually triggers a re-load of all the data into Excel worksheets and the Excel Data Model.

    Which one were you clicking on when seeing the issue you described above (the data not updating as expected)?

    Ehren

    • Marked as answer by AskQuery1984 Tuesday, March 26, 2019 7:00 AM
    Monday, March 25, 2019 9:39 PM
  • Thank you so much Ehren for clearing my doubt.

    Rajender

    Tuesday, March 26, 2019 7:00 AM