Forum Discussion
Folder files with date as column names
Hi, I am trying to set up folder as data set that has reports from agencies. The problem is that provided reports have working hours column names in days Monday - Sunday. I can change those days to date what allows me to see hours by day if I unpivot those columns. Problem appears when there is more than one report in the folder. Everything breaks because every new report is for different week so day date would be different. And power query combines them in to one table, what is what I want but it does not separate different date columns and appends them. All the new report data gets append to the bottom of the first file.
I know if I change the name of the columns back to Mondat - Sunday it will work, but then I wont be able to tell which hours are for which days. Anyone know any working solution. I can provide sample data if needed. I attach screenshot of one of the report files.
Thanks.
From what I have tried this solution seems to have worked for me.
https://www.youtube.com/watch?v=tpK_xklbDf0
This is blog version of the video https://www.myonlinetraininghub.com/combine-files-with-different-column-names-in-power-query
3 Replies
If the date is part of the filename (or if there is a created/modified date column), you could dynamically rename the columns in each file with the correct dates for that week. Then you can unpivot the date columns so you can combine the data from all the files.
Pat
ppm1 There is no other columns that have any date data. And the name of the files from what I have seen are without dates or it is just a week of the year.
From what I have tried this solution seems to have worked for me.
https://www.youtube.com/watch?v=tpK_xklbDf0
This is blog version of the video https://www.myonlinetraininghub.com/combine-files-with-different-column-names-in-power-query