What if I told you there’s this super powerful, amazing feature in MS Excel that no one seems to know about. What if I told you it’s not even a feature, more like a whole other programme hidden inside of Excel. A programme that I wish I’d discovered years earlier because of the time it would have saved me. A programme I’m now in love with.

I present…

PowerQuery is a powerful tool within Excel that allows users to easily combine data from multiple sources, clean and shape data to fit your needs, and even perform complex transformations on the data with just a few clicks.

One of the key advantages of using PowerQuery is its ability to connect to a wide range of data sources, including files, databases, and even web services. Making it easy to access data from a variety of sources and combine it into a single, coherent dataset within Excel. For example, at PBH we use this ability to combine multiple sources by merging hundreds of Excel spreadsheets found in several subfolders into one single master data set, which we then use for a subsequent automated workflow.

PowerQuery also offers a number of tools for transforming and shaping data to fit your needs. For example, you can use PowerQuery to split columns, merge tables, and even perform advanced transformations like unpivoting data (unpivoting makes you feel like you weild superpowers 🦸). These tools make it easy to clean and shape even the most awfully formatted data so that it is ready for further analysis in Excel.

What makes PQ so powerful is that it creates repeatable workflows. I.e All the wonderful features described above can be sequenced into a routine that can then be run whenever you want, or automatically at set times. This makes any kind of scheduled reporting or analysis a doddle, as you can have PQ do the heavy lifting for you in a reliable manner. You can think of PowerQuery as being like a tool to create powerful VBA Macros without writing a single line of code. It is all drag and drop.

For those advanced users not scared of a little scripting, PowerQuery offers a powerful scripting language called M, which allows users to perform complex transformations on their data. With M, you can create custom functions and expressions to transform your data in powerful ways. This makes PowerQuery an excellent tool for high level data wrangling and preparation, as it allows users to easily manipulate data to fit any needs.

And to hammer home the point. All this functionality is built right into Excel. No additional downloads needed or costs incurred. You just need to know where to find it! (I reccomend watching some YouTube tutorials to get up to speed!)

Overall, PowerQuery is a highly valuable tool for anyone working with data in Excel. With its ability to connect to a wide range of data sources, its powerful data transformation tools, and its powerful scripting language, PowerQuery makes it easy to access, transform, and integrate data from a variety of sources within Excel.

Finally, PowerQuery is built right into PowerBI, so learning it gives you a 2-for-1 in terms of up-skilling your toolbox.

Check it out. It’ll save you countless hours.