How to calculate the duration in M or other language

[martinhyy]

Hi community,

This is my first post. I'm just wondering how to use any language to track the time that is used to query the data.

Concretely, I've got a query purely in MDX, when I choose a certain date range and load it directly to Power BI, it takes about 7mins to finish the loading (by looking at the clock). However I would like to know the exact time that is spent on the loading process.

I imagine there should be some kind of code

let 
   
    start_Time = DateTime.LocalNow()
   
    source = /*my MDX code here*/

    duration = DataTime.LocalNow() - start_Time

in
   
    duration

The code above always gives me a duration that is really close to 0 which probably means it didn't run the source part? Any ideas how I can make this correction?

Thanks

[Eric_Zhang · Accepted solution]

Measuring execution time in Power Query is not easy, especially because of the answer to your last question: no, execution order is not guaranteed from top to bottom. Instead, Power Query uses lazy evaluation, i.e. code won't be evaluated until it is actually required.

You may best be helped by this blog from Chris Webb in which he explains how to use Diagnostics.Trace to measure execution time.

---

See the reply from @MarcelBeug in Power Query - log start and end time of running a query