How to Automate Reports from Excel to Power BI

 

Many businesses use Excel for data management and reporting. Power BI offers powerful data visualization, but manually updating dashboards every time Excel files change is tiresome. Automating the process saves time and ensures your Power BI reports always display up-to-date information.

In this tutorial, we will show how to automate reports from Excel to Power BI.

Method 1: Direct Excel Connection

Step 1: Prepare Your Excel File

• Format as Table:
  • Select your data range.
  • Go to the Insert tab >> select Table or press Ctrl + T.
  • Check My table has headers.
  • Click OK.

• Rename your table:
  • Go to the Table Design tab >> select Table Name (use descriptive names like Sales or Sales_Data etc).

• Clean Data: Ensure no merged cells, blank rows, or inconsistent column headers.
• Save Location: Save your Excel file in an accessible location. Power BI will need to reference this path.

Step 2: Connect Power BI to Excel

• Open Power BI Desktop.
• Go to Home tab >> select Excel workbook.
  • Browse and select your Excel file.

• • In the Navigator window, select the tables/sheets you want to import.
  • Click Load to load data directly.

• Configure Connection:
  • Click Transform Data to open Power Query Editor.
  • Make necessary data transformations.
  • Click Close & Apply.

Step 3: Set Up Automatic Refresh

Publish to Power BI Service:

• Go to the File tab >> select Save and give your file a name (e.g., Sales_Report.pbix).
• Go to the Home tab >> select Publish.
• Select your Workspace (e.g., My Workspace).
• Click Select.

• Your report is now online and ready to share.

Configure Dataset Refresh:

• If Excel is on a Local PC or a Network Drive.
• Go to the Power BI Service.
• Schedule refresh works only if you use Power BI Gateway:
  • Download and install Power BI Gateway.
  • Register the gateway and connect it to your Power BI Service account.

Access Dataset (Semantic Model) Settings:

• Click on My Workspace.
• Select “Automate Reports from Excel” Semantic model.
• Hover your mouse over the desired Semantic model.
• Click on the three dots (“…”) that appear at the end of that row.
• In the dropdown, click Settings.

• This will take you to the settings page for your dataset.

• Manage the Gateway connections.
• Click Apply.

• Configure Data source credentials.
• Set up a scheduled refresh.
• Expand Refresh.
  • Select a Time zone.
  • Click on Configure a refresh schedule.
  • Select “Refresh frequency” Daily.
  • Select “Send refresh failure notifications to” Semantic model owner.
  • Select These contacts.
  • Click Apply.

Limitations: Direct file connections require the file path to remain constant and may not work with cloud storage.

Method 2: OneDrive/SharePoint Integration

Step 1: Store Excel Files in the Cloud

• Upload to OneDrive for Business or SharePoint
  • Create a dedicated folder for your data files.
  • Upload your Excel files.
  • Ensure proper sharing permissions.

Step 2: Connect Power BI to Cloud Files

In Power BI Desktop:

• Go to the Home tab >> select Get Data >> select Online Services >> select SharePoint Online List (or OneDrive for Business).

• Enter your SharePoint site URL or OneDrive path.
• Navigate to your Excel file.
• Click OK.

Alternative Method:

• Go to the Home tab >> select Get Data >> select From Web.
• Use OneDrive/SharePoint sharing URL.

Step 3: Configure Automatic Updates

Set Refresh Schedule:

• Publish your report to the Power BI Service.
• Configure scheduled refresh (up to 8 times daily with Pro license).

Enable Real-time Updates:

• Files updated in OneDrive/SharePoint automatically trigger data refresh.
• Changes appear in Power BI within minutes to hours.

Advantages:

• Automatic file synchronization.
• Better collaboration capabilities.
• More reliable than local file connections.

Share and Monitor Your Reports

• Share Dashboard: In Power BI Service, click Share to send dashboards to stakeholders.
• Monitor Refresh: Under your dataset’s settings, check the refresh history and notifications.

Tips & Best Practices

• Consistent Structure: Keep Excel columns and structure unchanged to avoid refreshing errors.
• Error Alerts: Set up email notifications for refresh failures.
• Data Security: Use permissions on OneDrive/SharePoint to control access.
• Version Control: Keep backup copies of your Excel file.
• Refresh Failure: Check if the Excel file moved, the table name changed, or if the gateway is running.
• Data Not Updating: Ensure the file is saved/overwritten in the same location and the data is inside a table.
• Gateway Issues: Restart the gateway service or reinstall if connectivity fails.

Conclusion

Automating reports from Excel to Power BI streamlines your analytics workflow and ensures data accuracy. Start with the simplest approach that meets your requirements, then gradually implement more sophisticated automation as your needs grow. Regular monitoring and maintenance ensure your automated reporting system remains reliable and efficient. Choose the method that best fits your organization’s needs.

Remember to test thoroughly in a development environment before deploying to production and always maintain backup copies of your source data and Power BI files.