Flag nth dates in a recuring 7 day period

[Richard_Halsall]

Hi, I am looking to create a column that will flag nth dates in a recuring 7 day period. For example the 4 days in a first 7 day period, 4 days in the next 7 day period etc etc. It can flag the first 4 days or the last 4 days or any 4 days in the 7 day period it does not matter

A pattern does not need to complete

In the sample code below there are 3 id's with differing date ranges and DaysPerWeek values. I have created an ExpectedFlag column to show the required outcome

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lde9agMxEEXhd9naYM3V/pYuQnCRkEAgBOPObaq8P8SLG+8Ud3UMLgxfIfasZM3l0pUyftayfur32+/f+fTaHbrh/i1xLMNRReuPn+56cFbAVmB7YAdgxyf7vmMnYGewhqXdRgEWdAvQLWr7c4geWNAtRmAnYEG3AN1U2p+DAljQTWC/Cew3gW4C3QT2m2ZgQbcK9lsl5+TdjuBMbbb1ye6dUT2wg13Dx3J72Gl/vVtbge2BJesdgZ2AnYFd2u36H9Bs/Xu2taBbgG4BugXoFqBbgG4BugXoJtBNoJtAN4FuAt0Eugl0E+gm0E2gWwXd1nN9Audks63A9sAOwI7O3r6k8+llfV7d3hyQrL2XJGvvJckO7k6QrJ0DkrX3kmTtvSRZey/ZWj8HJGvvJcmqfb1+DkjWzgHJgm5+DkjWzgHJ2jkg2aV9vX4OSNbOAcmCbn4OSBbsNz8HJAv2m58DkgX7TaCbnwOSTfvt+g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, DaysPerWeek = _t, HeadcountDate = _t, #"Expected Flag" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"DaysPerWeek", Int64.Type}, {"HeadcountDate", type date}, {"Expected Flag", type text}})
in
#"Changed Type"

Any help will be much appreciated

Thanks

[lbendlin · Accepted solution]

What is your definition of "week" ?  When does your week start? 2025-05-01 is a Thursday.

When you say "x days per week"  do you mean "first x days in the week" ?  or "any x days in the week" ?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lde9agMxEEXhd9naYM3V/pYuQnCRkEAgBOPObaq8P8SLG+8Ud3UMLgxfIfasZM3l0pUyftayfur32+/f+fTaHbrh/i1xLMNRReuPn+56cFbAVmB7YAdgxyf7vmMnYGewhqXdRgEWdAvQLWr7c4geWNAtRmAnYEG3AN1U2p+DAljQTWC/Cew3gW4C3QT2m2ZgQbcK9lsl5+TdjuBMbbb1ye6dUT2wg13Dx3J72Gl/vVtbge2BJesdgZ2AnYFd2u36H9Bs/Xu2taBbgG4BugXoFqBbgG4BugXoJtBNoJtAN4FuAt0Eugl0E+gm0E2gWwXd1nN9Audks63A9sAOwI7O3r6k8+llfV7d3hyQrL2XJGvvJckO7k6QrJ0DkrX3kmTtvSRZey/ZWj8HJGvvJcmqfb1+DkjWzgHJgm5+DkjWzgHJ2jkg2aV9vX4OSNbOAcmCbn4OSBbsNz8HJAv2m58DkgX7TaCbnwOSTfvt+g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, DaysPerWeek = _t, HeadcountDate = _t, #"Expected Flag" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"DaysPerWeek", Int64.Type}, {"HeadcountDate", type date}, {"Expected Flag", type text}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Rows", each _, type table [Id=nullable text, DaysPerWeek=nullable number, HeadcountDate=nullable date, Expected Flag=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rows2", each Table.AddColumn(Table.AddIndexColumn(Table.Sort([Rows],{{"HeadcountDate", Order.Ascending}}), "Index", 0, 1, Int64.Type), "Flag", each if Number.Mod([Index],7)<[DaysPerWeek] then "Y" else "N")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Rows2" = Table.ExpandTableColumn(#"Removed Columns", "Rows2", {"DaysPerWeek", "HeadcountDate", "Expected Flag", "Flag"}, {"DaysPerWeek", "HeadcountDate", "Expected Flag", "Flag"})
in
    #"Expanded Rows2"

[Richard_Halsall]

Hi and thanks for your response,

To clarify:

My definition of a week is a 7 day period starting from the earliest headcount date of the Id and not a traditional calendar week. So for Id 006Q3000003WMmsIAG the first 7 day period would be 2025-05-01 to 2025-05-07, the next 2025-05-08 to 2025-05-14 etc etc

"x days per week"  do you mean "first x days in the week" ?  or "any x days in the week" - it can be either I just need to flag the specified number of days in a that 7 day period

Thanks

[lbendlin]

see my code above

[Richard_Halsall]

Perfect thank you for your help