Last 5 minutes calculation

Power Query
1

Hello Everyone,

I am trying to do a calculation to get the last 5 minute of work done when my dataset is refreshed using power query.

My data table contains Date | Total hours I Time

I have tried using the custom Colum with an If statement but still getting errors.

Expected Result : if work done is within the last 5 minutes return “Yes”, else “No”

@Melissa

I have tried this formula but getting an error

if [Total Hours] + [Time] > DateTime.LocalNow -5 then “Yes” else “No”

Time Log.pbix (17.0 KB)

2

Hi @EmmanuelBassey,

Don’t understand your requirement so based on the supplied sample, also include the expected result.
Thank you

3

Apologies, I didn’t include that.

I want to check if the work done was in the last 5 minutes return “Yes” else “No”

@Melissa

4

Hi @EmmanuelBassey,

Thanks for confirming that.
So for the Now I’ve used DateTimeZone.FixedUtcNow() because UTC is the default in the PBI Service, you can adjust that to your time by adding or subtracting hours. The example below shows how to add 1 hour to UTC time.

I’m assuming there can’t be future datetime values. Meaning all combined [Date] & [Time] values greater than Now - 5 min will evaluate to true. Adjust accordingly.

let
    Now = DateTime.From( DateTimeZone.FixedUtcNow() + #duration( 0, 1, 0, 0 )),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDsQwCEPvknXVggn5+CpV73+NIVnMSFO8AFl6Mk7uu9gFXBCgHEXOab7UwqiwgtXKc/xTKrMD24BWWWcCybCtDbnTkEEi6KEwTpmEvyA9u/nahDKavUN6160w0SVyJIHcv53HephrBrVmI7STvFOzzjXmpDg9u4Nfmcn4w3Xn+QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Total Hours" = _t, Time = _t]),
    ChTypeWithLocale = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
    ChType = Table.TransformColumnTypes(ChTypeWithLocale,{{"Total Hours", type number}, {"Time", type time}}),
    InLast5Min = Table.AddColumn(ChType, "In last 5 mins", each [Date] & [Time] > (Now - #duration( 0, 0, 5, 0 )), type logical )
in
    InLast5Min

I hope this is helpful

5

Thanks.

Not really deep in M. Thought there is a lay man’s way of manipulating the answer😒

6

Hi @EmmanuelBassey, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

7

Hi @EmmanuelBassey, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.