Forum Discussion

JesseV's avatar
JesseV
Frequent Visitor
4 years ago
Solved

Replacevalue after comparing date type collumn value with current date

Good afternoone everyone 

 

Me (a noobie to PowerBI) would like to add a powerquery step to the following example data:

 

ResultaattekenGeldig tm
X25/02/2020
@null
X22/10/2030

 

if the "Geldig tm" column has a date that is in the past I'd like the "Resultaatteken value to be V.  as such (with the help of these forums!) I wrote the following code:

 

= Table.ReplaceValue(PrevStep, each [Resultaatteken] , each if DateTime.LocalNow > [Geldig tm] then "V" else [Resultaatteken],Replacer.ReplaceValue,{"Resultaatteken"})

 

Unfortunately this is not giving me the desired result, at which time I decided I'd like to see what the value of DateTime.LocalNow is (Suspecting it is some form of display or Date & datetime shennanigans) so I copied the Resultaatteken column to Resultaattekenz and ran: 

 

= Table.ReplaceValue(#"Volgorde van kolommen gewijzigd1", each [Resultaattekenz], DateTime.LocalNow ,Replacer.ReplaceValue,{ "Resultaattekenz"})

 

which doesn't change the value at all, however when I replace DateTime.LocalNow in the second query with a string literal "Why" then it changes the values of the collumn as I expected.. So as far as I can tell DateTime.LocalNow appears empty. 

 

Long story short(er) I was hoping to find some wisdom on this forum;

 

  1. How do I compare todays date to a date value (date type) during Table.ReplaceValue
  2. How can I view the output of a function. (I suspect the whole copying of a collumn and printing it on every row isn't exactly the way to go).

 

Thank you in advance for any and all help given! 

 

 

  • Icey's avatar
    Icey
    4 years ago

    Hi JesseV ,

     

    You can try to use DateTime.Date function.

    = Table.ReplaceValue(#"Changed Type with Locale", each [Resultaatteken], DateTime.Date(DateTime.LocalNow()),Replacer.ReplaceValue,{ "Resultaatteken"})

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • JesseV's avatar
    JesseV
    Frequent Visitor

    So through the magic of reading the manual I have discored that I was missing as datetime after DateTime.LocalNow()

     

    = Table.ReplaceValue(#"Volgorde van kolommen gewijzigd1", each [Resultaattekenz], DateTime.LocalNow() as datetime ,Replacer.ReplaceValue,{ "Resultaattekenz"})

     

    Which just leaves me wondering how I can convert it to just the date value ('as date' unfortunately didn't take.. which seems a little odd to me)

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support

      Hi JesseV ,

       

      You can try to use DateTime.Date function.

      = Table.ReplaceValue(#"Changed Type with Locale", each [Resultaatteken], DateTime.Date(DateTime.LocalNow()),Replacer.ReplaceValue,{ "Resultaatteken"})

       

       

      Best Regards,

      Icey

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.