Create Calendar Table for Payroll

[bzflowerbee]

Hi,
How is everybody doing in here? I am hoping I could get some help in here. I'm working on a payroll report for bi weekly pay period. Our 1st pay period is for example Jan 3, 2021 to Jan 16, 2021...So, how do I create a calendar table based on the date range to give me the pay period number? For example, I want the date from Jan 3 to Jan 16 shows "pay period 1"....

Thank you.

 

[Chihiro]

So it's every 14 days.

Calendar table in Power Query / Data model should start from Jan 1 and always end on Dec 31.

Steps:

1. Using list generate create list of every date for year(s) that you want to include.
Ex: = List.Dates(#date(2021,1,1),365,#duration(1,0,0,0))

2. Convert this into table.

3. In separate query. Create list of pay period start dates. Then add column for pay period end dates. Add index and construct pay period string.
Ex:

let
    Source = List.Dates(#date(2021,1,3),26,#duration(14,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Start"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [Start] + #duration(13,0,0,0)),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Custom", "End"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "PayPeriod", each "Pay Period " & Text.From([Index])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Start", type date}, {"End", type date}, {"PayPeriod", type text}})
in
    #"Changed Type"

4. Back in date list, use following custom column to add pay period.

= try Table.SelectRows(Query2,(magic) => magic[Start] <= [Column1] and magic[End] >= [Column1])[PayPeriod]{0} otherwise "Not this Year"