create list of periods from list of date&time

[Anonymous]

Hi everyone

I have a list of date&time

e.g.

ts = 27.02.2019 2:39:39

each second I have new ts

e.g.

ts = 27.02.2019 2:39:40

So in 10 seconds I will have next column

27.02.2019 2:39:39

27.02.2019 2:39:40

27.02.2019 2:39:41

27.02.2019 2:39:42

27.02.2019 2:39:43

27.02.2019 2:39:44

27.02.2019 2:39:45

27.02.2019 2:39:46

27.02.2019 2:39:47

27.02.2019 2:39:48

I need to create a list of periods in 5 sec

e.g.

27.02.2019 2:39:39 - 27.02.2019 2:39:43

27.02.2019 2:39:44 - 27.02.2019 2:39:48

Have no idea how to do that

Have you?

Thanks in advance

[LivioLanzo]

In PowerQuery you can try like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bchBDgAQDATAr0jPErVK8RXp/7/B3SZzmnMEBV6gdSXFbuuRyH+b8q68wbvxNt6d9+DtvKdEXA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Times = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Times", type datetime}}),
    SortedRows = Table.Sort(ChangedType,{{"Times", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    InsertedIntegerDivision = Table.AddColumn(AddedIndex, "Integer-Division", each Number.IntegerDivide([Index], 5), Int64.Type),

    GroupData = Table.Group(
                        InsertedIntegerDivision, 
                        {"Integer-Division"}, 
                        {{"From", each List.Min([Times]), type datetime}, {"To", each List.Max([Times]), type datetime}})
in
    GroupData

[PattemManohar · Accepted solution]

@Anonymous  Please try below steps:

Create a New Column as below

DateTime5Sec = Test225TimeGen[DateTime]+TIME(0,0,5)

One more column as below

Rnk = RANKX(Test225TimeGen,Test225TimeGen[DateTime],,ASC)

Create a New Table (supporting table) for the main logic

Test225Series = 
VAR _Count = COUNTROWS(Test225TimeGen)
VAR _5Series = GENERATESERIES(6,_Count,5)
RETURN _5Series

Create New Column in the source table to flag which records we want to retain as below:

Flag = IF(Test225TimeGen[Rnk] IN VALUES(Test225Series[Value]) || Test225TimeGen[Rnk] = 1,"Y","N")

Finally, if we filter on the Flag field with Y - expected output will be

Sample Input Data