locked
Roster Calendar - Power Query RRS feed

  • Question

  • I am trying to create a Rostering calendar for 365 days which starts on 28 April, which can be achieved by 

    List.Dates(#date(2019, 04, 28), 365, #duration(1, 0, 0, 0)). After this I need to create a number of custom columns, which I need assistance of:

    1) Create Week number column such 1st 7 days starting from 28 Apr will be week 1, second 7 days will be week 2 and so on. But when I hit the 52 weeks, there will be 1 day left over which I want to include in the last week of the rostering calendar.



    Saturday, February 16, 2019 7:29 AM

Answers

  • Oh sorry - the index needs to start at 0 instead:

    let
        Query1 = List.Dates(#date(2019,04, 28), 365, #duration(1, 0, 0, 0)),
        #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
        #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
        #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7)+1, Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Integer-Divided Column", "WeekNumber", each if [Index]<= 52 then [Index] else 52),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, February 18, 2019 4:05 PM

All replies

  • You can encapsle the logic for the weeknumber in condition that returns its value as long as it's less or equal to 52 and if not, return 52 instead:

    if <MyFormulaToCalculateTheWeekNumber> <=52 then <MyFormulaToCalculateTheWeekNumber> else 52


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, February 16, 2019 6:34 PM
  • Hi Imke:

    What goes in <MyFormulaToCalculateTheWeekNumber> here?

    I tried "Date.WeekOfYear" and "Date.WeekOfMonth" but couldn't get it work? The other way I am think is conditional column which would have "52 else if statements" not very elegant.

    let
        Query1 = List.Dates(#date(2019,04, 28), 365, #duration(1, 0, 0, 0)),
        #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Date.WeekOfMonth([Date]))
    in
        #"Added Custom"

    Sunday, February 17, 2019 8:51 AM
  • Hi M.,

    I thought you had the formula for the week already.

    Please try this out:

    let
        Query1 = List.Dates(#date(2019,04, 28), 365, #duration(1, 0, 0, 0)),
        #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
        #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
        #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7)+1, Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Integer-Divided Column", "WeekNumber", each if [Index]<= 52 then [Index] else 52),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 17, 2019 3:00 PM
  • Hi Imke: Love your work. In week -1, it shows "1" - 6 times and in 52 weeks the instances is 9. That's the only issue needs to be fixed.

    Apart from that it looks good.

    Sunday, February 17, 2019 10:01 PM
  • Oh sorry - the index needs to start at 0 instead:

    let
        Query1 = List.Dates(#date(2019,04, 28), 365, #duration(1, 0, 0, 0)),
        #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
        #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
        #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7)+1, Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Integer-Divided Column", "WeekNumber", each if [Index]<= 52 then [Index] else 52),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, February 18, 2019 4:05 PM