Latest Enterprise DNA Initiatives

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

@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" 

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

– Brian

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

1 Like

@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

Thanks @BrianJ

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

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.

2 Likes

Fantastic @Melissa!!!
Many thanks

1 Like