Calculating Weekday for Fortnight (1-14) in Power Query.

[DataLibrary2020]

Hi All, 

I am trying to create a column in Power Query which calculates the "Weekday" but for a fortnight. 
i.e Days 1-14 rather than days 1-7. 

Any help is appreciated! 

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdBBCsAgDETRu3Rd0Bm1tWcR73+N0kVh/vJDSB5Z62hFxdU+9vmFM1pGzxgZV8adMTOeDFUUDAJCUAgMwSFABIlAESyGxfwHLIbFsBgWw2JYDIthaXG9F0TPmBgzaqD+9fsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.PositionOf(List.Dates(Date.StartOfMonth([Date]),Date.Day(Date.EndOfMonth([Date])),#duration(1,0,0,0)),[Date])+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Day of Week", each (if [Custom]<=7 then Date.DayOfWeek([Date])
        else if [Custom]<=14 then Date.DayOfWeek([Date])+7
        else if [Custom]<=21 then Date.DayOfWeek([Date])
        else if [Custom]<=28 then Date.DayOfWeek([Date])+7
        else Date.DayOfWeek([Date]))+1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"