Calculate datediff of many status in a events table in M code

[Anonymous]

Hi all

I got this thread (https://community.powerbi.com/t5/Desktop/Calculate-datediff-of-many-status-in-a-events-table/m-p/741...) were I got a solution with a DAX measure. It worked very well and I could use it for all the commands needed but as the DB got bigger it is very slow and I got limitations to make calculations in a row level. I think this would be better to be done in query editor with M code. Please could somebody givi me a hand to get a solution this way?

Cheers.

Pepe

[v-eachen-msft]

Hi @Anonymous ，

According to your oringin DAX, I get the result with m query.

I uploaded my test file, you can download and refer to it.

[Anonymous]

Hi @v-eachen-msft 

Very much appreciated your effort. This gives a solution to the sample provided, but the way you get the dates with the megerType won't work in the whole table.

There are several different types of commands and not always the command to star the procces is the number before the command to end the process.

We've got a porces that stars with commands 27 (deploy fro postpro) and end with command 4 (import mastered audio).

Apart from the commands, the description field identifies different process of the same type. Meaning that the Commands 9 should get the duration from commands 8 that matches the description.

Could the "merge type" step be done with a conditional to match pairs of commnads (not consecutive) and that matches also a 3rd value like description?

somethingt like this:

= Table.AddColumn(#"Changed Type", "mergeType", each 
              if [CommandType] = 9 then 8
             else if [CommandType]=6 then 5
             else null)

but it needs to macht the description too.

Cheers

Pepe