Combining spreadsheets

Occasional Contributor

Currently, I have around 60 different spreadsheets with data. But not all the spreadsheets have the same header. I want to combine all these 60 tables of data based on the headers. So lets say 15 have the same header then they get combined as one (and similarly for the remaining spreadsheets). Ultimately for example if the 60 spreadsheets have 4 different headers in total, I want to be able to get 4 different tables. 


I am able to code in VBA so that shouldnt be any problem but from what I understand, Power query is able to combine data like this. So what should I do and how? Would appreciate any help with this matter!

2 Replies
PQ can do a lot, but it is designed to get one "table" per set of files. So you're looking at creating four queries. Is it possible to use something else than the heading to determine which files to use for each heading type? File name or location for example? Alternatively, you could create a query that just fetched the headers from each file so it can determine which files belong in which group. Then you could design a query for each "heading type" and filter the list of files against the first queries' results.
Thanks for the response Jan, will try it out!