Forum Discussion
Recreating excel operations
Hi MRM_CCM , is this what you are looking for? I'll just attach the images of the output and code the used. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lastname", type text}, {"Firstname", type text}, {"InPunchDate", type datetime}, {"InPunchTime", type time}, {"OutPunchDate", type datetime}, {"OutPunchTime", type time}, {"EarnHours", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Lastname", "Firstname", "InPunchDate", "OutPunchDate"}, {{"All", each _},{"Sum", each List.Sum(_[EarnHours])}})[[All],[Sum]],
Try1 = Table.AddColumn(#"Grouped Rows","RowCount", each Table.RowCount(_[All]) - 1),
Try2 = Table.AddColumn(Try1,"List", each {1.._[RowCount]}),
Try3 = Table.TransformColumns(Try2,{"List", each List.Transform(_, each 0)}),
#"Expanded List" = Table.ExpandListColumn(Try3, "List"),
SumNos = List.RemoveNulls(List.Combine(List.Zip({#"Expanded List"[Sum],#"Expanded List"[List]}))),
#"Expanded All" = Table.ExpandTableColumn(#"Expanded List"[[All]], "All", {"Lastname", "Firstname", "InPunchDate", "InPunchTime", "OutPunchDate", "OutPunchTime", "EarnHours"}, {"Lastname", "Firstname", "InPunchDate", "InPunchTime", "OutPunchDate", "OutPunchTime", "EarnHours"}),
FinTable = Table.TransformColumns(Table.AddIndexColumn(#"Expanded All","H3",0,1), {"H3", each SumNos{_}})
in
FinTable
I was messing with the data and learning about Grouipby and found a briefer way to get the results that I needed.
= Table.Group(#"Changed Type1", {"InPunchDate"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Daily Hours", each List.Sum([EarnHours]), type nullable number}, {"Last Name", each List.Min([Lastname]), type nullable text}, {"First Name", each List.Min([Firstname]), type nullable text}}, GroupKind.Local)
This let me skip having to recreate the other columns and group each date for each person and get their total hours for that day.