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 ...
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
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!