Performance of counting overlapped datetime

[hirokichi]

Hi there.

The dataset consists of columns for ID, start time, and end time, with tens of thousands of rows.
I would like to aggregate other rows that overlap in time based on the start time of each row.
I created the query using the answer , but the performance is poor.
How do I create a better query?

#DataSet

Download 

#Example

ID	StartDateTime	EndDateTime	Overlap
1	2021/11/25 00:00:00	2021/11/25 02:00:00	??? -> 1
2	2021/11/25 01:00:00	2021/11/25 02:00:00	??? -> 2
3	2021/11/25 03:00:00	2021/11/25 08:00:00	??? -> 1
4	2021/11/25 04:00:00	2021/11/25 07:00:00	??? -> 2
5	2021/11/25 05:00:00	2021/11/25 08:00:00	??? -> 3
…
100000

#Query1

let
    source = Csv.Document(File.Contents("C:\SampleTime.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
    header = Table.PromoteHeaders(source, [PromoteAllScalars=true]),
    convertType = Table.TransformColumnTypes(header,{{"Id", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}}),
    addColumn = Table.AddColumn(convertType, "Temp", each convertType),
    expandColumn = Table.ExpandTableColumn(addColumn, "Temp", {"StartDate", "EndDate"}, {"Temp.StartDate", "Temp.EndDate"}),
    convertType1 = Table.TransformColumnTypes(expandColumn,{{"Temp.StartDate", type datetime}, {"Temp.EndDate", type datetime}}),
    addColumn1 = Table.AddColumn(convertType1, "Compare", each ([Temp.StartDate] <= [StartDate]) and ([StartDate] <= [Temp.EndDate])),
    filterRow = Table.SelectRows(addColumn1, each ([Compare] = true)),
    groupBy = Table.Group(filterRow, {"Id"}, {{"Concurrent", each Table.RowCount(_), Int64.Type}})
in
    groupBy

Query#2

let
    source = Csv.Document(File.Contents("C:\SampleTime.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
    header = Table.PromoteHeaders(source, [PromoteAllScalars=true]),
    convertType = Table.TransformColumnTypes(header,{{"Id", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}}),
    addColumn = Table.AddColumn(convertType, "Overlap", each Table.RowCount(Table.SelectRows(convertType, (x) => x[StartDate] <= [StartDate] and [StartDate] <= x[EndDate])))
in
    addColumn

Best regards,

[Vera_33 · Accepted solution]

Hi @hirokichi 

Groupby is slow, so did those 2 queries above achieve what you wanted? How about buffer the table in Query 2?

let
    source = Csv.Document(File.Contents("C:\SampleTime.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
    header = Table.PromoteHeaders(source, [PromoteAllScalars=true]),
    convertType =Table.Buffer( Table.TransformColumnTypes(header,{{"Id", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}})),
    addColumn = Table.AddColumn(convertType, "Overlap", each Table.RowCount(Table.SelectRows(convertType, (x) => x[StartDate] <= [StartDate] and [StartDate] <= x[EndDate])))
in
    addColumn

try query 3

let
    source = Csv.Document(File.Contents("C:\SampleTime.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
    header = Table.PromoteHeaders(source, [PromoteAllScalars=true]),
    convertType = Table.Buffer( Table.TransformColumnTypes(header,{{"Id", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}})),
    Custom = List.Buffer( Table.AddColumn(convertType, "Custom", each {[StartDate], [EndDate]})[Custom]),
    #"Added Custom1" = Table.AddColumn(convertType, "Overlap", (x)=> List.Count(List.Select( List.Transform(Custom, each _{0} <=x[StartDate] and _{1}>=x[StartDate]), each _=true)))
in
    #"Added Custom1"
    

[hirokichi]

Thank you for your advice.
Queries are much faster!