Forum Discussion

philerob2014's avatar
philerob2014
Frequent Visitor
3 years ago
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 empty

 

Any ideas how I can do this? 

Need Help
Show and Tell
Tips and Tricks
  • tackytechtom's avatar
    tackytechtom
    3 years ago

    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/

9 Replies

  • philerob2014's avatar
    philerob2014
    Frequent Visitor
    3 years ago

    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's avatar
      tackytechtom
      Most Valuable Professional
      3 years ago

      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's avatar
        philerob2014
        Frequent Visitor
        3 years ago

        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