Forum Discussion
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!
Anonymous
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
4 Replies
Anonymous
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")))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!!!!
Anonymous
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