Welcome to the forum – great to have you here!
Very interesting problem. I was able to solve this using a trick I picked up in Problem of the Week #3, which is the use of the Modulo function on the UI you can use to create a repeating pattern. In this case, I added an index starting at zero, then took the modulo(16) of that, added one to it and that created the 1…16 repeating pattern we needed here. From there I just did a “Add Column by Example” based on the repeating pattern column to get to our desired pattern. Here’s the M code:
let
Source = #"Extended Date Table"(fxStartDate, fxEndDate, 7, null, null),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 16), type number),
#"Inserted Addition" = Table.AddColumn(#"Inserted Modulo", "Addition", each [Modulo] + 1, type number),
#"Removed Other Columns1" = Table.SelectColumns(#"Inserted Addition",{"Date", "Addition"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Other Columns1", "Custom", each if [Addition] >= 13 then "1" else if [Addition] >= 9 then 0 else if [Addition] >= 5 then 1 else 0, type any),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Custom"})
in
#"Removed Other Columns"
I hope this is helpful – full solution file posted below.
– Brian