Timestamp when true

[philerob2014]

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 empty

Any ideas how I can do this? 

[philerob2014]

This worked beautifully @tackytechtom  to fix the first of 2 problems, thanks! But I think I may be out of luck on rounding the solution out for my reporting.

Problem 1 : Get a timestamp when condition is true (SOLVED)

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. 

Do you have any idea if ill be able to do that? I see that I cant with a table column outside of power query editor, so I cant just make another column and make it take the date if one already exists, or to make a new one. 

[tackytechtom]

Hi @philerob2014 ,

Ok, I think I now understand a bit better what you are after. For your scenario I suggest you use Power Query:

Before:

After:

I applied the following logic:

if [Column] = true and [timestamp] <> null then [timestamp] else if [Column] = true and [timestamp] = null then DateTime.LocalNow() else if [Column] = false and [timestamp] <> null then null else null

Here the code for the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKikqTVXSUTIyMDLSNTTSNTBTMDSyMjAAIqVYnWiltMScYnR5QyzyYA7ULFR2LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type logical}, {"timestamp", type datetime}}),
    #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [timestamp],each if [Column] = true and [timestamp] <> null then [timestamp] else if [Column] = true and [timestamp] = null then DateTime.LocalNow() else if [Column] = false and [timestamp] <> null then null else null, Replacer.ReplaceValue,{"timestamp"})

in
    #"Replace Values"

In case you haven't worked with Power Query before, I'd suggest to start with a blank query and look into the steps above. I am sure you will be able to figure it out how to align your code 🙂 Otherwise, you could also just create a new column with the if logic above. If you are happy with it and you want to overwrite your timestamp column, I'd suggest to check out this blog post, where you can learn how to substitute values (instead of creating a new column)

[philerob2014]

Ahhhh, you are brilliant! But what would be my referenced field for "[timestamp]". That was created outside of power query editor so I didn't think the second step could reference that. @tackytechtom 

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

[philerob2014]

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.

[tackytechtom · Accepted solution]

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/

[philerob2014]

@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? 

[tackytechtom]

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/

[tackytechtom]

Hi @philerob2014 ,

I reckon with timestamp you mean the current timestamp?

Here a solution in DAX with a calculated column:

timestampcol = IF ( 'Table'[column] = TRUE, NOW(), BLANK() )

I am not sure though, whether this is what you were looking for 🙂

Let me know!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/