Power Query / M - If statement with Duration

[Anonymous]

Hi all,

I'm looking for a bit of help converting a column built with DAX into PowerQuery. 

Time for next period = if('Master Append'[EndTime] > 'Master Append'[Interval End] , datediff('Master Append'[Interval End],'Master Append'[EndTime],SECOND),0) 

Any help would be greatly appreciated! 

Kind regards,

Jordan 

[Anonymous]

I don't know the DAX sintax at all, but for what I can understand, would suggest to try this:

#"Time for next period" =List.Max({Duration.Seconds(#"Master Append"[Interval End]-#"Master Append"[EndTime]),0}

If you want, as seems from picture, add a new column with this data you shoul put the left right of the expression on the tool

add custom tool

 

[Anonymous]

@Anonymous no luck unfortunately  

[Anonymous]

could you explain better what you mean by no lucky?
if you want useful help, you have to provide maximum information, otherwise it is difficult to imagine what happens

[Anonymous]

Hi @Anonymous ,

Your M below has not worked, but I believe it needs an if statement in there. I would expect something a bit more like "= if([EndTime] > [Interval End] then [Interval End]-[EndTime]else 0)" would be the answer I need? 

Kind regards,

Jordan 

[Anonymous]

Hi @Anonymous 

you didn't explained what error my code raises.

The logic of the expression I give is the same of your if ... then .. else. Only written in a differente way.

But now I have seen an error in the sintax

this

List.Max({Duration.Seconds(#"Master Append"[Interval End]-#"Master Append"[EndTime]),0}

should be

List.Max({Duration.Seconds(#"Master Append"[Interval End]-#"Master Append"[EndTime],0})

[Anonymous · Accepted solution]

solved this, seems the brackets used were not allowing me to complete the M code.

Solution was = if [EndTime] > [Interval End] then Duration.Seconds([EndTime] - [Interval End]) else 0