# Column with a Pattern

Hi All,
Many thanks for sharing your knowledge, you are an inspiration.
I am trying to create a column in the Dates table (Power Query using M) with the following pattern (0 0 0 0 1 1 1 1 0 0 0 0 1 1 1 1 …):
Date D1
1/1/2021 0
2/1/2021 0
3/1/2021 0
4/1/2021 0
5/1/2021 1
6/1/2021 1
7/1/2021 1
8/1/2021 1
… …

Many thanks in advance
Hugo

1 Like

@hatsilva

try below logic and fill up option

2 Likes

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

1 Like

I just saw your solution after I posted mine – really clever use of fill up in combination with modulo to get to a very efficient solution. I’ll definitely tuck that one away for future use…

• Brian

Thanks @BrianJ

Hi @Rajesh,

Great one as usual

I just wanted to share that you can avoid the Added Index column all together with a minor adjustment and that is to subtract the FirstDate from the Date column value, for example:

`Number.Mod(Number.From( [Date] ) - Number.From( #date(2021, 1, 1)) +1, 8 ) = 0`

.
This is amazing stuff you guys!!
Love it.

2 Likes

Thanks @Melissa

Many thanks @Rajesh, @BrianJ and @Melissa for your help. I was stuck on this for some time!

2 Likes

Hi @hatsilva,

Thought your question would make an excellent introduction into the Number.Mod function.
Hope you find it helpful.

3 Likes

Fantastic @Melissa!!!
Many thanks

1 Like

let
EndDate = Date.From (Date.EndOfYear(Date.AddYears(DateTime.LocalNow(),10))),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
LstDates = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.TransformColumnTypes( Table.FromList(LstDates, Splitter.SplitByNothing(), {“Date”}) , {{“Date”, type date}}),
AddIndex = Table.AddIndexColumn(TableFromList, “Index”, 1, 1, Int64.Type),
Division = Table.AddColumn(AddIndex, “Division”, each [Index] / 4, type number),
RoundUp = Table.AddColumn(Division, “Round Up”, each Number.RoundUp([Division]), Int64.Type),
SubtractOne = Table.TransformColumns(RoundUp, {{“Round Up”, each _ - 1, type number}}),
Modulo = Table.AddColumn(#“SubtractOne”, “Modulo”, each Number.Mod([Round Up], 2), type number)
in
Modulo

Many thanks.
Will give it a try.
regards