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

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 

5 Replies

  • slorin's avatar
    slorin
    Super User
    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      thanks slorin !

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

  • ImkeF's avatar
    ImkeF
    Community Champion
    2 years ago

    Hi Anonymous ,
    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'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.