Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Consecutive values on multiple columns

Hello,   I hope you can help me with a case I have spent hours on. I am struggling with consecutive values where they are spread over several columns. You can access the sample data via thi...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

    another solution

     

    let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    DatesList = List.Buffer(Source[Date]), 
    Table = #table(
          List.Skip(Table.ColumnNames(Source)),
          {List.Transform(
             List.Skip(Table.ToColumns(Source)),
             each try DatesList{Text.PositionOf(
                 Text.Combine(List.Transform(_,Text.From)),
                 Text.Repeat("2",12))}
             otherwise "--")}
           )
    in
    Table

    Stéphane 

ImkeF's avatar
ImkeF
Community Champion
2 years ago

Hi Anonymous ,
do you really just want to see the first date where there are more than 14 consequtive values?

See person 5 for example, there is another sequence starting at the 11th of march.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ImkeF 

 

I intentionally marked with a big "X" the 5th person to remind myself of this double sequence. 

But, yeah, just the first date that matches the criteria. The other set in the column can be omitted in the search.