Yesterday morning I lost almost 3 hours trying to do something with Power Query.
I don’t think it was really designed with what I was trying to do in mind!
Basically, I had weekly reports that I was trying to combine into one table, but every single report needed a load of processing to make it look right; rows removed, columns removed, dates added where there were no dates before, based on the range given in a single cell. It was enough of a struggle getting it to do everything in the sample data set, but when I tried to refresh it so it did the same steps for all of the files, oh boy!
So the experiment didn’t succeed in the way I hoped, but I still don’t count it as a failure.
To get Power Query to do what it had, I had to learn a lot of different ways of manipulating data within Power Query. I learned how a lot of the features work and I learned some of the limitations along the way.
The lessons I gained from failing to succeed were worth the 3 hours, even if I could have accomplished the results I want using VBA instead.
So maybe don’t use Power Query for everything, but definitely challenge, break it and bring it to its knees. You should find out what you can do with the tools available, and more importantly, what they cannot do.