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