Flatten parent child hierarchy based on start date and end date

[grangema]

Hello,

I'm having difficulties to split a parent child hierarchy into a flattened one because there is a start date and end date to take into consideration.

In the example below, employee D has moved from Employee Parent B to C on the 1st of july 2024.

I would expect to see in row number 5 : C|D|F.

Currently, my funtion looks like this, and doesn't include the date as I wasn't able to succeed : 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Suivi budget - Copie.xlsx"), null, true),
    Employee_Table = Source{[Item="Employee",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Employee_Table,{{"Employee", type text}, {"Employee Parent", type text}, {"Join team date", type date}, {"Left team date", type date}, {"Employee Is Manager ?", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Left team date", "Left team date tmp"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Left team date", each if [Left team date tmp]=null then  Date.EndOfYear(DateTime.Date( DateTime.LocalNow() ))
else [Left team date tmp], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Employee Parent", "Employee", "Employee Is Manager ?", "Join team date", "Left team date"}),
    myfunction=(ChildCol,ParentCol,CurrentParent)=>
                    let
                    mylist=Table.Column(Table.SelectRows(#"Removed Other Columns",each Record.Field(_,ChildCol)=CurrentParent),ParentCol),
                    result=Text.Combine(mylist)
                    in
                   
                    Text.TrimEnd(
                    if result ="" then ""  else @ result & "|" & @ myfunction(ChildCol,ParentCol,result),
                   "|"),                           

    Path=Table.AddColumn(#"Removed Other Columns", 
                            "Hierarchy", each 
                                    Text.Trim(
                                    Text.Combine(
                                    List.Reverse(
                                        List.RemoveItems(
                                        Text.Split(myfunction("Employee","Employee Parent",[Employee Parent]),"|"),{""}
                                                        )
                                                 )
                                             &{[Employee Parent],[Employee]}
                                             ,
                                             "|"),"|"))
in
    Path

You advice will be helpfull, thank you.

[dufoq3]

Hi @grangema,

this matches your criteria for now, but maybe we will need some update for more complex data.

Let me know please.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYkMgNjDUByIjAyMTIMfYUN/QCMKJ1YkGq3EBqcFQZ6BvYIZQ5wwUcsWqDtU8Z6h5MHvNcagDqXFDMg+rulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Parent" = _t, Employee = _t, #"Employee Is Manager ?" = _t, #"Join team date" = _t, #"Left team date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Employee Is Manager ?", Int64.Type}, {"Join team date", type date}, {"Left team date", type date}}),
    MergedQueryItself = Table.NestedJoin(ChangedType, {"Employee Parent"}, ChangedType, {"Employee"}, "Hierarchy", JoinKind.LeftOuter),
    TransformChangedType = Table.TransformColumns(MergedQueryItself, {{ "Hierarchy", each 
    try Text.Combine(Record.ToList(Record.SelectFields(Table.Last(Table.Sort(_, {{"Join team date", Order.Ascending}})), {"Employee Parent", "Employee"}, MissingField.Ignore)), "|") otherwise null, type text }}),
    ReplaceChangedType = Table.ReplaceValue( TransformChangedType,
      each [Employee],
      each [Employee Parent] & "|" & [Employee],
      (x,y,z)=> if x = null then z else x & "|" & y,
      {"Hierarchy"} )
in
    ReplaceChangedType

[lbendlin]

Does it have to be Power Query?  In DAX you get all of this for (nearly) free with the PATH functions and FILTER.