cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Date and Time Custom Column If/Then Syntax

New to Power BI and I need to create syntax for a custom column to include:

 

DAY OF WEEK ONLY as Friday and TIME ARRIVED ONLY as after 5:00:00pm, DAY OF WEEK ONLY as Saturday - no time restriction, and DAY OF WEEK ONLY as Sunday - no time restriction.

 

Friday after 5:00:00pm would be coded as 1_Fri

Saturday as 2_Sat

Sunday as 3_Sun

All else being coded as 0

 

I can't seem to get my syntax to work properly. Any help would be greatly appreciated!

 

alexandriaIDEA_0-1593792831003.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Re: Date and Time Custom Column If/Then Syntax

@alexandriaIDEA 

I am not good at M, maybe you can try

if Text.End([DAY OF WEEK ONLY],3)="Fri" and [TIME ARRIVED ONLY]>#time(17,0,0) then "1_Fri" else if Text.End([DAY OF WEEK ONLY],3)="Sat" then "2_Sat" else if Text.End([DAY OF WEEK ONLY],3)="Sun" then "3_Sun" else 0

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

4 REPLIES 4
Highlighted
Resident Rockstar
Resident Rockstar

Re: Date and Time Custom Column If/Then Syntax

@alexandriaIDEA 

you can try to use DAX to create a column

Column = if(right('Table (2)'[DAY OF WEEK ONLY],3)="Fri" && 'Table (2)'[TIME ARRIVED ONLY]>TIME(17,0,0) ,"1_Fri",if(RIGHT('Table (2)'[DAY OF WEEK ONLY],3)="Sat","2_Sat",if(RIGHT('Table (2)'[DAY OF WEEK ONLY],3)="Sun","3_Sun","0")))

1.PNG 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
New Member

Re: Date and Time Custom Column If/Then Syntax

Hey, @ryan_mayu!

 

Is there a way to do it through Query instead rather than through DAX? I'm really trying to get a better hold on the if/then statements in Query and I don't understand why I keep getting errors since I know exactly what I need and where the variables are coming from. Thank you, regardless!!!!

 

 

Highlighted
Resident Rockstar
Resident Rockstar

Re: Date and Time Custom Column If/Then Syntax

@alexandriaIDEA 

I am not good at M, maybe you can try

if Text.End([DAY OF WEEK ONLY],3)="Fri" and [TIME ARRIVED ONLY]>#time(17,0,0) then "1_Fri" else if Text.End([DAY OF WEEK ONLY],3)="Sat" then "2_Sat" else if Text.End([DAY OF WEEK ONLY],3)="Sun" then "3_Sun" else 0

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
New Member

Re: Date and Time Custom Column If/Then Syntax

Woohoo! @ryan_mayu  this worked perfectly! Thank you so much, you're my hero!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors