Forum Discussion
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:
| Resultaatteken | Geldig tm |
| X | 25/02/2020 |
| @ | null |
| X | 22/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;
- How do I compare todays date to a date value (date type) during Table.ReplaceValue
- 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!
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
- JesseVFrequent 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
Community 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.