Convert the excel formula into power query formula to compute a column

  • Hi,

    Im fetching data from database using power query 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)


    Cell B2 contains Date. How do I write this in query editor ?

  • Thanks Carim.

    I went through the page following above link, but couldn't find equivalent formula for CHOOSE and RoundUp functions

  • Can you just explain in words what the formula should do? It looks unnecessarily complicated to me.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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)

  • Assuming column B is called Date, you can get the start of the current fiscal year using something like:


    Code
    1. Date.AddDays(Date.StartOfYear([Date]),210+Number.Mod(8-Date.DayOfWeek(Date.StartOfYear([Date]),Day.Sunday)-1,7))


    You can create another column with the start of the previous fiscal year, then just use whichever is appropriate in your week calculation.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • This formula return only first date of the fiscal year. Correct?

    FYI, the fiscal calendar actually start from AUG 1-JUL31. Is this formula written accordingly?

    Code
    1. Date.AddDays(Date.StartOfYear([Date]),210+Number.Mod(8-Date.DayOfWeek(Date.StartOfYear([Date]),Day.Sunday)-1,7))
  • Yes. It's the equivalent of your current formula (which actually returns 2-Aug for 2020). I'd have that as one calculation, do the same to get the prior year, and then use whichever column is appropriate to calculate the week number.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • And? I'm merely suggesting you use two separate columns to calculate the two fiscal year starts, and then use whichever of those is appropriate to calculate your week number. I wasn't suggesting that that was the end result.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why