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 everyone,
I'm trying to do a if statement with this logic behind in M language
if weekday = 5 and Time Opened Hour > 18 then sum 3 days to Time Opened column and set the time to 7.
= Table.AddColumn(#"Filtered Rows", "OutWorkTime", each if [WeekDay] = 5 then Date.AddDays([Time Opened], 3) and DateTime.Time([Time Opened], #time(7,0,0))
else null)
Thanks for the help
Solved! Go to Solution.
Hello @luismiguelopes
there are several errors in your code. Try out this and check out what i changed. And by the way, you dont't need to have this calculated columns for this
if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened]
Here the complete solution for everybody
let
Source = #table
(
{"Time opened"},
{
{"43868,7916666667"}, {"43868,625"}
}
),
ToDateTime = Table.TransformColumns
(
Source,
{
{
"Time opened",
each DateTime.From(Number.From(_)),
type datetime
}
}
),
AddColumn = Table.AddColumn
(
ToDateTime,
"new column",
each if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened],
type datetime
)
in
AddColumn
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @luismiguelopes
there are several errors in your code. Try out this and check out what i changed. And by the way, you dont't need to have this calculated columns for this
if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened]
Here the complete solution for everybody
let
Source = #table
(
{"Time opened"},
{
{"43868,7916666667"}, {"43868,625"}
}
),
ToDateTime = Table.TransformColumns
(
Source,
{
{
"Time opened",
each DateTime.From(Number.From(_)),
type datetime
}
}
),
AddColumn = Table.AddColumn
(
ToDateTime,
"new column",
each if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened],
type datetime
)
in
AddColumn
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @luismiguelopes,
I think you've mistakenly took DateTime.Time to set the time, but this formula gets the time of your column.
I think you'll have to create your datetime with the date-part of your column [Time Opened], and add the time of 7 hours yourself.
Maybe something like the following:
#datetime(Date.Year([Time Opened]), Date.Month([Time Opened]), Date.Day([Time Opened]), 7, 0, 0)
Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV
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.