Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luismiguelopes
Regular Visitor

M Language IF Statement Setting Specific DateTime

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)

 

 

 

ps1.png

 

Thanks for the help

 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

 

View solution in original post

2 REPLIES 2
Jimmy801
Community Champion
Community Champion

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

 

nickyvv
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors