Loading data for multiple months

Hi datamp ,

The most robust way to ingest monthly attendance files (each with different date columns) is to load them from a single folder and transform them in one go. Power Query can automatically apply the same transformation to every file, unpivot the date columns, and convert the date headers (like 1-Aug) into proper dates.

You can try below steps.

1. Put all monthly Excel files into one folder (one file per month).
2. Load from Folder in Power BI / Power Query.

In the combined query,:

1. Keep the fixed columns (e.g., SL #, Name, Emp ID, Tower, Team).
2. Unpivot the date columns (the columns like 1-Aug, 2-Aug, …) into two columns: DateHeader and Status.
3. Convert the DateHeader (e.g., "1-Aug") into a real Date column (Date).
4. Optionally derive Year/Month if you want a proper date dimension to join to a Date table.
5. Refresh will automatically ingest new monthly files, using the same logic.

Step-by-step outline (Power BI / Power Query)

Put files in a folder

Create a folder

(for example: C:\Attendance\Monthly) and drop each month’s Excel file there.

Connect to the folder
In Power BI Desktop > Get Data > Folder.
Browse to your folder and click Combine > Transform Data.

In the Sample File / Combined query, define the transformation

Identify fixed columns (these don’t get unpivoted): e.g. SL #, Name, Emp ID, Tower, Team.

Unpivot the remaining columns (these are your date columns like 1-Aug, 2-Aug, …).

Example (conceptual M code inside the “Transform File” function that Power Query creates for you)

The exact code is generated by Power Query, but you’ll typically end up with something like:

Unpivot:

Keep columns: {"SL #","Name","Emp ID","Tower","Team"}
Unpivot other columns to: Attribute (this holds the header like "1-Aug"), Value (the attendance/status)

Parse the header into a real Date:

Add a column DateHeader = [Attribute] (trim as needed)
Split DateHeader by "-" to get Day and MonthAbbrev
Map MonthAbbrev to a month number (Jan=1, Feb=2, …, Aug=8, etc.)

Decide on Year:
If you have the year in the file name (recommended), extract Year from [Name] or [Source.Name].
Otherwise, use the current year or a parameter you set per file.
Create a real Date column:
Date = #date(Year, MonthNumber, Day)
Clean up and rename:

Remove the temporary header column (Attribute or DateHeader)
Ensure Status values are clean (trim, normalize spaces)

Please mark this post as solution if it helps you. Appreciate Kudos.