Connect Excel to Power BI – Every Detail You Should Know
Connecting your Excel dataset to Power BI is a piece of cake. You won’t have to set up complex configurations or install any plugins or third-party tools. However, this topic includes more than just getting data from a workbook. You may need to load a transformed dataset, connect your Excel file stored on OneDrive or SharePoint to Power BI, and so on. This is why we created this Excel Power BI ultimate guide to make your life easier.
How to use Power BI with Excel
We decided to split our tutorial into two high-level sections:
- Connecting Excel to Power BI desktop
- Connecting Excel to Power BI service
Each of these sections will contain answers to mostly the same questions, such as how to get data from a local Excel file or how to export data from Power BI to Excel.
Connect Excel to Power BI desktop
The Power BI desktop app provides a wide set of connectivity options.
For example, we’ve blogged about connecting Google Sheets to Power BI. Excel is undisputedly the most common data source. Let’s start with it.
Local Excel connect to Power BI dataset
On the Home ribbon, click “Excel Workbook“, or you can select the respective option on the home screen.
Select the Excel workbook stored on your device and then, once the Navigator window opens, select the worksheet that contains the necessary data. Click “Load“.
Your dataset will be imported from Excel to Power BI.
Transform data before loading
Let’s say, you don’t need to import an entire sheet from Excel to Power BI. Or you may want to rename columns, exclude some rows, or otherwise transform the dataset for import. In this case, once you’ve selected an Excel workbook and worksheet, click “Transform Data“.
The Power Query Editor will open, where you can choose and remove columns/rows, group values, change data types, etc. Check out our Power Query Tutorial for more.
For example, let’s keep a few columns that we’ll use for our Power BI report:
Once you’re done with the data transformation, click “Close & Apply” to load the dataset to Power BI.
And here is the result.
Connect Power BI desktop to Excel OneDrive
Power BI does not provide a preset connection to OneDrive. However, there is a workaround to connect to your Excel workbook stored on OneDrive.
Go to your OneDrive, right-click on the Excel workbook to import, and select Embed.
Click the “Generate” button to generate an HTML code for embedding.
You should get a code like this:
<iframe src="https://onedrive.live.com/embed?cid=EC24D8340D235C65&resid=EC24D8340D235C65%213421&authkey=AHVqD34kq_kqoHP&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>
Copy the values from resid and authkey parameters and insert them into the following boilerplate:
https://onedrive.live.com/download?resid={replace-with-your-value}&authkey={replace-with-your-value}&em=x&app=ExcelYou should get the following URL string:
https://onedrive.live.com/download?resid=EC24D8340D235C65%213421&authkey=AMVqX25kq_kwoJK&em=2&em=x&app=Excel
Now get back to Power BI and select Get data => Web
In the open window, insert your URL string and click “Ok“:
Confirm your connection to OneDrive and welcome the Navigator window, where you can select the sheets to import.
The next steps you already know.
Export data from Power BI to Excel
You can export your reports’ data from Power BI desktop in CSV format. For this, click the three dots in the top right corner of a report and select “Export Data“.
After that you’ll need to select a folder where you want to save a CSV file with your data. That’s it. In Power BI service, you can export reports as Excel files. We’ll cover this part later.
Connect to Power BI dataset from Excel as a mediator
Before talking about Power BI service, we’d like to highlight that you can use Excel as an intermediate point to load data from multiple sources to Power BI. The list of Power BI supported sources is long enough, but it lacks such apps as Shopify, Airtable, Pipedrive, and many others. In this case, you can connect your source to Excel, and then connect Excel to Power BI:
Your data source => Excel => Power BI
We explained the details of such a route in the blog post Shopify to Power BI. To summarize, you can use Coupler.io, a tool for integrating apps and sources with Excel, Google Sheets, or BigQuery. Each integration setup starts includes three steps:
- Source – you need to select a source app, for example, Harvest, connect to it, and select a data entity for export.
- Destination – you need to select Excel as a destination app, connect to your Microsoft account, and choose an Excel file on OneDrive to load data to.
- Schedule – you need to enable the Automatic data refresh and configure the frequency you want.
Here is what an importer can look like:
After that, you need to connect your Excel file to the Power BI desktop, as we described above, or to Power BI service, as we’ll describe next.
Check out the available Excel integrations.
Connect Power BI service to Excel
In the online version of Power BI, click a magic button “Get data“, then “Files“.
Then will be able to load data from Excel files stored locally on your device, or in a cloud service, including OneDrive and SharePoint.
Important note:
The data in your Excel workbook must be formatted as a table, otherwise you will get the following error when importing or uploading files to Power BI.
To format a range as a table, open your Excel file, select the range of cells that contain your data and press Ctrl + T. Click “OK” to confirm the creation of a table.
Do the same for ranges in each worksheet.
Now you’re ready to import data from Excel to Power BI service.
Local Excel file to Power BI service
Click the “Local File” button, select an Excel file from your device, and choose how to connect the chosen workbook to Power BI:
- Import Excel data into Power BI (data refresh is available)
- Upload your Excel file to Power BI (no data refresh)
Select the desired option and welcome your data in Power BI. By the way, here is what the Excel file looks like if imported and uploaded to Power BI.
Excel OneDrive to Power BI service
You can load data from OneDrive Personal and Business accounts. Let’s go with the Business one for this example. Click the respective option and select the Excel workbook. Then click “Connect“.
Choose how to connect to your Excel workbook:
- Import Excel data into Power BI (connect a workbook to Power BI with automatic data refresh)
- Connect, manage, and view Excel in Power BI (bring a workbook to Power BI)
Here is what the Excel OneDrive file looks like if imported and connected to Power BI.
Now it’s time for another cloud storage – SharePoint. Click the respective option and enter the URL of your SharePoint site. For example:
https://marbrecords.sharepoint.com/sites/MBrecords2
Note: Use the URL that corresponds to your homepage of your SharePoint site, like this:
Then choose the folder where your Excel workbook is stored, select the file and click “Connect“.
Then choose how to connect to your Excel workbook. The options are the same as for connecting Excel OneDrive to Power BI:
- Import Excel data into Power BI (connect a workbook to Power BI with automatic data refresh)
- Connect, manage, and view Excel in Power BI (bring a workbook to Power BI)
Export Power BI service to Excel
We promised to show how you can export report data from Power BI service to Excel file. Open your report, click the three dots, and select “Export data“.
Then you can choose the format for your export file: CSV or XLSX (Excel).
Click “Export” to download the file to your computer.
How often is the Excel to Power BI connection refreshed?
Power BI updates data from the connected Excel files, both local and cloud, on a one hour basis. To manually refresh the dataset, click the “Refresh” button on the Home ribbon.
In case of Power BI service, you’ll need to refresh the connected dataset. Here is the button.
Excel to Power BI or another data visualization tool
Power BI easily connects to Excel and many other data sources. However, this data visualization tool has its limitations and flaws. At the same time, you can consider an alternative, such as Google Data Studio or Tableau. These tools integrate seamlessly with Google Sheets, so, with Coupler.io, you’ll be able to synchronize data from your Excel workbook with Google Sheets. Choose the best dataviz tool for your project and good luck with your data!
