How to Import Multiple Excel Sheets into Power BI

Follow these steps to import an Excel file into Power BI. We cover how to import from local folders and services like Microsoft OneDrive, SharePoint while working with multiple sheets at once.

Importing an Excel File into Power BI

When you first open Power BI, the Toolbar at the top of the screen has multiple ways that you can open an Excel file and import it into Power BI. You can either click on the Excel Workbook button or the Get Data button at the top of the page

Screenshot of importing an Excel file into Power BI using the Get Data or Excel Workbook options


Your choice will depend on where your Microsoft Excel document is located. If it is saved on a network drive or on your laptop, you can use the Excel Workbook button and choose the file.

If you are importing an Excel File from a cloud service such as OneDrive, SharePoint or the PowerBI Service you will have to click on Get Data

Importing an Excel File from SharePoint

One of the best parts of Power BI is how flexible it is and how many data types it works with after installing it. These connections range from giving you the ability to connect directly to many Microsoft products like SharePoint Folders full of Excel files.

Once you choose the SharePoint Folder, you will be able to select it after logging into your organization account. Microsoft uses single sign on to verify that it’s you. Then you will be shown a table of all of the files that are available. Expand the table, and the files will import.

Connecting PowerBI to a SharePoint or a OneDrive folder full of Microsoft Excel workbooks.

Importing an Excel File from OneDrive

Microsoft used to offer a OneDrive connector in PowerBI. It has since been removed in recent versions of the popular data visualization software. This can be a big nuisance, but there is an easy workaround. Use the SharePoint Folder connector and point it to the OneDrive URL that you are trying to import.

You can find the URL by going to and logging into your account. You will be able to copy and paste the URL of the folder you want to bring in.

Note: Only want to copy the URL portion before “_layouts/15/onedrive.aspx?view=1”

Power BI will prompt you to log in using your organization account. This is usually your Windows user ID that you log into your computer or Office365.

Importing Multiple Excel Files at Once

After you select a folder of files to import in Power BI, you will be prompted with a screen like the one below. This screen lists all of the file information for each file in the folder. You can then filter on the specific file folder, or set of file names in the next step.

Folder contents of files to import into PowerBI for consolidating excel or other file formats.

Click on Transform Data

You will then be presented with the Power Query editor. From here, you can filter your list by clicking the top of each column and applying a filter from the drop down menu. Common ways to filter file lists are to filter based on containing specific words, filtering to a specific folder, or filtering on the most recent date.

Note: You can combine filters and reduce the list using multiple criteria. Be aware of the naming convention if you want to save additional files to be consolidated in future periods.

Screenshot of filtering a list of OneDrive, Sharepoint or Network Files to import into PowerBI

After filtering to the list of files that you want, the top row of Power Query will have a column field named Content. If you click the two down arrows in the top right of the column it will expand the dataset and combine the files that you are importing.

Screenshot of filtering a list of Excel files being imported from Microsoft OneDrive, SharePoint or a local network drive.

Import Multiple Sheets from a Workbook

While working with multiple sheets in a single Excel workbook, you are prompted to choose the sheets that you want to bring into your data model.

To select multiple at one time: Click a sheet at the top of the list, Hold down SHIFT, and click on one of the sheets below. This will automatically select all of the sheets within the range that you selected.

Using this technique means that you won’t have to click on every individual sheet all at once. It’s a great time saver when working with large workbooks.

Screenshot of importing multiple excel sheets from a single workbook in Power BI

Use a Power BI Gateway to Import On-Premises Data to

If you are connecting to a folder of files saved on a local network, but still want to enable automatic refreshes through you will need to install a PowerBI Gateway. This typically involves some help from your IT department, as the gateway should be installed on a computer that is always on, usually a virtual machine or a server.

There is a version that can be installed on your laptop or desktop computer, but the automated refresh features will not be available if your computer powers off.

We wrote an in-depth guide on Power BI Gateways to help you set one up.

Pop! Automation is Here to Help

Contact us today If you need help developing Power BI dashboards and reports, Pop! Automation offers hands on training courses in-person or virtually. We also do hands off development where you work with one of our professional developers and consultants to scope out a solution. We build it and then hand it off to your team with training on how the report works so that it can be maintained as business needs change.

    Contact Us Today!

    Scroll to Top