Column with a Pattern

Power Query
1

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
2

@hatsilva

try below logic and fill up option

image
image1210×515 19.5 KB

image
image1544×547 25.2 KB

2 Likes
3

@hatsilva,

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"

image
image520×674 21.5 KB

I hope this is helpful – full solution file posted below.

– Brian

eDNA Forum -Repeating Pattern solution.pbix (68.3 KB)

1 Like
4

@Rajesh,

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
5

Thanks @BrianJ

6

Hi @Rajesh,

Great one as usual :+1:

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
7

Thanks @Melissa

8

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

2 Likes
9

Hi @hatsilva,

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

3 Likes
10

Fantastic @Melissa!!!
Many thanks

1 Like
11

let
StartDate = Date.From(Date.StartOfYear(Date.AddYears(DateTime.LocalNow(),-1))),
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

12

Many thanks.
Will give it a try.
regards