When you select "Combine & Transform", PQ generates a number of queries for you. They are listed in the left hand side of the editor and could like like this.

The 'Transform Sample File' usually contains data from the first file in the folder only. But the one in the "Other Queries" section (shaded green in the screenshot and)  should contain data from all files in the folder.

If that is not the case for you, please share some screenshots that show what you are seeing.

Hi Cristopher,

Combine and Transform is not a good way to go, it's not yet flexible enough.

See this post for a more flexible solution:
https://www.myonlinetraininghub.com/excel-forum/power-query/how-to-remove-duplicated-headers-when-importing-from-folder#p16593

Thank you for your reply.

I found out what I was doing wrong.

I had moved all the data I needed from one vast Excel workbook into a separate workbook - with one file for each month of this financial year.

My mistake was to use the same worksheet and save each with a different filename. What I didn't realise was that each worksheet in each workbook has to have exactly the same name. While my were all just one sheet, I managed to call some of them "sheet1" and some of them "sheet 1" - Eurika moment when I realised my error(s).

Your suggestion for a more flexible solution looks like it can be something to incorporate in the coming months as we decide how much data we need to keep.

At the moment each spreadsheet is 23 columns wide - too much to fit on one screen without making it about 80%.

May I ask for a bit more detail as to where to put your suggested text/file? My data is in the C drive, desktop, datafile with each of the monthly file in that folder.

Thanks

Chris

Excuse my butting in.

I read your last entry, and you say your file is saved in the C desktop.

I always tell everyone to avoid storing actual files on the Desktop. It's part of your profile for that particular system and the larger the profile folders get the slower your system will start.

I always use a a entire separate folder outside the user's profile folders or even better an extra drive.

If you C-drive is large you can partition it and creating one or more new partitions depending on how large it is.

That way for can use drive D as a 'data drive' and place all your file in relevant sub-foders, first it's easier to share and next it's maintainable and if it comes to a new installation of the OS for whatever reason the C drive may be overwritten but the D-drive remains.

 The desktop profile issues play especially a big role when it comes to network profiles which are loaded every time you log onto a new system

Hope my explanation makes sense.

Happy coding 🙂

Thank you so much for "butting in" as you put it - Never a problem.

Always glad to have some advice.

Great advice.

I only used the desktop as I was begining the exercise and wanted to be able to find it quickly. I have now moved it into a folder on our Z drive which is for our network of four computers.

Best wishes

Chris

I wonder if you can help with this new little "niggle"

I follow all the steps, make pivot tables but when I "refresh all" i get an error which says "expression.error The column of region/school was not found. However, none of my columns has such a heading. This morning I even went through each of the 20 files and copied and pasted the same heading into each sheet across att 23 columns.

Does it make a difference that I have made each datasheet a table? Should I return them to ranges rather than tables?

Thank you in advance

Regards

Chris

Hi Chris, about the file on Z-drive, what you can do is place a link (shortcut) to that file on your desktop, that way it's always there another option is create a folder on your desktop and place all shortcuts in this folder, doesn't take much (disk)space.

about the ranges being tables, I'm not that hot with Pivot tables but as far as I can see/know is that a table (ListObject) makes it easier to address

Maybe the refresh cache contains some residual previous connections/references.

Have you tried recreating the pivot table from scratch?

Not sure why this discussion is side-tracking from your real Power Query issue. The "Expression.Error: The column of region/school was not found" message suggests that somewhere in your query(ies) there is a step that looks for a column called "region/school".

If that column no longer exists in your data files/tables, PQ will throw this error and can't re-load the data and pivot tables will not be refreshed. So, rather than copying and pasting headers in all your files, look for the query step(s) where a column "region/school" is(are) targeted. Understand what it is supposed to do and correct it if still needed by referencing the correct column name or delete the step. Save and rename your file before making any changes, so that you can always go back to how it was before.

Strålande. Tack så mycket.

The original "issue" was solved by making sure each sheet had the same format: "sheet 1" and not a mixture of "sheet1" and "sheet 1"...

Job for today: work through the query to find the naughty bit!

Have a good day

Chris

Tracking and fixing hard typed column names in Get and Transform is a waste of time, will never make it flexible. When a new file will be added in the folder, it will crash again.

Try the attached file, all you need to do is to change the folder path in Settings sheet and refresh the query.

Will combine all xlsx and csv files from that folder, zero hard typed column names, no hard typed sheet names.