power query help

[northernwarrior]

Hi, I have a folder with multiple files which are timesheets for the week for a group of employees. Each file is 1 week.

I'd like to merge all of these files to ultimately have 1 file with the employee number, last and first name in the first 3 columns and the dates in a row. I only need the columns under the date "CODE" ( vac,s,T, DO, etc)

so it would roughly look like:


April 7 april 8 april 9
127091 Jaxton McClain vac S
145809 name name DO T

I've included 2 sample files, but ultimately there are roughly 50 sheets (Jan-Dec)

I've tried playing with power query, but I can't seem to figure it out.

If someone could possibly transform the 2 files into 1 file, I could try and firgure out what you did, so I could expand.

Hoping someone is able to assit.

Thanks in advance,

Ron

[AliGW]

OK - so I've had a look.

You are going to need to be MUCH more specific about where in these source files the data is coming from - tell us the ranges.

You should also MANUALLY mock up a results file for the data that you want from these two workbooks - what you have dumped in your post is far from clear, I am afraid.

[northernwarrior]

Here is a small sample, but should give an idea of what I would love to see.

Little context: I want to be able to view 1 file and see all days with all employees. This would help finding trends without the need to open each file.

hope that helps somewhat.


Ron

[AliGW]

Got to be honest - the layout of the source files is going to make this quite difficult.

I'm going to step aside at this stage as I don't think my PQ skills are quite up to it, but I should add that it's always best to collate data in a completely normalised (flatfile) fashion, and then use formulae and pivot tables to present it in an aesthetic way. It's not good practice to put the cart before the horse.

But you have what you have and it's too complicated for me (layout and three separate tabs in the workbooks). Good luck with it.

[northernwarrior]

:-( Old school way of preparing reports. I'd love to change the format. Could you perhaps suggest a better layout of the data to make this easier ?

[AliGW]

It would need a root and branch reorganisation. As I said, normalised format, which means one row per record. I'm sorry, but it would take too long to put together an example.