Happy Birthday Power Query - Excelerator BI

Happy Birthday Power Query

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

10 thoughts on “Happy Birthday Power Query”

  1. The PQ Team is Brilliant and the most Agile team in Microsoft.
    Miguel if you are still there – great work !
    Below things would make it awesome
    1. Get a Modern Editor – Intllisense, Syntax highlighting, Auto Indenting, Line Nos, Batch Export/Import to/from .pq files
    2. Remove Case sensitivity – This is so fustrating
    3. Please don’t stop feedback via [email protected] – The last mail I sent bounced back

  2. Can’t agree more with you that PQ is such a powerful tool in Excel; but got unnoticed, un-promoted, and even un-discovered.
    I hope more people would pick it up soon. 🙂

  3. There are three links on the Getting Help section of this web page:
    https://support.office.com/en-us/article/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045

    The second one gets you to the download page of Version 1.0 of PowerPivot. Date Published: 2/20/2013
    “Microsoft PowerPivot for Excel 2010 and PowerPivot in Excel 2013 Samples”

    The third one gets you to the download page of Version 11.0.5635.3. Date Published: 11/29/2015
    “Microsoft® SQL Server® 2012 SP2 PowerPivot for Microsoft Excel® 2010”

  4. Lynette Lowrance

    I love Power BI, but I adore Power Query and Power Pivot. I use them in Excel extensively. And when I can’t get something to work in Power BI, create it in Excel and copy it Power BI.

  5. Couldn’t agree more. Taught an advanced class at a majorboil refinery yesterday and strongly encouraged the attendees to investigate Power Query/Power Pivot in Excel 2016. The org is moving to Power BI and I explained to these users that they can get a head start on their learning by learning Power Query from within Excel. They will have a head start and the learning is completely transferrable to Power BI.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top