Ways to Connect your Excel File to Power BI
Before enabling data refresh, you first need to connect your Excel file to Power BI.
There are basically two different ways:
1. Importing an Excel File (Static data)
• If you upload an Excel directly to Power BI, it creates a snapshot of the data at that moment.
• Any changes to the original file won't be reflected in Power BI unless you upload it again.
• Best for datasets that do not change frequently.
2. Connect to Online Excel File (Dynamic Data)
• If your Excel file is stored in OneDrive or SharePoint, Power BI can automatically sync changes such that you will set up your scheduled refresh without needing to do it manually.
• Best applicable for 'live' continuously changing datasets.
Using OneDrive or SharePoint is the best option for enabling automatic updates.
Set Data Refresh in Power BI Service:
Once you have connected your Excel file with Power BI, you should follow these steps in setting up the automatic refresh.
Step 1. Publish the Report to Power BI Service
1. Open Power BI Desktop
2. Click Get Data → Excel and Load Your Excel File
3. Create Your Reports and Dashboards
4. Click Publish and upload the report to Power BI Service
Step 2: Find Your Dataset in Power BI Service.
1. Sign in to Power BI Service (https://app.powerbi.com).
Go to Workspaces → Choose the workspace where your report has been published
2. Click on Datasets + Dataflows → Find your Excel dataset.
Step 3: Schedule Refresh Configuration
1. Click on the More Options (⋮) → Settings next to your dataset.
2. Down at Scheduled Refresh.
3. Toggle on Keep Data Updated.
Step 4: Set Refresh Frequency
• You can now choose how often Power BI will refresh your data:
Hourly - If you want real time updates.
Daily - This is a good refresh type for moderately changing data.
Weekly- Effective for the data refreshes that are infrequent.
• Choose the time zone and define refresh times.
Step 5: Set Your Data Source Credentials
1. Navigate in Dataset Settings to Data Source Credentials.
2. If your Excel file is stored in OneDrive or SharePoint, log in using Microsoft credentials.
3. If stored locally, set up a Power BI Gateway.
Using Power BI Gateway for Local Excel Files
Local storage data like Excel files or files shared on the network cannot be accessed directly by Power BI for an automatic refresh.
So, a Power BI Gateway must be established to set a safe connection.
Install and Configure a Gateway
1. First, download Power BI Gateway from the Power BI website.
2. Install Power BI Gateway on the computer where Excel files are stored.
3. Run the app and log in using your account.
4. Add the local file path into the settings in the gateway.
5. Now go to Dataset Settings in Power BI Service and update data source credentials.
Now your Power BI is ready for the automatic fetching of the latest data from your local Excel file.
Best Practices in Data Refresh in Power BI
This establishes vital smoothness in the data refresh. These are the best guidelines:
1. Store Excel Files in OneDrive or SharePoint
✔️ Instead of storing files on local systems, they need to have a gateway.
✔️ OneDrive and SharePoint provide good automatic refresh without needing a gateway.
2. Keep the Size of Your Excel File Manageable
✔️ The larger a file, the longer it takes to refresh and more likely it is to fail.
✔️ A file size lower than 1GB yields optimum performance.
3. Set Up Incremental Refresh for Large Datasets
✔️ Whereas Power BI refreshes an entire data set, only new or changed data is refreshed by Power BI.
✔️ Makes refresh faster and reduces server burden.
4. Monitor Refresh Failures
✔️ To monitor refresh failures, check Refresh History in Power BI Service.
✔️ Set up email alerts on refresh failures.
Troubleshooting Common Data Refresh Problems
At times, data refresh needs to be invoked experimentally or during time it takes too long.
Here are some of the solutions for those primarily encountered:
Issue 1: Unable to Connect to Data Source Error.
Solution: Ensure the Excel file path hasn't been shifted and the file is accessible.
Issue 2: Data refresh is slow or not working.
Solution: Shrink the file size, upload to OneDrive and Use Incremental refresh.
Issue 3: Refresh works in Power BI desktop but doesn't work in Power BI service.
Solution: Check Data Source Credentials.
Issue 4: Gateway Not Configured Error.
Solution: If working with Excel files that are stored on computers, then install and configure the Power BI Gateway.
Conclusion
It is important to create data refresh settings on Excel files in Power BI to ensure that the reports can be brought up to date.
This will now allow you to:
✔️ Efficiently connect Excel files to Power BI.
✔️ Schedule refreshes for updates to be automated.
✔️ Use Power BI Gateway for local files.
✔️ Fix refresh issues if they happen.
With automated Excel data refresh, Power BI reports provide accurate and real time insights at all times. You want to set up a refresh today and enjoy effortless use of Power BI!
