Calculate time between top line data and TODAY()

[Anonymous]

Hi all,

I have a table which shows the time of a status change together with the status change. I've managed to get the time between the status changes but there's one challenge left: I want to calculate the time between the top line (last status) and the moment of refreshing.

As you can see here: there hasn't been a status change since 12/06 and it's now 16/06. If I make a availibility chart of the last 2 days it shows blank as it thinks it doesn't have data since 12/06. Any idea how to manage this?

Regards,

Martijn

[Anonymous]

What I have:

Date	Time	MFH2 Status
18/06/2023	01:55:24	Shutdown
17/06/2023	14:39:45	Makeload
17/06/2023	14:10:34	Shutdown

What I want:

Date	Time	MFH2 Status	Duration state
=TODAY	=TODAY	=Previous state	Duration
18/06/2023	01:55:24	Shutdown	0.01:33:25.1000000
17/06/2023	14:39:45	Makeload	0.11:15:38.6000000
17/06/2023	14:10:34	Shutdown	0.00:29:11.6000000

The duration part: I've got that covered. But the part where it adds a line for the current time/date I can't get working.

I hope this clears it up.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDTNzIwMlbSUTIwtDI1tTIyATKDM0pLUvLL85RidYCKzJEUGZpYGVtamZgCmb6J2ak5+YkpWBUZGlgZo5oUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, #"MFH2 Status" = _t]),
    join = #table({"Date","Time","MFH2 Status"},{{Date.From(DateTime.FixedLocalNow()),Time.From(DateTime.FixedLocalNow()),Source[MFH2 Status]{0}}})  & Source,
    #"Changed Type" = Table.TransformColumnTypes(join,{{"Date", type date}, {"Time", type time}},"es")
in 
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Thanks a lot so far!

When I enter the query it changes it to this:

= "let#(cr)#(lf) Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""i45WMrTQNzDTNzIwMlbSUTIwtDI1tTIyATKDM0pLUvLL85RidYCKzJEUGZpYGVtamZgCmb6J2ak5+YkpWBUZGlgZo5oUCwA="", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, #""MFH2 Status"" = _t]),#(cr)#(lf) join = #table({""Date"",""Time"",""MFH2 Status""},{{Date.From(DateTime.FixedLocalNow()),Time.From(DateTime.FixedLocalNow()),Source[MFH2 Status]{0}}}) & Source,#(cr)#(lf) #""Changed Type"" = Table.TransformColumnTypes(join,{{""Date"", type date}, {""Time"", type time}},""es"")#(cr)#(lf)in #(cr)#(lf) #""Changed Type"""

---

 Obviously it doesn't work, I tried to enter the source table as source instead of 'Table.FromRows' but that doesn't help either

[lbendlin]

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Thanks a lot! missed the 'Advanced Editor' part.

Two more questions now: 

- I only see the top 3 lines + the current time. What if I want to see the whole page / all the data + the current time?

- Out of curiousity: what is the part that comes after 'Binary.FromText(...' part?

[lbendlin]

First question:

join = #table({"Date","Time","MFH2 Status"},{{Date.From(DateTime.FixedLocalNow()),Time.From(DateTime.FixedLocalNow()),Source[MFH2 Status]{0}}})  & Source,

Use whatever you want as the Source - as long as it has the same columns.

Second question

That is a Base64 encoded and compressed text that holds the sample data you provided.  You can plop it into an online Base64 decoder to see for yourself

let
    Source = Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDTNzIwMlbSUTIwtDI1tTIyATKDM0pLUvLL85RidYCKzJEUGZpYGVtamZgCmb6J2ak5+YkpWBUZGlgZo5oUCwA=", BinaryEncoding.Base64), Compression.Deflate))
in
    Source

It's an overly cute way of writing #table, basically.

[Anonymous]

Thanks a lot for all the help. I still don't get how to get the all of the data as output of the query?

[lbendlin]

Now you lost me. Where are you stuck?

[Anonymous]

This is what your query is showing me now with some tweaks:

This is what the original data is showing me:

As you can see a lot more data than the lines your query show. How can I show all the data in your query?

[lbendlin]

Take whatever is your original data as "Source"  for my query.

[Anonymous]

Works like a charm, thanks a lot for you help and patience!

[lbendlin]

In your Power Query script recalculate IP_TREND_TIME for the first row of the table ( addressed as  {0} )  to be the current timestamp.  Note:  This will be in UTC unless you convert timezones.

[Anonymous]

Thanks a lot! Think is I can't find how to do that? I need to add a new line with the current time stamp, not change the current top line time stamp to the current time.

regards,

Martijn

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...