In this video, we will show you how to append multiple Excel files from one folder in Power BI using Power Query.
Finance professionals regularly put together weekly and monthly reports. When a new month comes out, many still do it manually. They copy and paste the new month data into one big master file.
This may take up a lot of time and lead to errors. Especially when you are working with large amounts of data and several Excel copies. Instead, we recommend using Power Query to speed up the process and reduce manual errors and typos.
Watch the video below to learn how to append multiple Excel files:
To import multiple files from a folder into Power BI, first, click “Get data” and select a folder. You only really need the content column from the files in the folder, so other columns can be deleted. Once you have the content column, build a template query whose logic will be applied to all other files added to the folder later.
Use the following M code to create a custom column: =Excel.Workbook([content])
Upon creating the new column, select only the Data box and import tables. Sheets or flat files, if your data isn’t formatted as tables. Click on the expand filter button to import data from all the columns. Do whatever cosmetic and formatting steps you need before loading the data into the Power BI report view.
After a report is ready and you get a new month file next time, simply drop the new file into the folder. Go to Power BI and hit the refresh button. Data from your new month file has been appended and is already included in the main report.