Import Multiple Excel files from Folder

[apati]

I was trying to setup a dashboard and my source data is a daily excel file with multiple columns and sheets.
I do not have real control of the raw/source data file structure so a week ago I realized that the file changed and the new versions of the daily files have 2 extra columns in between. I thought by "training" the Power Query to import the more complex file it could handle the older simpler structure. but instead i either get this error:

An error occurred in the ‘Transform File from new stucture’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table


or it does not even allow me to change the sample file to the new version and it only reads the first file in the folder which is the one with the less columns.

Any suggestions on how to handle this so that I do not loose the history?

thank you in advance for your support.

Best Regards,

Dimitris

 

[sandy666]

Any chance to see structure "old" , "new" and changes?
If I understand correct new files will be with "new" structure only?

 

[apati]

I managed to fix the additional column issue by "re-training" the program to import based on the more complex file.
now, the problem i face is that in the new files one of the sheet names is different vs the old versions ie. sheet 1 vs sheet (1)

any ideas how to tackle this automatically so that i do not have to open and rename the sheet?

thank you in advance for your support.

best,

Jim

 

[Matt Allington]

Power Query M can use a relative reference. So if the line of code says [name=“Sheet 1”] or similar, you can replace the hard coded bit starting from Name and delete it all together, then append {0} at the end. This is code for “first row”. It takes a bit of getting used to.