Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, @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)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting