Overlapping time ranges

Unfortunately not, 

some consultants suggested that this kind of transformation cannot be managed in PowerQuery-Dax.

However this was my work:

Source

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBDcAgCAXQVRrPJuJX1HaJDmDcofufGmtSBa+fJwK1mvvxxhp2yA4EHD5dRCMpI6EeNNspJI0/FY+HDbLCmy3TRlEB9Ah+UpbUa4pJk/wv6wmwbJZFE9Zdl1mLKAQtlwuc+w5r8F2vtRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"StartDate", type datetime}, {"EndDate", type datetime}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Duration", each Duration.TotalMinutes([EndDate] - [StartDate])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time Subtraction",{{"Duration", Int64.Type}})
in
    #"Changed Type1"

Output

let
    CleanOverlaps = fn_CleanOverlaps(Source,"Activity","StartDate","EndDate"),
    #"Merged Queries" = Table.NestedJoin(Source, {"Activity"}, CleanOverlaps, {"Activity"}, "CleanOverlaps", JoinKind.LeftOuter),
    #"Expanded CleanOverlaps" = Table.ExpandTableColumn(#"Merged Queries", "CleanOverlaps", {"CleanedDuration"}, {"CleanedDuration"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded CleanOverlaps",null,0,Replacer.ReplaceValue,{"CleanedDuration"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "OverlapDuration", each [Duration] - [CleanedDuration], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Subtraction",{{"CleanedDuration", Int64.Type}, {"OverlapDuration", Int64.Type}})
in
    #"Changed Type"

This is the sequence of nested functions I developed, in the mean time I forgot how they work.

if you find a way to improve them please let me know, the funny thing is that I was able to do much more advanced things in VBA 🤔 then PowerQuery-DAX:

fn_CleanOverlaps

(Tab as table,  KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    MaxLoop = Table.RowCount(Tab),
    SortedTab = Table.Sort(Tab,{{StartD_S,Order.Ascending},{EndD_S,Order.Ascending}}),
    OverlapsChanged = #"private fn_CleanOverlaps (loop)"(SortedTab,MaxLoop - 1,-1,{},KeyCol_S,StartD_S,EndD_S),
    RemoveSkips = Table.SelectRows(OverlapsChanged[Tab], each not List.Contains(OverlapsChanged[Skip], [Activity])),
    AddDuration = Table.AddColumn(RemoveSkips, "CleanedDuration", each Duration.TotalMinutes([EndDate] - [StartDate]))

in

    AddDuration

private fn_CleanOverlaps (loop)

(Tab as table, MaxLoop as number, StartLoop as number, SkipList as list,KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    CurrentLoop = StartLoop + 1,
    LoopTab = #"private fn_CleanOverlaps (Single Record work)"(Tab, CurrentLoop,SkipList,KeyCol_S,StartD_S,EndD_S),
    Output = if CurrentLoop >= MaxLoop then
            LoopTab
        else
           @#"private fn_CleanOverlaps (loop)"(LoopTab[Tab],MaxLoop, CurrentLoop,LoopTab[Skip],KeyCol_S,StartD_S,EndD_S)

in
    Output

private fn_CleanOverlaps (Single Record work)

(Tab as table, n as number,SkipList as list, KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    MyRecord = Tab{n},//Target Record
    MyKey = Record.Field(MyRecord,KeyCol_S),//Target Key
    DeleteOtherRows = Table.SelectColumns(Tab,{KeyCol_S,StartD_S,EndD_S}),
    A_End = Record.Field(MyRecord,EndD_S),

    RemoveMyRecord = Table.SelectRows(DeleteOtherRows, each (Record.Field(_,KeyCol_S) <> MyKey)),//Remove Record

    ModRecord = #"private fn_CleanOverlaps (modify A Record)"(RemoveMyRecord,MyRecord,KeyCol_S,StartD_S,EndD_S),
    Mod_AStart = Record.Field(ModRecord,StartD_S),
    Mod_AEnd = Record.Field(ModRecord,EndD_S),

    Mod_TF =    if A_End <> Mod_AEnd then
                    true  
                else  
                    false,

    
    AddRelation = Table.AddColumn(RemoveMyRecord, "Relation", each fn_TimeRangeRelation(Mod_AStart,Mod_AEnd,Record.Field(_,StartD_S),Record.Field(_,EndD_S))),

    nSkipList = Table.ToList( Table.SelectColumns( Table.SelectRows(AddRelation, each not List.Contains({"NoRelation","BcontainsA"}, [Relation])),{KeyCol_S})),

    SkipList =  if List.IsEmpty(SkipList) then  
                    nSkipList  
                else  
                    List.Combine({SkipList,nSkipList}),

    DeleteRelCol = Table.RemoveColumns(AddRelation,{"Relation"}),
    AddNewRecord = Table.InsertRows(DeleteRelCol,n,{ModRecord})

in
    [
    IntialRecord = MyRecord,
    Tab = AddNewRecord,
    Skip = SkipList,
    n = n]

private fn_CleanOverlaps (modify A Record) 

(Tab as table, MyRecord as record, KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let

    A_Key = Record.Field(MyRecord,KeyCol_S),
    A_Start = Record.Field(MyRecord,StartD_S),
    A_End = Record.Field(MyRecord,EndD_S),

    AddRelation = Table.AddColumn(Tab, "Relation", each fn_TimeRangeRelation(A_Start,A_End,Record.Field(_,StartD_S),Record.Field(_,EndD_S))),
    AB_R = List.PositionOf(AddRelation[Relation], "AB_Intersect"),

    ModRecord = if AB_R <> -1 then
                #"private fn_CleanOverlaps (modify A Record)"(Tab, 
                                            Record.FromList( {A_Key, A_Start, Record.Field(AddRelation{AB_R},EndD_S)},{KeyCol_S, StartD_S, EndD_S}), 
                                            KeyCol_S, StartD_S, EndD_S)
                else  
                    MyRecord

in
    ModRecord

fn_TimeRangeRelation

= (A_start as datetime,A_end as datetime ,B_start as datetime, B_end as datetime) as text =>
/*
OutputType options
    "RelationTF"
    "RelType"
    "OverlapTime"
*/

let
    //OutputType = if OutputType is null then OutputType = "RelType" else OutputType,
    A_Duration = Duration.TotalHours(A_end-A_start),
    B_Duration = Duration.TotalHours(B_end-B_start),
    AStart_BEnd = Duration.TotalHours(A_start-B_end),
    BStart_AEnd = Duration.TotalHours(B_start-A_end),
    GetRelation = if A_Duration >= 0 and B_Duration >=0 then
                    if AStart_BEnd >= 0 or BStart_AEnd >= 0 then
                     "NoRelation"
                    else let 
                            StartRelation = Duration.TotalHours(A_start-B_start),
                            EndRelation = Duration.TotalHours(A_end - B_end),
                            RelType = if StartRelation > 0 then //Future
                                        if EndRelation > 0 then // Future
                                            "BA_Intersect"
                                        else if EndRelation < 0 then // Past
                                            "BcontainsA"
                                        else
                                            "BcontainsA"
                                      else if StartRelation < 0 then //Past
                                        if EndRelation > 0 then // Future
                                            "AcontainsB"
                                        else if EndRelation < 0 then // Past
                                            "AB_Intersect"
                                        else
                                            "AcontainsB"   
                                      else  // equal
                                        if EndRelation > 0 then // Future
                                            "AcontainsB"
                                        else if EndRelation < 0 then // Past
                                            "BcontainsA"
                                        else
                                            "AB_equal"  
                         in 
                         RelType
                else "Invalid Ranges"

in

    GetRelation