Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Convert the excel formula into equivalent power query (M code) formula to compute a column

0

Hi,

Im fetching data from database using power query into excel and in the query table I need to add a custom column in the query editor, the formula in excel goes like this

=ROUNDUP((B2-IF(DATE(YEAR(B2),1,1)+210+CHOOSE(WEEKDAY(DATE(YEAR(B2),1,1),1),0,6,5,4,3,2,1)>B2,DATE(YEAR(B2)-1,1,1)+210+CHOOSE(WEEKDAY(DATE(YEAR(B2)-1,1,1),1),0,6,5,4,3,2,1),DATE(YEAR(B2),1,1)+210+CHOOSE(WEEKDAY(DATE(YEAR(B2),1,1),1),0,6,5,4,3,2,1))+1)/7,0)

I went through all the M functions in microsoft document but I couldnt find eqivalent functions for Roundup and Choose.

Note:Cell B2 contains Date.

excel
MS Office Version: 2016
posted by: Dr Liss (rep: 26) on Thu Jan 21, 2021 at 3:19 am
Answer
Discuss

Discussion

Dr Liss

What does your Excel formula aim to do please? It seems to return a number based on comparing a date (e.g. in B2) with 31 July of the current (or previous) year ut what does that represent?

Is your question here "How do I do this in Power Query?" rather than in Excel)? 
If so I probably can't help you other than to simplify your formula (so one of Power Query's Date Time functions might be used). 
John_Ru (rep: 6142) Jan 21, '21 at 4:48 am
Yes,
Basically, the formula is trying to assign the order date in column B to a week in our fiscal calendar.   A breakdown of the formula is as follows:
IF the first day of the fiscal year is greater than the order date in column B, THEN use the prior year fiscal calendar to assign the fiscal week
OTHERWISE, taking the first date of the current fiscal year, calculate how many days have passed between that date and the order date(B) The formula then adds ‘1’ to that result and divides by 7 to convert the days to weeks
Then round up to get to a whole number which is the fiscal week result  
For example, 9/21/2020 (ordered date) is greater than 8/2/2020 (first day of this fiscal year) so it belongs in this fiscal year (FY2021). There are 50 days in between 8/2/2020 and 9/21/2020. 9/21/2020 is day #51 of the fiscal year so +1 to 50, 51 divided by 7 is 7.285. Then round up to the next whole number: 8. So 9/21/2020 belongs to FW8(Fiscal week 8)
Cartus (rep: 2) Jan 21, '21 at 12:39 pm
Add to Discussion

Answers

0
Selected Answer

Cartus (/Dr Liss)

REVISION 2: Answer modified to 1) use date number in formula 2) adjust for erroneous result in week 52 and 3) attach a file comparing formule for all dates in 2021 

You can avoid the CHOOSE and ROUNDUP functions if you re-write your formula to something like that below (where you might need to change the date in bold to suit your fiscal year start):

=MOD(WEEKNUM(B2,1)-WEEKNUM(44408,1),52)

(and that cell should be formatted as General). That gives the same result as yours for today's date for example (even with a 31 July 2021 date number in bold since MOD ignores the negative portion of the remainder it produces).

That does however produce a 0 result when the date in B2 is at the end of the year (so the sum is 52 and the MOD remainder becomes 0.). That can be corrected by wrapping it with IF (formula<>0, formula, 0) like this: 

=IF(MOD(WEEKNUM(B2,1)-WEEKNUM(44408,1),52)<>0,MOD(WEEKNUM(B2,1)-WEEKNUM(44408,1),52),52)

Then you might use the Power Query function Date.WeekOfYear  instead of WEEKNUM and Number.Mod to replace MOD. Not sure but suggest you check out both sets of Excel and M Query functions since they seem to offer a simpler formula.

REVISION 1: See comparison of formulae (D3:D5 for basic with B2 and row 7 down for all dates in 2021) in attached file.

posted by: John_Ru (rep: 6142) on Thu Jan 21, 2021 at 1:08 pm
Discuss

Discussion

Thanks John!
I tried and the formula seems to give an error value.
Dr Liss (rep: 26) Jan 21, '21 at 10:34 pm
Did you try changing the fixed date within the formula to your normal style, say "07/31/2021"?
John_Ru (rep: 6142) Jan 22, '21 at 1:57 am
See  WEEKNUM function
John_Ru (rep: 6142) Jan 22, '21 at 2:00 am
See REVISION 1 to my Answer and illustrative file now attached
John_Ru (rep: 6142) Jan 22, '21 at 2:18 am
Cartus.

Please see REVISION 2 to my answer, which doesn't use the text date (but the Excel number) and compares all dates in 2021 to show that the new formula produces same result as yours (but is about one third of the length so hopefully easier to translate into Power Query).

If this doesn't help, I leave you to seek a solution or wait for others to work on it.
John_Ru (rep: 6142) Jan 22, '21 at 4:33 am
Did that work? 
John_Ru (rep: 6142) Jan 23, '21 at 2:17 am
Thanks John for your help!
Dr Liss (rep: 26) Mar 10, '21 at 1:58 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login