Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.6 Relationships Across Multiple Files (Power Query from a Folder)

By Last updated:

Example first. Every month the Pune team saves a new orders file in one folder: Orders_Oct.xlsx (6 rows) and Orders_Nov.xlsx (6 rows). Instead of copy-pasting, Get Data › From Folder combines all files into one Orders query (12 rows), loads it straight into the Data Model and keeps the same relationships. Next month, drop Orders_Dec.xlsx in the folder and click Refresh.

Combine files from a folder into the model — in 30 seconds

What: Power Query reads every file in a folder, stacks them into one table, and loads that table into the Data Model with "Only Create Connection". The dimension tables can come from other files too.

Why: Data spread over many files becomes one fact table in the model, and new months arrive with one Refresh instead of manual copy-paste.

Tiny example: Folder with 2 files → one query of 12 rows → Oct ₹1,005 + Nov ₹1,310 = ₹2,315 in the model.

First look at the data (before → after):

Combine files from a folder into the model: before and after tables, fictional Blinkit Maharashtra data

Expected result — Query Orders in the Data Model (Source.Name column added)

Source.Name Rows Amount (₹)
Orders_Oct.xlsx 6 1,005
Orders_Nov.xlsx 6 1,310
Total 12 2,315
Before table and formula as text

Before — Folder: D:\BlinkitPune\Orders

File Rows Amount (₹)
Orders_Oct.xlsx 6 1,005
Orders_Nov.xlsx 6 1,310

Formula / click path

Data › Get Data › From File › From Folder › pick the folder › Combine › Combine & Transform Data
Power Query Editor › Home › Close & Load › Close & Load To…
Only Create Connection + tick Add this data to the Data Model › OK

Do it step by step:

  1. Put all monthly files with the same columns in one folder.
  2. Click Data › Get Data › From File › From Folder, choose the folder and click Open.
  3. Click Combine › Combine & Transform Data, pick the sheet or Table in the sample file and click OK.
  4. In the Power Query Editor, check the data types (Date as Date, Qty and Amount as Whole Number).
  5. Click Home › Close & Load › Close & Load To…, choose Only Create Connection, tick Add this data to the Data Model, and click OK.
  6. In Diagram View relate the new Orders query to Stores, Products and Calendar as before.
  7. Next month, copy the new file into the folder and click Data › Refresh All.

Things that break a folder query

Problem What happens Fix
One file has an extra or renamed column That column comes in blank or the refresh fails Keep one template for every month
A temporary file such as ~$Orders_Nov.xlsx is in the folder (the file is open) Refresh error Close the file, or filter out names starting with ~$ in the query
Someone saves a .txt notes file or a photo in the folder Combine fails Filter the Extension column to .xlsx
Folder path changes Refresh cannot find it Edit the Source step in the query

The same idea in Power BI is taught in Get Data from a Folder.

Practice task (try it before you open the answer):

  1. Split the Orders table into two files by month and save them in one folder.
  2. Combine them with From Folder and load to the Data Model only.
  3. Relate the query and build a PivotTable of Source.Name by Sum of Amount.
Answer

Orders_Oct.xlsx ₹1,005 (6 rows), Orders_Nov.xlsx ₹1,310 (6 rows), total ₹2,315.

Ravindra Bagale's Tip

Keep the "Source.Name" column. When a number looks wrong, it tells you at once which monthly file the row came from.