Power BI – Get Data from Excel Workbook files

Supported Excel Workbooks 

Power BI can import or connect to workbooks that were created in Excel 2007 or later versions. To be compatible with Power BI, the workbooks must be saved in either .xlsx or .xlsm file format and must not exceed 1 GB in size. It’s important to note that certain features discussed in this article may only be available in newer versions of Excel.

• Workbooks with ranges or tables of data
• Workbooks with data models
• Workbooks with connections to external data sources
• Workbooks with PivotTables and charts

Data types

To enhance your Power BI experience, it’s recommended to label data in Excel with appropriate data types. Power BI supports data types such as Whole Numbers, Decimal Numbers, Currency, Date, True/False, and Text.

Streamlined upload to Power BI

To add files to a workspace in Power BI, look for the “Upload” dropdown menu option next to the “New” button. From there, you can connect to files stored in your OneDrive for Business or any SharePoint site that you have permission to access. Alternatively, you can upload files from your computer by selecting the “Browse” option. See the image below for reference.

Uploading a local file to the workspace creates a duplicate of the file in the workspace. However, if you opt for the OneDrive for Business or SharePoint option, Power BI establishes a connection to the file. Any changes you make to the file in SharePoint are automatically synced with Power BI approximately every hour. When you upload an Excel file through this method, it appears in Power BI in a similar format as it would in Excel Online. The image below depicts this.

At present, you can still import data from an Excel file into a Power BI dataset using the get data feature mentioned in this article, regardless of your subscription status. However, if you don’t have a subscription, you won’t be able to use the OneDrive for Business and SharePoint options. Nonetheless, you can still locate local files on your computer by selecting the Browse option, as shown in the image below.

Uploading files from personal OneDrive accounts is not supported, however, you can still import data from workbooks stored in personal OneDrive accounts using the “Get Data” feature described later in this article.

Import or connect to an Excel workbook from Power BI

• Click Get Data in Power BI’s nav pane.

• Click Get in Files.

• Find your file.

• Choose Import or Connect if your workbook file is on OneDrive or SharePoint – Team Sites.

Local Excel workbooks

It is also possible to upload a local Excel file into Power BI. Simply choose Local File from the previous menu, then navigate to the location of your Excel workbooks.

Select Upload to Power BI from the menu.

As soon as your workbook is uploaded, you will receive a notification that it has been added to your list of workbooks. It is located in the workspace’s nav pane under Workbooks, and in the workspace’s content list.

Excel files stored locally cannot be edited within Power BI, unlike Excel files stored on OneDrive or SharePoint – Team sites.