Power Query in excel- stored in SharePoint online/One drive. Data wont refresh when its is closed.

[skipper]

I am having a problem having query data coming to Excel. It is API data flowing to Excel. Using power Query data is getting loaded to excel. 

I have to manually open the file to get this data refresh.

I have tried Refreshing it using Microsoft power Automate but it did not work.

i have tried schedule refresh in excel and it's not working.

How do I refresh data in excel without opening it?

This data gets updated to the Power BI dataset. Not using PQ for the dataset directly because I cannot hold historical data.

[v-jingzhang]

Hi @skipper 

When you refresh the queries in Excel's Power Query, it will need to launch Microsoft Mashup Evaluation Container to execute the refresh operation. It is running within the Excel application. If you don't open the Excel file, the whole Excel application is not running thus it cannot launch the Mashup Evaluation Contianer to execute the refresh.

If you want to hold the historical data in Excel workbooks, is it possible to export the data from API into Excel workbooks automatically rather than use Power Query in Excel to query the data? Then you can use Power Query in Power BI Desktop to connect to these Excel workbooks directly. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[skipper]

"export the data from API into Excel workbooks automatically rather than use Power Query in Excel to query the data?"

how do I export data from API to excel without using Power Query?

[amitchandak]

@skipper , have you already tried these steps

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive