Get our whitepaper on form submission right to your inbox!!!
Microsoft power bi has offered numerous ways of importing different types of data sources into your data model. One of the predominant ways of importing data is, importing it as a source of Excel.
Recently I have been working on a dashboard which imports monthly sales data. Each month a system-generated Excel is imported to the data model. The main necessity of the model is that the sales table should be competent to store monthly data for the past couple of years and future months by combining multiple excels. The simplest way to accomplish this is to maintain a SQL database.
Having said that, the major problem that I have confronted is, my client is not a database enthusiast, and rather preferred data to remain in excel files. In this case Microsoft Power bi has provided an amazing way to import multiple excel files to a single table of your data model. In advance this method can be extended to import multiple excel files with any number of sheets in it. But the key rule to remember is, all the excel files should be in the same format.
Here is a step by step guideline to achieve this.
Below is my sample Excel file for a specific month. It contains sales data of two regions in two sheets in the same format.
Step 1: Place all the monthly excel files in a folder. Here I place all my monthly sales data in a folder called Sales
Step 2: Open power bi and select “get data” option in the main ribbon
Step 3: Under the get data select on the Folder option. Next click on the “connect” button
Step 4: After clicking the connect button you will be asked to give the path to the sales folder. Give the path by browsing to the folder location and the click ok
Step 5: Dialog box will show the details of all the files in your folder. Click on the down arrow of the combine button and select “Combine and Transform Data
Step 6: The following dialog box indicates the sheets available in a single Excel file. In here there are two sheets called “Kandy’ and “Colombo”. Click on top of each sheet to view data on it. Right-click on the Parameter  folder highlighted in the screenshot below and click on transform. Then the edit query window will open where we can do the transformation to our data.
Step 7: Under the Queries pane at the left side there are two folders namely, Transform File from Sales  and Other Queries. Under the Transform file from the Sales folder there is a table called Transform sample file. When you click on top of that file you can see the details of each sheet. In here we can do all the modifications to each sheet.
Step 8: Do the necessary transformations to each of the sheets. The steps applied in transforming data are shown in the “Applied Steps” pane on to your right side. Then the sheets should be appended together. Click on the Append queries to append two sheets.
Step 9: Below dialog will be shown to append quires. Here we are going to append only two sheets. So select Two tables only. In the drop down you will see the Transform Sample file table. Select that and click on OK
Step 10: You can see the same sheet has been appended twice. Then go to formula bar and change formula necessarily as shown below.
Step 11: Now you can see that both the sheets have been appended
Step 12: Then move to the actual table of the data model under the “Other Queries” folder. Then you will see an error message as shown below. This happens because we have changed the types before in each of the datasheets. Therefore remove that step by clicking the cross mark
Final result: Now you can see the Sales table combining all the sheets and all the files of the Sales folder. Here Source. Name Column automatically generates to indicate the file name of the folder.