On-prem SQL to Excel on OneDrive to Power BI

[SarahVenk]

Hii,

I have an excel file that uses Power Query to connect to the On-prem SQL server. This excel file is stored on OneDrive for Business.
Power BI is able to connect to this excel file on OneDrive. How can I refresh this Power BI such that its data source - excel file's power query is also refreshed? In other words, how can I refresh data from On-prem SQL and show it in my Power BI report with excel online in between? Thanks!

[v-yadongf-msft · Accepted solution]

Hi @SarahVenk ,

If you created your datasets and reports based on a Power BI Desktop file, Excel workbook, or comma separated value (.csv) file on OneDrive or SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh. 

1.Unlike a dataset refresh during which Power BI imports data from a data source into a dataset, OneDrive refresh synchronizes datasets and reports with their source files. By default, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization.

2.The dataset settings page only shows the OneDrive Credentials and OneDrive refresh sections if the dataset is connected to a file in OneDrive or SharePoint Online, as in the following screenshot. Datasets that aren't connected to sources file in OneDrive or SharePoint Online don't show these sections.

3.Keep in mind that OneDrive refresh doesn't pull data from the original data sources. OneDrive refresh simply updates the resources in Power BI with the metadata and data from the .pbix, .xlsx, or .csv file, as the following diagram illustrates. To ensure that the dataset has the most recent data from the data sources, Power BI also triggers a data refresh as part of an on-demand refresh. You can verify this in the Refresh history if you switch to the Scheduled tab.

For more information, please refer to:Data refresh in Power BI - Power BI | Microsoft Learn

Best regards,

Yadong Fang

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