Power BI gets a lot of love – and it deserves it. But under the hood you will probably know that there are 2 technologies that are the true work horses that prepare the data for use. Those 2 products are Power Query and Power Pivot. Power Query is the data acquisition software that goes and grabs, reshapes, cleans and prepares the data to be loaded. Power Pivot is the data modelling software that takes the loaded data and prepares and enhances the raw data for use and reuse inside Power BI and Excel. Both Power Pivot and Power Query were first built for Excel and then the technologies were ported to Power BI. The products are also part of SSAS, but I am not sure of the timings of this.
Power BI is almost 3 Years Old
Power BI is almost 3 years old (later this month). I didn’t always love the screaming baby in its first year, but as it has grown up to be a toddler I have learnt to love it. But the 2 technologies under the hood are much older than 3 years old.
Power Query is 5 Years Old
I wasn’t actually sure when Power Query was born, so I sent a note to Miguel Llopis at Microsoft and asked him. I am pretty sure Miguel has been working on PQ since almost the start. It turns out the “birth date” for Power Query was 6th July 2013, right on 5 years ago. Here is the original announcement that Power Query had been made “Generally Available” https://blogs.msdn.microsoft.com/dataexplorer/2013/07/06/data-explorer-is-now-microsoft-power-query-for-excel/
I really want to spread some love here to the PQ team. This product is SUCH a great tool. Reasons I love PQ include:
- Any Excel user can learn the tool from the user interface
- It is designed to make repetitive low-value-adding tasks fast and easy to execute, over and over again.
- It has a very powerful language under the hood that can be used to perform very complex advanced manipulations.
- It is completely transferable between Excel and Power BI.
- It has Intellisense to help you write native M language queries. Wait, What?! Sorry, my mistake! No it DOESN’T have Intellisense to make it easy to write M language queries, but I am sure someone at Microsoft is working on that! Surely! Surely?! Please please please do this Microsoft as Intellisense will launch this little sucker into the stratosphere.
Actually, there is one thing (apart from Intellisense) that I really would love, and that is for Microsoft to do a better job at marketing this brilliant product. I conducted a training course today with 10 people to teach them Power BI. Only 2 people in the class had heard of Power Query. This is a tragedy in my mind, that such a great product has gone un-noticed to so many Excel users. You can find Power Query “hidden” under the name “Get and Transform Data” on the data tab in Excel 2016.
Power Pivot is 8 Years Old
I wasn’t sure how old Power Pivot was, so I asked Rob Collie. Rob was the original Program Manager on the project. Power Pivot started as Project Gemini in 2007 and was then released originally as PowerPivot in April 2010. Later it was renamed Power Pivot (with the space) due to a trademark issue with Power View (of all things). Rob tells me that PowerView was a trademark of another company, so Microsoft renamed PowerView to Power View, and then renamed PowerPivot to Power Pivot for consistency. If anyone has a link to the birth announcement of Power Pivot, please post it in the comments below.
Thanks Microsoft Gurus
A big shout out here to all the fabulous engineers, program managers and other managers at Microsoft that make this all possible. Keep up the good work