Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I am trying to replace null values with a date in the future (30/12/22) and dates in the past ie. before today with a date in the future (31/12/22). Replacing null values is straightforward but replacing dates in the past is confusing me. I would really appreciate some advice on a solution!
Here is what I have:
#"Replaced Value" = Table.ReplaceValue(Table1_Table,null,#date(2022, 12, 30),Replacer.ReplaceValue,{"Forecast"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < DateTime.FixedLocalNow() then #date(2022,12,31) else [Forecast],Replacer.ReplaceValue,{"Forecast"})
Many thanks!
Replace future dates by today date
Solved! Go to Solution.
Hi @Anonymous
I think your forecast date is probably a date not a datetime. Which mean the comparing it to DateTime.FixedLocalNow() will not work (they need matching datatypes. You could solve this with a Date.From function you could also get both steps in one.
#"Replace Value" = Table.ReplaceValue(
#"Table1_Table",
each [Forecast],
each
if [Forecast] < Date.From(DateTime.FixedLocalNow()) or [Forecast] = null then
#date(2022, 12, 31)
else
[Forecast],
Replacer.ReplaceValue,
{"Forecast"}
)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Best regards,
Jeroen
Many thanks Jeroen, your advice to introduce a Date.From function worked. I kept my steps seperate in the end as it was a simple fix.
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(2021, 12, 31),Replacer.ReplaceValue,{"Forecast"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,30) else [Forecast],Replacer.ReplaceValue,{"Forecast"}),
Regards
Matt
Instead of Table.ReplaceValue, you could also try Table.Transform.
Table.TransformColumns(
Table1_Table,
{
"Forecast",
each if _ = null then #date(2022, 12, 30)
else if _ > Date.From(DateTime.FixedLocalNow()) then #date(2022, 12, 31)
else if _ < Date.From(DateTime.FixedLocalNow()) then #date(2022, 12, 30)
else _
}
)
Hi
Like your solution. Only aspect that isn't working is for dates in the future, these should be the [Forecast] value. I tried adjusting your code but no luck! Suspect it would be a simple fix.
If you want the [Forecast] value, then use _ instead of #date(2022, 12, 31).
Hi @Anonymous
I think your forecast date is probably a date not a datetime. Which mean the comparing it to DateTime.FixedLocalNow() will not work (they need matching datatypes. You could solve this with a Date.From function you could also get both steps in one.
#"Replace Value" = Table.ReplaceValue(
#"Table1_Table",
each [Forecast],
each
if [Forecast] < Date.From(DateTime.FixedLocalNow()) or [Forecast] = null then
#date(2022, 12, 31)
else
[Forecast],
Replacer.ReplaceValue,
{"Forecast"}
)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Best regards,
Jeroen
Many thanks Jeroen, your advice to introduce a Date.From function worked. I kept my steps seperate in the end as it was a simple fix.
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(2021, 12, 31),Replacer.ReplaceValue,{"Forecast"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,30) else [Forecast],Replacer.ReplaceValue,{"Forecast"}),
Regards
Matt
Use coalesce operator to simplify further more,
Table.ReplaceValue(#"Changed Type", each [Forecast], each if ([Forecast]??Date.From(0)) < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,31) else [Forecast], Replacer.ReplaceValue, {"Forecast"})
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.