Forum Discussion

MRM_CCM's avatar
MRM_CCM
Regular Visitor
1 year ago
Solved

Recreating excel operations

I'm not sure how to recreate a set of steps I achieved in excel with Power Query. The data is a record of time punches. However they are all  just recorded as in in one column and out in another. For...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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

MRM_CCM's avatar
MRM_CCM
Regular Visitor
1 year ago
LastnameFirstnameInPunchDateInPunchTimeOutPunchDateOutPunchTimeEarnHoursh1h2h3
JonesIndiana1/2/20256:27 AM1/2/202510:57 AM4.58.08 8.08
JonesIndiana1/2/202511:56 AM1/2/20253:31 PM3.58000
JonesIndiana1/3/20256:28 AM1/3/202510:55 AM4.458.0508.05
JonesIndiana1/3/202511:54 AM1/3/20253:30 PM3.6000
JonesIndiana1/4/20256:58 AM1/4/202512:00 PM5.0305.035.03
JonesIndiana1/6/20256:28 AM1/6/202510:55 AM4.458.0508.05
JonesIndiana1/6/202511:54 AM1/6/20253:30 PM3.6000
JonesIndiana1/7/20256:28 AM1/7/202510:54 AM4.438.0508.05
JonesIndiana1/7/202511:53 AM1/7/20253:30 PM3.62000
ConnerSarah1/2/20257:28 AM1/2/202512:13 PM4.758.5708.57
ConnerSarah1/2/20251:11 PM1/2/20255:00 PM3.82000
ConnerSarah1/3/20257:29 AM1/3/202512:11 PM4.78.5208.52
ConnerSarah1/3/20251:11 PM1/3/20255:00 PM3.82000
ConnerSarah1/6/20257:30 AM1/6/202512:54 PM5.48.5208.52
ConnerSarah1/6/20251:54 PM1/6/20255:01 PM3.12000
ConnerSarah1/7/20257:29 AM1/7/202512:43 PM5.238.5308.53
ConnerSarah1/7/20251:43 PM1/7/20255:01 PM3.3000
SundarRaj's avatar
SundarRaj
Super User
1 year ago

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

  • MRM_CCM's avatar
    MRM_CCM
    Regular Visitor
    1 year ago

    I;ve got a long way to go before I inderstand all of what you have going on there, but I was able to patch it into my query and it worked out great. Thankyou very much.