Pivoting/Grouping Data based on dates

[endee]

Hi all, this is my first post so apologies if I haven't done this exactly right!

I have a table of requests and I need to evaluate if the request was created by a manager or a manager's delegate (and if the person was a delegate at the time of the request)

The issue is that a manager can have multiple delegates and the same person could be added or removed as a delegate multiple times

This is what the requests data looks like

Request Number	Requestee	Requester	Requestee Manager	Request Date
1111	Tim	John	John	01/07/2024
1112	Fred	Tim	Tim	01/07/2024
1113	Sam	Tim	John	02/07/2024
1114	Tim	John	John	04/07/2024

This is what the Delegate data looks like

Manager	Delegate	Action	Date
John	Mike	Add delegate	01/07/2024
John	Stan	Add delegate	01/07/2024
John	Mike	Remove delegate	03/07/2024
John	Mike	Add delegate	06/07/2024
John	Greg	Add delegate	06/07/2024
John	Stan	Remove delegate	07/07/2024
Matt	Robin	Add delegate	01/07/2024
Matt	Harper	Add delegate	01/07/2024
Jeremy	Hannah	Add delegate	01/07/2024
Jeremy	Hannah	Remove delegate	07/07/2024
Jeremy	Mike	Add delegate	07/07/2024

I was trying to somehow transform the data to add an Add delegate and Remove delegate column based off the Action and Date columns, but I would also need it to group the sequential add and remove dates and create a new row for each new add delegate action

This would be my desired output

Manager	Delegate	Add delegate	Remove delegate
John	Mike	01/07/2024	03/07/2024
John	Mike	06/07/2024
John	Stan	01/07/2024	07/07/2024
John	Greg	06/07/2024
Matt	Robin	01/07/2024
Matt	Harper	01/07/2024
Jeremy	Hannah	01/07/2024	07/07/2024
Jeremy	Mike	07/07/2024

If the data was structured this way I'd be able to merge it into the requests data to confirm if the requester was a delegate at the time it was requested

Any help with this would be greatly appreciated

[dufoq3]

Hi @endee, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfLNzE4FUo4pKQopqTmp6YklIK6Bob6Bub6RgZGJUqwOXG1wSWIesWqh5gal5uaXpaIoN8ajHN1oM2xq3YtS04lVC3UyFmeYoyj3TSwpAanLT8ok7EWoYo/EooLUIsIBklqUmlsJVp+Xl5hBhnqCrodrwRGMSKpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Manager = _t, Delegate = _t, Action = _t, Date = _t]),
    GroupedRows = Table.Group(Source, {"Manager", "Delegate"}, {{"A", each 
        [ a = List.Transform({0..Table.RowCount(_)-1}, (x)=> if [Action]{x} = "Add delegate" then {[Date]{x}, null} else {null, [Date]{x}}),
          b = Table.FromRows(a, type table[Add delegate=date, Remove delegate=date]),
          c = Table.FillUp(b, {"Remove delegate"}),
          d = Table.SelectRows(c, (x)=> x[Add delegate] <> null)
        ][d], type table }}),
    ExpandedA = Table.ExpandTableColumn(GroupedRows, "A", {"Add delegate", "Remove delegate"}, {"Add delegate", "Remove delegate"}),
    ChangedType = Table.TransformColumnTypes(ExpandedA,{{"Add delegate", type date}, {"Remove delegate", type date}})
in
    ChangedType

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="delgate"]}[Content],
    sort = Table.Sort(Source,{{"Manager", Order.Ascending}, {"Delegate", Order.Ascending}, {"Date", Order.Ascending}}),
    group = Table.Group(
        sort, 
        {"Manager", "Delegate", "Action"}, 
        {{"Add delegate", (x) => x{[Action = "Add delegate"]}[Date]},
        {"Remove delegate", (x) => try x{[Action = "Remove delegate"]}[Date] otherwise null}},
        GroupKind.Local, 
        (s, c) => Number.From(c[Action] = "Add delegate")
    ),
    z = Table.RemoveColumns(group, "Action")
in
    z

[endee]

This has worked prefectly, thank you very much!