Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Excel Power Query Auto update with Azure Devops OData

Hi,

 

Short version:

How can I schedule and make sure that my excel sheet with auto update keeps on updating daily?

 

Longer version:

I am having some issues with using Power Query in Excel. In particular, with the use of OData from Azure Devops, and with auto refreshes:

  • For auto refreshes, it appears they are simply not made despite me having turned on background updates and Update every x minutes. Is there any other requirements or other ways to schedule updates? In essence, I need refreshes daily or twice a day.
  • For OData, even though I am logged in to microsoft and using the correct authentication, sometimes the Power Query bugs out and says that the provided legitimation is not valid. How I fix it is to go into the transform data/power query window, go to settings, switch to organizational account (which of course gives an error) and then switch back and it works again.

For reference, here is the config I have on my connections:

 

 

Thanks in advance for any help,

Jens

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    According to your description, first of all, you mentioned to set up timed refresh for the data source, if you want to refresh according to once a day or twice a day, you can set it to refresh every 1440 minutes or 720 minutes. Also to make sure the refresh doesn't get locked you need to enable the background refresh option.
    Regarding authentication, you can delete your original credentials in the data source settings in Get Data and reconfigure the credentials using another authentication method. It's not possible to determine what your error message is, so you can check the following documentation to correspond to your error message

    Power Query OData Feed connector - Power Query | Microsoft Learn
    Authentication in desktop apps - Power Query | Microsoft Learn
    Handling authentication for Power Query connectors - Power Query | Microsoft Learn

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      Thanks for the response. The screenshot I added at the bottom is in Norwegian, but "Aktiver bakgrunnsoppdatering" is enabling backgroun refresh and "Oppdater hvert 720 minutt" means it is set to updating every 720 minutes as per your suggestion. However, no updates occur even when I am gone for the document for 24 hours regardless if I have the excel file open or not.

       

      As for the point of the exact error message, I will try to reproduce it, but it does usually work, it is just after a few days it seems like it bugs out.