Select Page

VIDEO: Appending Multiple Excel Files From One Folder

by | Apr 17, 2020 | Video

Finance professionals are regularly tasked with putting together weekly and monthly reports for different purposes. When a new month report comes out, many still use a manual method by copying and pasting the new month data into one big master file.
This may take up a lot of time and lead to errors, especially when working with large amounts of data and several Excel copies floating around among team members. To speed up the process and reduce manual errors and typos, Power BI’s query editor comes as an effective solution.

 

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’re left with 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) that were in the folder. 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.

Share This