This article will be showing, two very easy ways to connect to excel files in Power BI and how you can append multiple similar excel files in an automated way.
Blog structure:
1) Connect to Excel in Power BI
⦁ Connect to Excel using an Excel connector.
⦁ Connect to Excel using Folder.
2) Combine similar Excel files into one table dynamically.
Connect to Excel using Excel connector:
Step 1: Either select ‘Excel’ in Home ribbon or click on ‘Get data’ & select ‘Excel’.
Step 2: A browse window will pop up. Navigate to the Excel file’s location. Click on the file & select ‘Open’.
Step 3: In the ‘Navigator’ window, select the required sheets & click ‘Load’. Clicking on Transform data will take you to the Power Query Editor.
Step 4: Apply transformations of your choice & then choose ‘Close & apply’. Your data will be ready to visualize.
Connect to Excel using Folder:
Step 1: Click on ‘Get data’ & select ‘Folder’.
Step 2: It will open a ‘Folder’ dialog box. Click on Browse and navigate to the folder which has your excel files. Select the folder & click OK. Proceed by clicking OK for the ‘Folder’ dialog box.
Step 3: Click on ‘Transform Data’ option. It will open the Power Query Editor.
Note: You would have noticed that the ‘Navigator’ window gives an extra option to Combine the files. We will not be combining it in this section, but you can use it to shorten the steps required to use this feature which I will be explaining in the next section.
Step 4: In Power Query Editor, when you want to select individual excel files, you can click on ‘Binary’ next to the name of the file.
Step 5: Select ‘Data’ next to the name of the sheet. And once you’re done with other required transformations, hit ‘Close & apply’.
Combine similar Excel files into one table:
Step 1: Follow Steps 1 to 3 from the previous section (Connect to Excel using Folder).
Step 2: In Power Query Editor, click the combine button from the top-right corner of the ‘Content’ column.
Step 3: In Combine files window, select a sample file on which you can specify transformations that will be automatically repeated for the other files in the list. (Default selection will be the first file)
Step 4: Select the sheet. Then click on OK.
Note: Name of the sheet must be the same across all the files that are being combined.
After step 4, you will notice that Power BI has created a bunch of queries & functions by default. In them, there will be a ‘Transform Sample File’ query (You can rename it later) which will have the basic transformations that Power BI applies according to the structure of the sheet. More transformations can be added to this query & Power BI will automatically replicate it across all the other Excel files.
Step 5: In the main query, Power BI must’ve pulled all the data form every file present in the folder. Power BI will also create a column to store the file name to make it simple to identify which row belongs to which file.
Going forward, if you add more files to the source folder, it will automatically start reflecting in the power bi report after the next refresh.
Using this ‘combine’ feature, you can connect power bi to one folder & keep on adding more similar files to it without ever needing to append it manually. A simple use case can be putting monthly sales files in a single folder.
Pro tip: If your folder has a lot of different files & you want to only use files that are relevant to the table, you can filter the list based on the file’s name, type etc before combining them together.
Karan Nair
Team Lead – Data Analytics
Addend Analytics