Forum Discussion
Timestamp when true
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/
Hi philerob2014 ,
I interpreted "Problem 2: If the row already has a timestamp from the previous refresh and is still true, leave it. If its no longer true, make it blank again." that there is already a timestamp column in place. I might have misunderstood your query.
You miight wantt to provide some example data and expected result?
/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/tackytechtom Ok, I see what you are heading towards. I am lost on one thing to make this work, because when I place the code, it gives me an error. Is it because that I am using your code without first creating a "timestamp" column first that it can reference? I am under the assumption that we would have to create 2 columns in total the refer to the substate column. 1 to grab the timestamp, and the second to identify the earliest date in which the condition was true.
Is your code design for just 1 total column or the 2 that I thought we would have to use?
Hi philerob2014 ¨,
My approach is just using one column and expected that column to be in place already.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/