Forum Discussion
Add custom column in power query with IF-OR
Hi
I'm trying to add new column in power query with condition that I will normally use in excel as follow:
if(or(weekday([Date],2)=5,weekday([Date],2)=6),weeknum([Date]+3,2),weeknum([Date]+2,2)
Really appreciate any help on how to do it in power query.
regards
It is very similar, just a slightly different syntax:
if(or(weekday([Date],2)=5,weekday([Date],2)=6),weeknum([Date]+3,2),weeknum([Date]+2,2)Becomes
if Date.DayOfWeek([Date],Day.Monday) = 5 or Date.DayOfWeek([Date],Day.Monday) = 6 then Date.WeekOfYear(Date.AddDays([Date],3),Day.Monday) else Date.WeekOfYear(Date.AddDays([Date],2),Day.Monday)Without actual data hard to verify so check it out. the Day.Monday parameter just says what the first day of the week is, so change that as desired. I think the ,2, in Excel does the same.
In Power Query, there is no IF() function, but you use if/then/else, and else is always required. If you don't need it just use else null to return nothing. if/then/else is always 100% lowercase too.
I put line breaks in my formula above. You don't have to, but easier to read I think. See if that helps wlljhn
2 Replies
- edhans
Community Champion
It is very similar, just a slightly different syntax:
if(or(weekday([Date],2)=5,weekday([Date],2)=6),weeknum([Date]+3,2),weeknum([Date]+2,2)Becomes
if Date.DayOfWeek([Date],Day.Monday) = 5 or Date.DayOfWeek([Date],Day.Monday) = 6 then Date.WeekOfYear(Date.AddDays([Date],3),Day.Monday) else Date.WeekOfYear(Date.AddDays([Date],2),Day.Monday)Without actual data hard to verify so check it out. the Day.Monday parameter just says what the first day of the week is, so change that as desired. I think the ,2, in Excel does the same.
In Power Query, there is no IF() function, but you use if/then/else, and else is always required. If you don't need it just use else null to return nothing. if/then/else is always 100% lowercase too.
I put line breaks in my formula above. You don't have to, but easier to read I think. See if that helps wlljhn
CNENFRNLHi, wlljhn , given that return values of excel WEEKDAY(param1, return_type) function vary based on return_type as such,
Return_type Number returned
1 or omitted
Numbers 1 (Sunday) through 7 (Saturday).
2
Numbers 1 (Monday) through 7 (Sunday).
3
Numbers 0 (Monday) through 6 (Sunday).
I'd like to verify that you exam the [Date] is either Friday or Saturday, don't you? If so, its equivalent in Power Query is like this
if Date.DayOfWeek([Date],Day.Sunday) >= 5 then Date.WeekOfYear(Date.AddDays([Date],3),Day.Monday) else Date.WeekOfYear(Date.AddDays([Date],2),Day.Monday)