Power Query Performance Improvements - Test Drive - Excelerator BI

Power Query Performance Improvements – Test Drive

Microsoft has just released the March ‘15 update to Power Query.  On the official blog, MS called out 3 things:

  1. Performance improvements
  2. Microsoft Dynamics CRM Online connector
  3. New transformations

Performance improvements?  You have my attention.  I have found that Power Query (and Power Pivot for that matter) are both slow to load Excel files.  The comparison in speed between a CSV and XLSX of the same data is significant (XLSX is much slower). It seems to me that Power Query wants to “read” the whole file before it starts importing, but with CSV is just starts at the top and gets on with it – well that’s the way I rationalise it anyway.   So I decided to test out the claimed performance improvements in the new release and post the results here.

My Test Data Consolidates 44 Separate Excel Workbooks

I wrote a Power Pivot dashboard and reporting tool for a customer that allows them to analyse key business metrics using time series trend reports etc.  The source data for this report is a standardised stand alone weekly financial report that is produced in Excel.  So in any 1 week, you get a snapshot of what happened that week – which is great – but it doesn’t help you if you want to compare weeks or trends over time.

The reporting tool I wrote uses Power Query to import all the data from these individual Excel files on my PC, and import them into Power Pivot.  I use the trick I blogged about here to do this in case you are interested.  The old version of Power Query before this release took 4 mins and 39 seconds from when I pressed refresh to when the load was complete.  I then downloaded and installed the latest version of Power Query (2.20.3945.242) and ran the test again.  This time it took just 1 min and 23 seconds! 

I had to double check that it had worked properly as I couldn’t believe it.  That’s a 70% reduction in total time to refresh – from 279 seconds to 83 seconds.  Good job Microsoft!

pq update

I hope to see this improvement flow through to native Power Pivot imports of Excel files as the same slow process can be seen in both the 2010 and 2013 versions of Power Pivot.

Here is the “always up to date link” for the latest version of Power Query

 

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/

2 thoughts on “Power Query Performance Improvements – Test Drive”

  1. Anthony Newell

    Hi Matt, this is great news, I’ll be downloading this morning

    I’m curious, with so many workbook sources feeding your model via power query (like me) how do you mitigate against the eventuality that the structure of one of them changes?

    Aside from telling the client that they must ensure the structure of the data sources remains constant (columns, rows etc) I’m just wondering if you have any kind of early warning system in place for the day when one file does change?

    Cheers

    Anthony

    1. Good question. In my case – it simply doesn’t change. I don’t have any warning system but I guess it would start to throw some errors if it did (hopefully obvious ones).

      This customer links a lot of workbooks to other workbooks, so keeping the exact same structure is fundamental to the process. Of course if they could start again and do it using Power Pivot it would be much better – but then again you can’t change where you are. Maybe one day.

Leave a Comment

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

Scroll to Top