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 ,
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.