Merge excel files and excel worksheets

[ricod]

Hi,

i've need to import a monthy excel reports file, in each file i've 9 identical worksheets structure representing employe planning of the file month. i want to agregate the files i put in a folder and agregate the worksheets too. To have activities of all month and all employes.

When i import data from folder i can import all the file and worksheets, after that i develop data to show all column but it seem the transform take only the first sheet when for exemple i try to delete 3 first rows.

a bit lost how to do :$ especially if i want to do a unpivot other columns 🙂

Cheers

[Anonymous]

Hi ,

The file connection still won't open. Please re-upload the file.

Best Regards,

Wisdom Wu

[NaveenGandhi]

Hi @ricod 

I have created a sample solution for the mentioned problem, have a look at [Invidual file all sheets] table's applied steps, I have utilised this steps in the tranform file function for t1 table [I altered my existing transform file function which was taking 1st sheet from all files] so that all sheets in all files in the folder in extracted.

Just adding the exapand step in your tranform file would do the job in nutshell.

Let me know if this helps.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[ricod]

Hi @NaveenGandhi here are two sample file  june July . i combine file from folder, transform and after due to date change i'm not able to "merge" then to have full history

cheers

[ricod]

Hello @NaveenGandhi , thx for this but i don't i precised an important point, my column in each files/worksheets are not the same, because there is dates. for example:

in my july files i've 9 worksheets with table for each employe with activities of the month of july

Env.	Project	Project Ref.	Type of work	01/07/2024	02/07/2024	03/07/2024	04/07/2024	05/07/2024	06/07/2024
			Holidays
Prod	uncle	cal	Test						0,75
non-prod			dev			0,25	0,25
			Meeting			0,25
prod	junior	deb	implementation			0,25	0,5	1	0,25

in and other file (still in the same folder), i've 9 worksheets with table for each employe with activities of the month august 

Env.	Project	Project Ref.	Type of work	01/08/2024	02/08/2024	03/08/2024	04/08/2024	05/08/2024	06/08/2024
			Holidays
Prod	oto	hum	Test						0,75
non-prod			dev			0,25	0,25
			Meeting			0,25
prod	isi	rac	implementation			0,25	0,5	1	0,25

and when i combine them, i've error due to the dates changed in the planning of each worksheet of the other month.

cheers

[vanessafvg]

please can you provide some examples of the problem and what are you expectations output is?

[ricod]

Hi @vanessafvg , here two example files 🙂 

june july 

i've find this Power Query Combine Files with Different Column Names - Super EASY! (youtube.com) but i'm not able to reproduce it

Please help 🙂 

[NaveenGandhi]

@ricod 

Can you provide access to these files.

Thanks,
NG

[ricod · Accepted solution]

hello @NaveenGandhi , should be ok now 🙂 thanks