Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

create list of periods from list of date&time

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

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@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

 

image.png

 

Sample Input DataSample Input Data





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

PattemManohar
Community Champion
Community Champion

@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

 

image.png

 

Sample Input DataSample Input Data





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors