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
.
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.
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 OneDrive.com 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.
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.
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.
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.
Use a Power BI Gateway to Import On-Premises Data to PowerBI.com
If you are connecting to a folder of files saved on a local network, but still want to enable automatic refreshes through powerbi.com 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.