Forum Discussion
Great Power Bi Report - but can't access the spreadsheet data Real time lets it down
Hi,
Got to grips with power Bi and published a report. The report has a table visual, which gets it's data from a Excel Spreadsheet on a windows shared drive (i access it via UNC path)
The Spreadsheet is updated occasionally manually who add additonal records to it. However, when they then look at the report, it doesn't have the latest data in it.
I then arranged for a data connection/gateway to be setup to link to the excel spreadsheet and have put in a couple of manual refreshes, but it's not ideal.
Is there any way of getting the report to get the latest data from the spreadsheer when it is opened?
Thanks
Hi Rarebit - In Power BI, automatically pulling the latest data from an Excel file on a shared drive in near real-time can be challenging, especially if the data is on a network drive accessed via a UNC path.
Setting up a scheduled refresh through Power BI’s data gateway is generally the most reliable way to keep your data up-to-date. Even though it’s not fully real-time, you can set it to refresh frequently (up to 8 times per day for Power BI Pro, or more with Premium). To ensure this works smoothly:
- Make sure the data gateway is configured correctly and has access to the shared drive.
- Set up the scheduled refresh intervals based on when updates are typically made to the Excel file.
- Ensure that the users are aware of the scheduled refresh times so they know when the latest data will be available in the report.
-
Switch to OneDrive or SharePoint: Power BI has native integration with OneDrive and SharePoint, allowing it to detect changes in real-time. If you can migrate the Excel file to OneDrive or SharePoint, Power BI will automatically refresh the data roughly every hour. This setup doesn’t require a gateway and provides a more seamless way to keep data current:
- Upload the Excel file to OneDrive or SharePoint.
- Connect Power BI to the file using the OneDrive or SharePoint connector, and Power BI will refresh the dataset hourly without needing manual intervention.
-
Direct Query (Alternative Approach): If you’re dealing with data that needs real-time or near real-time access, consider moving the data to a database that supports DirectQuery (e.g., SQL Server, Azure SQL Database). DirectQuery allows Power BI to fetch the latest data every time a user interacts with the report, though it’s limited to databases and doesn’t support Excel files.
-
Power Automate for Triggered Refreshes: If you’re using Power BI Premium or Power BI Pro with certain configurations, you can set up a Power Automate flow that triggers a refresh of the Power BI dataset when the Excel file is updated. To set this up:
- Create a Power Automate flow that monitors changes to the Excel file.
- Use the Power BI “Refresh a dataset” action in Power Automate to refresh the dataset when a change is detected.
-
If real-time updates aren’t possible, you can set up refresh notifications for users. In Power BI Service, users can subscribe to the report and receive an email notification when the report is refreshed. This helps ensure that users know when they’re viewing the latest data.
Data refresh in Power BI - Power BI | Microsoft Learn
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive
1 Reply
Hi Rarebit - In Power BI, automatically pulling the latest data from an Excel file on a shared drive in near real-time can be challenging, especially if the data is on a network drive accessed via a UNC path.
Setting up a scheduled refresh through Power BI’s data gateway is generally the most reliable way to keep your data up-to-date. Even though it’s not fully real-time, you can set it to refresh frequently (up to 8 times per day for Power BI Pro, or more with Premium). To ensure this works smoothly:
- Make sure the data gateway is configured correctly and has access to the shared drive.
- Set up the scheduled refresh intervals based on when updates are typically made to the Excel file.
- Ensure that the users are aware of the scheduled refresh times so they know when the latest data will be available in the report.
-
Switch to OneDrive or SharePoint: Power BI has native integration with OneDrive and SharePoint, allowing it to detect changes in real-time. If you can migrate the Excel file to OneDrive or SharePoint, Power BI will automatically refresh the data roughly every hour. This setup doesn’t require a gateway and provides a more seamless way to keep data current:
- Upload the Excel file to OneDrive or SharePoint.
- Connect Power BI to the file using the OneDrive or SharePoint connector, and Power BI will refresh the dataset hourly without needing manual intervention.
-
Direct Query (Alternative Approach): If you’re dealing with data that needs real-time or near real-time access, consider moving the data to a database that supports DirectQuery (e.g., SQL Server, Azure SQL Database). DirectQuery allows Power BI to fetch the latest data every time a user interacts with the report, though it’s limited to databases and doesn’t support Excel files.
-
Power Automate for Triggered Refreshes: If you’re using Power BI Premium or Power BI Pro with certain configurations, you can set up a Power Automate flow that triggers a refresh of the Power BI dataset when the Excel file is updated. To set this up:
- Create a Power Automate flow that monitors changes to the Excel file.
- Use the Power BI “Refresh a dataset” action in Power Automate to refresh the dataset when a change is detected.
-
If real-time updates aren’t possible, you can set up refresh notifications for users. In Power BI Service, users can subscribe to the report and receive an email notification when the report is refreshed. This helps ensure that users know when they’re viewing the latest data.
Data refresh in Power BI - Power BI | Microsoft Learn
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive