Although I think of myself as an Excel expert and there are very many things I can do easily that the average Excel user is dumbfounded by, there are also things Excel can do that I don’t really understand and am still intimidated by.
An example of this are the Power tools than Excel provides, PowerPivot, PowerQuery and PowerBI.
It doesn’t take very much research to find people describing the awesome power that the Excel Power tools provide. Data Visualisations to rival Tableau, data wrangling and slicing with PowerPivot and extracting and transforming data from all different inputs with Power Query.
Although I’ve wanted to learn how to use them, they’ve often felt a bit out of my reach. That is, until last Friday.
While working on a new report and trying to find a simple way to bring in data from several different files, I decided to experiment with the Get Data options on the data tab. After connecting multiple files and appending them to each other, I discovered I was actually working with Power Query all along! At some point over the last few years, Microsoft made it very simple to connect and transform data in Power Query and it’s incredibly easy to just stumble into using it!
Some time ago I met someone who mentioned how using Power Query could replace VLOOKUPs and as interesting as that sounded to me, I was skeptical. Having experimented a little with Power Query myself, quite by accident, I am turning into a believer.
My first impressions of Power Query are very favourable, and I have a feeling that it’s going to help me overcome my fear of, and indeed embrace, the other Power tools in Excel. I can imagine many use cases for PowerBI already, I might even check it out again tonight.