Match severals excel with differents number of columns

[Romain_L]

Hello everybody,

I want to merge differents excel woorkbooks in a power query but i have a differrents number of columns.

see example below :

My problem is that in power query, the columns number 5 (E in excel) will contain 0 in the Excel#1, a TOTAL in excel#2 and the qty of banana in the excel#3

Do you have a solution for this ?

Regards,

Romain

[PijushRoy]

Hi @Romain_L 

I have created two sample table and try to same job you want.
Please find the attached pbix file.  Please follow highlighted 3 table and do same step in your file

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[Romain_L]

Hi, 

Thanks for the aswer, yes it's working in that case but, the thing is that i have like 80 files like excel 1 and i need to be adble to add new excel in the folder and powerbi will upgrade automatically, thats why i don't want to charge every excel files but the entire folder.

Regards,

Romain

[PijushRoy]

@Romain_L 

When you uploading folder, my suggestion is you need to standarize the excel format for now and for future. So it will not create any issue for you.
Is excel generated from any software and manual fitted excel

Please fix the excel format like
FruitsName, Month, City of Apple, City of Benana, City of Orange, Total...

Please let me know

If solve your requirement, please mark this answer as SOLUTION

If this comment helps you, please LIKE this comment/Kudos

[Romain_L]

To be more precise,

In my case, my company have a badge reader that count every person going inside the construction (using badges).

And every day this software generate automatically thoses excel files.

So to take back the exemple, 

City is the company of the person going inside

Months are days

Fruits are statuts of the person (worker, foreman, ingeneer, client, other...)

So i can't change the new excel files and i already have hundreds of old files that i need to regroup.

i know that changing the excel will be way easier but sadlly i can't.

regards,

[Romain_L]

the main problem is that if there is no foreman the monday, i will not have the column "foreman" but if i have a foreman the tuesday, the software will create  the column and push the other columns on the left

[Romain_L]

as an exemple 

[Arul]

@Romain_L ,

Please through your sample data in accessible format.

Thanks,

Arul

[Romain_L]

the site does not allow me to add excel files, i don't kow how to do