Forum Discussion
Loading data for multiple months
Hi folks,
I will be recieving the below attendance data each month in a separate excel file. how do I automate the ingestion for all months, considering each file is going to have different dates in columns . needyou help
| SL # | Name | Emp ID | Tower | Team | 1-Aug | 2-Aug | 3-Aug | 4-Aug | 5-Aug | 6-Aug | 7-Aug | 8-Aug |
| 1 | aaa | 123 | PTP | MDM | Working | Working | Working | Working | Working | Working | ||
| 2 | vv | 2424 | PTP | MDM | Paid Leave | Working | Sick Leave | UPL | Sick Leave | Working | ||
| 3 | vvv | 2432 | PTP | MDM | Working | Working | Working | Working | Paid Leave | Paid Leave | ||
| 4 | vvv | 42424 | PTP | MDM | Working | Working | Working | Working | Paid Leave | Paid Leave |
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.
- Put all monthly Excel files into one folder (one file per month).
- Load from Folder in Power BI / Power Query.
In the combined query,:
- Keep the fixed columns (e.g., SL #, Name, Emp ID, Tower, Team).
- Unpivot the date columns (the columns like 1-Aug, 2-Aug, …) into two columns: DateHeader and Status.
- Convert the DateHeader (e.g., "1-Aug") into a real Date column (Date).
- Optionally derive Year/Month if you want a proper date dimension to join to a Date table.
- 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 transformationIdentify 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.
8 Replies
Hi datamp - Use Power Query to create a dynamic solution that handles variable date columns and appends all files automatically. eg., Save all monthly attendance Excel files in the same folder (e.g., C:\AttendanceFiles\).Make sure files follow a consistent structure.
In Power query editor , use get data >> folder option to extract all the files and combine the files into one file>> use unpivot other column transformation on dates (1-Aug, 2-Aug,3-Aug and so on) by selecting this columns.
you will see attibutes and values . Hope the above process works
if possible you can also provide sample data will do it and share the output.
Thank you.
Thanks for your prompt response. I selected the common column like emp id and others and then chose unpivot other columns, the problem I ma facing is the Sep mont column names are in rows and not columns
You need to have them in the rows - Power BI needs long skinny tables, not wide and short. You can have the dates in columns in a visual if you want, but the best format for ingestion is what rajendraongole1 suggested - making them skinny and long.
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.
- Put all monthly Excel files into one folder (one file per month).
- Load from Folder in Power BI / Power Query.
In the combined query,:
- Keep the fixed columns (e.g., SL #, Name, Emp ID, Tower, Team).
- Unpivot the date columns (the columns like 1-Aug, 2-Aug, …) into two columns: DateHeader and Status.
- Convert the DateHeader (e.g., "1-Aug") into a real Date column (Date).
- Optionally derive Year/Month if you want a proper date dimension to join to a Date table.
- 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 transformationIdentify 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.
Thanks for your help mate
Hi datamp ,
Thank you FarhanJeelani for your inputs.I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.
Thank you.
Hi datamp ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.
