Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JensEilertsen
Regular Visitor

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:

JensEilertsen_0-1722939243418.png

 

 

Thanks in advance for any help,

Jens

2 REPLIES 2
v-heq-msft
Community Support
Community Support

Hi @JensEilertsen ,

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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors