Consecutive values on multiple columns

[ozod]

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

As you can see, the first column is set to dates. The other columns show personnel with daily values: 0, 1, and 2 in each cell.

The job here is to create a query to find 14 or more successive values of "2" without any interruption on all columns other than "Date". 

The final output should give the start date of those values if they exist. 

I have looked up and found out that columns can be grouped with "GroupLocal" for consecutive values.

However, I couldn't apply this technique on dynamic columns at once (the number of columns and names are changing).

Is it possible to achieve the desired result in the end?

Let me know if you have questions.

Thank you in advance! 

[slorin · Accepted solution]

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 

[ozod]

thanks @slorin !

I have made minor adjustments and it works as intended in just 2 steps.

[ImkeF]

Hi @ozod ,
you can use this method then. It is using GroupKind.Local as you suggested, but for each column separately:

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    ColumnNames = List.Buffer(List.Skip(Table.ColumnNames(Source))), 
    ListOfColumns = Table.ToColumns(Source), 
    DatesList = List.Buffer(ListOfColumns{0}), 
    ListOfPersonColumns = List.Skip(ListOfColumns), 
    Custom1 = Table.FromColumns({ListOfPersonColumns, ColumnNames}, {"Values", "Person"}), 
    AddTable = Table.AddColumn(
        Custom1, 
        "Custom", 
        each Table.FromColumns({DatesList, [Values]}, {"Date", "Value"})
    ), 
    AddGrouping = Table.AddColumn(
        AddTable, 
        "Grouped", 
        each Table.Group(
            [Custom], 
            {"Value"}, 
            {{"StartDate", each List.Min(_[Date])}, {"Count", List.Count}}, 
            GroupKind.Local
        )
    ), 
    #"Added Custom" = Table.AddColumn(
        AddGrouping, 
        "Custom.1", 
        each Table.SelectRows([Grouped], (inner) => inner[Count] >= 14)
    ), 
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Person", "Custom.1"}), 
    #"Expanded Custom.1" = Table.ExpandTableColumn(
        #"Removed Other Columns", 
        "Custom.1", 
        {"StartDate"}, 
        {"StartDate"}
    ), 
    #"Transposed Table" = Table.Transpose(#"Expanded Custom.1"), 
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars = true])
in
    #"Promoted Headers"

Also, see file attached.

[ImkeF]

Hi @ozod ,
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.

[ozod]

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.