Forum Discussion
Solved
Timestamp when true
Hey Everyone, I need some help. Essentially, I am trying to timestamp a record once the conditions are true. If Condition 1 = true and condition 2 = true date/timestamp else leavel empt...
Hi philerob2014 ,
Maybe like this?
Before:
After:
I applied the following logic:
if [Substate] = "Pending return" and [timestamp] <> null then [timestamp] else if [Substate] = "Pending return" and [timestamp] = null then DateTime.LocalNow() else if [Substate] <> "Pending return" and [timestamp] <> null then null else null
Here the full code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjNS8nMS1coSi0pLcpT0lEyMjAy0jU00jUwUzA0sjIwACKlWJ1opbTEnOJUNHlDLPJgDoapuERjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Substate = _t, timestamp = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Substate", type text}, {"timestamp", type datetime}}), #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [timestamp],each if [Substate] = "Pending return" and [timestamp] <> null then [timestamp] else if [Substate] = "Pending return" and [timestamp] = null then DateTime.LocalNow() else if [Substate] <> "Pending return" and [timestamp] <> null then null else null, Replacer.ReplaceValue,{"timestamp"}) in #"Replace Values"/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Sure, let me clarify exactly what I need so its clear. Your solutions are beautifully written, I just dont understand how to connect them.
If [Substate] = "Pending return"
get original time and date that this condition was first true on report refresh. Purge date when condition is not true. If condition is true over multiple refreshes, keep the original date.
Hi philerob2014 ,
Maybe like this?
Before:
After:
I applied the following logic:
if [Substate] = "Pending return" and [timestamp] <> null then [timestamp] else if [Substate] = "Pending return" and [timestamp] = null then DateTime.LocalNow() else if [Substate] <> "Pending return" and [timestamp] <> null then null else null
Here the full code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjNS8nMS1coSi0pLcpT0lEyMjAy0jU00jUwUzA0sjIwACKlWJ1opbTEnOJUNHlDLPJgDoapuERjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Substate = _t, timestamp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Substate", type text}, {"timestamp", type datetime}}),
#"Replace Values" = Table.ReplaceValue(#"Changed Type",each [timestamp],each if [Substate] = "Pending return" and [timestamp] <> null then [timestamp] else if [Substate] = "Pending return" and [timestamp] = null then DateTime.LocalNow() else if [Substate] <> "Pending return" and [timestamp] <> null then null else null, Replacer.ReplaceValue,{"timestamp"})
in
#"Replace Values"
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/