Add custom column in power query with IF-OR

[wlljhn]

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

[CNENFRNL]

Hi, @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)

[edhans · Accepted solution]

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