Forum Discussion

Dimi_2207's avatar
Dimi_2207
Icon for Helper I rankHelper I
2 years ago
Solved

To show max value based on condition within time period

Hello!   Could you, please, help me with the following task. I have the set of data:    Phone Date Call number CC Max Call number CC 111 01-11-23 12:59 1 1 111 15-11-23 12:59 1 ...
  • lbendlin's avatar
    2 years ago
    let
      Source = Table.FromRows(
        Json.Document(
          Binary.Decompress(
            Binary.FromText(
              "ddBBDsQgCIXhq0xc1wQeqJWrNL3/NUY70tLFJO6+/BA8jsTMaUvEmTlDPgwrPZ2bA5c/gLsoVmoEcVCjEqE5NKPXqP0ZtQDABM2MC9igEbrDKCgASR6nQOcO1ffyBezFuhx5vAndiCPUBSCDRGheVJNfISLzr+gB7AFwAy44vw==", 
              BinaryEncoding.Base64
            ), 
            Compression.Deflate
          )
        ), 
        let
          _t = ((type nullable text) meta [Serialized.Text = true])
        in
          type table [Phone = _t, Date = _t]
      ), 
      #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {{"Phone", Int64.Type}, {"Date", type datetime}}, 
        "en-GB"
      ), 
      #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Phone"}, 
        {{"Rows", each _, type table [Phone = nullable number, Date = nullable datetime]}}
      ), 
      Process = (tbl) =>
        let
          #"Sorted Rows" = Table.Sort(tbl, {{"Date", Order.Ascending}}), 
          #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), 
          #"Added Custom" = Table.AddColumn(
            #"Added Index", 
            "Group", 
            each List.Accumulate(
              {0 .. [Index]}, 
              1, 
              (state, current) =>
                if current = 0 then
                  state
                else if #"Added Index"{current}[Date]
                  - #"Added Index"{current - 1}[Date] > #duration(7, 0, 0, 0)
                then
                  state + 1
                else
                  state
            )
          ), 
          #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Phone", "Date", "Group"}), 
          #"Grouped Rows1" = Table.Group(
            #"Removed Other Columns", 
            {"Group"}, 
            {
              {
                "Rows", 
                each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), 
                type table [
                  Phone = nullable number, 
                  Date = nullable datetime, 
                  Group = number, 
                  Index = Int64.Type
                ]
              }, 
              {"Max", each Table.RowCount(_), Int64.Type}
            }
          ), 
          #"Expanded Rows" = Table.ExpandTableColumn(
            #"Grouped Rows1", 
            "Rows", 
            {"Phone", "Date", "Index"}
          )
        in
          #"Expanded Rows", 
      #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows])), 
      #"Expanded Processed" = Table.ExpandTableColumn(
        #"Added Custom", 
        "Processed", 
        {"Group", "Date", "Index", "Max"}, 
        {"Group", "Date", "Index", "Max"}
      ), 
      #"Removed Other Columns" = Table.SelectColumns(
        #"Expanded Processed", 
        {"Phone", "Group", "Date", "Index", "Max"}
      )
    in
      #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.