Connecting your Excel dataset to Power BI is a piece of cake. You can import Excel to Power BI from your desktop or connect a workbook stored on OneDrive. For this, you won’t have to set up complex configurations or deal with REST APIs.
Nevertheless, you may have different scenarios on what this connection should look like connect Excel to Power BI if your file is stored in the cloud, or import data to Power BI service. This is why we created this Excel Power BI ultimate guide to make your life easier. Let’s go!
How to use Power BI with Excel
We decided to split our tutorial into two high-level sections:
- Connect Excel to Power BI desktop
- Connect 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.
Import Excel to Power BI from your computer
To import Excel to Power BI, click Excel Workbook on the Home ribbon of the Power BI desktop app. 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. And if you need some help with Power BI, you can also take a look at our Power BI tutorial.
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.
OneDrive Excel connect to Power BI dataset
Power BI doesn’t offer a built-in connection to OneDrive. However, you can connect Excel files from OneDrive to Power BI with Coupler.io.
This Power BI connector also allows you to import data from other 60+ sources. Before loading data, you can organize it on the go. Additionally, the connector allows you to schedule automated data refreshes, even as frequently as 15 minutes.
We’ve already preselected OneDrive as a source and Power BI as a destination app. So, click Proceed in the form below.
Note: Using OneDrive as a source allows you to export data from both Excel and CSV files on OneDrive. If you go with Excel as a source, you’ll be able to only export Excel to Power BI.
Complete the following steps:
Step 1. Extract data
- Connect to your Microsoft account for OneDrive.
- Select an Excel workbook and a worksheet to extract data from. You can also select a CSV file on OneDrive.
Coupler.io allows you to create reports using data from multiple sources. If you need to extract data from different Excel files, click on the Add one more source button and configure the connection accordingly.
Step 2. Transform data
At this step, you can preview the data to be extracted and even transform it before loading it to Power BI. The transformation options include:
- Column management – you can hide/unhide columns, edit their names and types
- Filter – you can filter data by different criteria
- Sort – you can sort data in ascending/descending order
- Formula – you can create custom columns based on the supported formulas.
With your data prepared, you can connect the Excel to Power BI. Follow the instructions in the wizard:
- Click Save and Run to run the importer.
- Copy the Power BI integration URL.
- Go to Power BI desktop and navigate to Get data => Web.
- Insert the integration URL and click OK. This will open Power Query Editor with the data imported from Excel on OneDrive.
Now you can make additional transformations if needed and load the data set to Power BI.
If you want to have this dataset refreshed in Power BI, you’ll need to complete one more step.
Step 3. Schedule importer
Go back to Coupler.io and toggle on the Automatic data refresh. This will enable the schedule according to which the data will be refreshed. You can specify an update interval from every month to daily and even every 15 minutes, making your report live.
That’s it. In less than 5 minutes, you’ve created a fully automated dataflow that loads data from Excel OneDrive to Power BI.
A workaround to connect to Power BI dataset from Excel on OneDrive
There is also a workaround to connect Excel to Power BI for the files 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.
Do you need Excel as a mediator to connect apps to Power BI?
Power BI is a tool where you can create interactive reports and Excel serves as a source of data for them. Moreover, it’s a frequent case that financial or sales specialists use Excel as a mediator to transfer data from a business app to Power BI.
For example, you have a dataset in QuickBooks or Pipedrive, and you want to build a report based on that data. So, you export data from the app as an Excel file and then import Excel to Power BI.
Your data source => Excel => Power BI
You no longer need to go this way!
The reporting automation solution we introduced above for connecting OneDrive Excel to Power BI, Coupler.io, allows you to integrate multiple apps with Power BI and other destinations. This means that you won’t need Excel as a mediator anymore if you want to connect QuickBooks, Pipedrive, Salesforce, or another app to Power BI.
All you need to do is select a desired source app, click Proceed ,and complete 3 simple steps.
- Step 1. Extract data – configure the connection to your source application and select the needed data.
- Step 2. Transform data – preview the data to load to Power BI and make it analysis-ready using the available transformation options. Load the source data to Power BI.
- Step 3. Schedule importer – automate the data flow from your source app to Power BI on the desired schedule.
Automate data export with Coupler.io
Get started for freeConnect Power BI service to Excel
Now, let’s see how you can connect Excel to the online version of Power BI. Click the magic button Get data, then Files.
Then you 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 export 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.
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)
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 the case of the Power BI service, you’ll need to refresh the connected dataset. Here is the button.
Export Power BI to Excel
Now, let’s briefly mention that you can also load data in a reverse direction: from Power BI to Excel. Here is how you can do this for both desktop and online Power BI versions.
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.
Export Power BI service to Excel
We promised to show how you can export report data from the Power BI service to an 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 Excel file to your computer.
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 Looker Studio or Tableau. For more details, see our article on how to connect Excel to Tableau.
Coupler.io allows you to synchronize data from your Excel workbook or other 60+ apps with these BI tools. Choose the best data visualization solution for your project and good luck with your data!
Automate data export with Coupler.io
Get started for free
