Forum Discussion

endee's avatar
endee
New Member
2 years ago
Solved

Pivoting/Grouping Data based on dates

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

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    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

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
    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's avatar
      endee
      New Member
      2 years ago

      This has worked prefectly, thank you very much! 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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