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
… …

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 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.