You can import Excel workbooks that are stored on your local machine, or in cloud storage such as OneDrive for Business or SharePoint Online. We will look at the advantages of using cloud storage for your Excel files. For more information on how to import Excel files into Power BI, see Get data from Excel workbook files.
What are the advantages?
Create your best work with the latest versions of Word, Excel, and other Office apps. Plus, get 1 TB of cloud storage, document sharing, ransomware recovery, and more with OneDrive. An Excel file may be locked in OneDrive for an update or delete up to 12 minutes since the last use of the connector. A single Excel file should be used by a single connection to prevent writing data from multiple connections and possible data inconsistency. Simultaneous file modifications made by other connectors or manual edits are not supported. Do you want to learn to use PowerApps Excel Spreadsheets? Then this video is for you. We take an Excel workbook hosted in OneDrive for Business and use it as. Collaborate for free with an online version of Microsoft Excel. Save spreadsheets in OneDrive. Share them with others and work together at the same time.
Importing files from OneDrive, or SharePoint Online, is a great way to make sure the work you’re doing in Excel stays in-sync with the Power BI service. Any data you’ve loaded into your file’s model is imported into the dataset, and any reports you’ve created in the file are loaded into Reports in Power BI. If you make changes to your file on OneDrive, or SharePoint Online, like add new measures, change column names, or edit visualizations, once you save, those changes are updated in Power BI too, usually within about an hour.
When you import an Excel workbook from your personal OneDrive, any data in the workbook, like tables in worksheets and/or data that are loaded into the Excel data model and the structure of the data model, are imported into a new dataset in Power BI. Any Power View visualizations are re-created in Reports. Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.
You can refresh the dataset in the Power BI service. When you manually refresh, or schedule refresh, on the dataset, Power BI connects directly to the external data sources to query for updated data it then loads into the dataset. Refreshing a dataset from within Power BI does not refresh the data in the workbook on OneDrive, or SharePoint Online.
What’s supported?
In Power BI, Refresh Now and Schedule Refresh are supported for datasets created from Power BI Desktop files imported from a local drive where Get Data/Query Editor is used to connect to and load data from any of the following data sources:
Power BI Gateway - Personal
- All online data sources shown in Power BI Desktop’s Get Data and Query Editor.
- All on-premises data sources shown in Power BI Desktop’s Get Data and Query Editor except for Hadoop file (HDFS) and Microsoft Exchange.
On-premises data gateway
Data source
- Analysis Services Tabular
- Analysis Services Multidimensional
- SQL Server
- SAP HANA
- Oracle
- Teradata
- File
- Folder
- SharePoint list (on-premises)
- Web
- OData
- IBM DB2
- MySQL
- Sybase
- SAP BW
- IBM Informix Database
- ODBC
Note
A gateway must be installed and running in order for Power BI to connect to on-premises data sources and refresh the dataset.
OneDrive or OneDrive for Business. What’s the difference?
If you have both a personal OneDrive and OneDrive for Business, it’s recommended you keep any files you want to import into Power BI in OneDrive for Business. Here’s why: You likely use two different accounts to sign into them.
Connecting to OneDrive for Business in Power BI is typically seamless because the same account you use to sign into Power BI is often the same account used to sign into OneDrive for Business. But, with personal OneDrive, you likely sign in with a different Microsoft account.
When you sign in with your Microsoft account, be sure to select Keep me signed in. Power BI can then synchronize any updates you make in the file in Power BI Desktop with datasets in Power BI.
If you make changes to your file on OneDrive that cannot be synchronized with the dataset or reports in Power BI, because your Microsoft account credentials might have changed, you’ll need to connect to and import your file again from your personal OneDrive.
Options for connecting to Excel file
When you connect to an Excel workbook in OneDrive for Business, or SharePoint Online, you’ll have two options on how to get what’s in your workbook into Power BI.
Import Excel data into Power BI – When you import an Excel workbook from your OneDrive for Business, or SharePoint Online, it works as described above.
Connect, Manage, and View Excel in Power BI – When using this option, you create a connection from Power BI right to your workbook on OneDrive for Business, or SharePoint Online.
When you connect to an Excel workbook this way, a dataset is not created in Power BI. However, the workbook will appear in the Power BI service under Reports with an Excel icon next to the name. Unlike with Excel Online, when you connect to your workbook from Power BI, if your workbook has connections to external data sources that load data into the Excel data model, you can set up a refresh schedule.
When you set up a refresh schedule this way, the only difference is refreshed data goes into the workbook’s data model on OneDrive, or SharePoint Online, rather than a dataset in Power BI.
How do I make sure data is loaded to the Excel data model?
When you use Power Query (Get & Transform in Excel 2016) to connect to a data source, you have several options where to load the data. To make sure you load data into the data model, you must select the Add this data to the Data Model option in the Load To dialog box.
In Navigator, click Load To…
Or, If you click Edit in Navigator, you’ll open the Query Editor. There you can click Close & Load To….
Then in Load To, make sure you select Add this data to the Data Model.
What if I use Get External Data in Power Pivot?
No problem. Whenever you use Power Pivot to connect to and query data from an on-premises or online data source, the data is automatically loaded to the data model.
How do I schedule refresh?
When you set up a refresh schedule, Power BI connects directly to the data sources using connection information and credentials in the dataset to query for updated data, then loads the updated data into the dataset. Any visualizations in reports and dashboards based on that dataset in the Power BI service are also updated.
For details on how to set up scheduled refresh, see Configure scheduled refresh.
When things go wrong
When things go wrong, it’s usually because Power BI can’t sign into data sources, or if the dataset connects to an on-premises data source, the gateway is offline. Make sure Power BI can sign into data sources. If a password you use to sign into a data source changes, or Power BI gets signed out from a data source, be sure to try signing into your data sources again in Data Source Credentials.
Be sure to leave the Send refresh failure notification email to me checked. You’ll want to know right away if a scheduled refresh fails.
Important notes
Refresh is not supported for OData feeds connected to and queried from Power Pivot. When using an OData feed as a data source, use Power Query.
Troubleshooting
Onedrive Excel Online
Sometimes refreshing data may not go as expected. Typically this is an issue connected with a gateway. Take a look at the gateway troubleshooting articles for tools and known issues:
More questions? Try the Power BI Community
-->With Azure Logic Apps and theExcel Online for Business connector orExcel Online for OneDrive connector,you can create automated tasks and workflows basedon your data in Excel Online for Business or OneDrive.This connector provides actions that help you work withyour data and manage spreadsheets, for example:
- Create new worksheets and tables.
- Get and manage worksheets, tables, and rows.
- Add single rows and key columns.
You can then use the outputs from these actions withactions for other services. For example,if you use an action that creates worksheets each week,you can use another action that sends confirmation emailby using the Office 365 Outlook connector.
If you're new to logic apps, reviewWhat is Azure Logic Apps?
Note
The Excel Online for Businessand Excel Online for OneDrive connectorswork with Azure Logic Apps and differ from theExcel connector for PowerApps.
Prerequisites
An Azure subscription. If you don't have an Azure subscription,sign up for a free Azure account.
A work or school accountfor your work account or personal Microsoft account
Your Excel data can exist as a comma-separatedvalue (CSV) file in a storage folder, for example, in OneDrive.You can also use the same CSV file with theflat-file connector.
Basic knowledge abouthow to create logic apps
The logic app where you want to access your Excel Online data.This connector provides only actions, so to start your logic app,select a separate trigger, for example, the Recurrence trigger.
Onedrive Excel Spreadsheet
Add Excel action
In the Azure portal, open yourlogic app in the Logic App Designer, if not already open.
Under the trigger, choose New step.
In the search box, enter 'excel' as your filter. Under the actions list,select the action you want.
Note
The Logic App Designer can't load tables that have 100 or more columns.If possible, reduce the number of columns in the selected table so thatthe designer can load the table.
If prompted, sign in to your work or school account.
Your credentials authorize your logic app to create aconnection to Excel Online and access your data.
Continue providing the necessary details for the selected actionand building your logic app's workflow.
Connector reference
Onedrive Excel
For technical details, such as triggers, actions, and limits,as described by the connector's OpenAPI (formerly Swagger) files,see these connector reference pages:
Next steps
Onedrive Excel Login
- Learn about other Logic Apps connectors