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 ,
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/