Timing M Query Execution In Power Query And Power BI (Revisited)

Some time ago I wrote the following post on how to work out how long your M query takes to execute in Power Query:


While it’s still relevant for Power Query and now of course Power BI (and it also illustrates some interesting facts about query execution), recently I had an interesting discussion with Colin Banfield in the comments section of that post that led to us finding an easier way of measuring query execution times.

In M, the DateTime.LocalNow() function returns the current system date and time at the time the function is called – which means that you could call it multiple times in the same query and get different results. There’s also the DateTime.FixedLocalNow() function which also returns the system date and time; the difference between the two is, as the documentation says:

This value is fixed and will not change with successive calls, unlike DateTime.LocalNow, which may return different values over the course of execution of an expression.

The question here is, though, what time does DateTimeFixedLocalNow() actually return? I asked on the Power Query forum here and Ehren from the Power Query dev team revealed that it returns the system date and time at the point when the query begins.

This means that it can be used to simply the original method I used to find query execution. Here’s a query that uses Function.InvokeAfter() to create a delay of 5 seconds during query execution and returns the difference between the values returned by DateTime.LocalNow() and DateTime.FixedLocalNow():

   SomeFunction = ()=> DateTime.LocalNow(),
   InvokeAfter5Secs = 
      Function.InvokeAfter(SomeFunction, #duration(0,0,0,5)),
   Output = 
      Duration.ToText(InvokeAfter5Secs - DateTime.FixedLocalNow())


If you’re using this on a real query I strongly recommend you read my original post carefully and make sure that all of the steps in your query are executed, but does make things a little bit easier.

8 responses

  1. Pingback: M Query Timing – Curated SQL

  2. Pingback: Understanding Let Expressions In M For Power BI And Power Query – Chris Webb's BI Blog

  3. Pingback: Relationships, Timing and something free – Roundup #41 – Guy in a Cube

  4. Pingback: Power BI Diagnostics, Trace Logs And Query Execution Times (Again) – Chris Webb's BI Blog

  5. Pingback: Finding Power Query Query Execution Times In Power BI And Excel Using SQL Server Profiler « Chris Webb's BI Blog

  6. Pingback: Relationships, Timing and something free – Roundup #41 – 360reports.io Power BI Videos

  7. Pingback: BTS: What’s in my bag? | April 2016 – 360reports.io Power BI Videos

Leave a Reply to Kawabata Yoshihiro Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: