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 days that have 2 sets of punches I need a total in a new column (Daily Total) at the top row of that set and a 0 at the second row and then for days with only one set of punches I need that single value value in the Daily Total column. To ac;hieve this with excel I used a couple helper columns to check if the date from row to row was the same and if so total those, if not add a zero to Helper Column 1 (h1) using =IF(C2=C3,G2+G3,0)

In Helper Column 2 (h2)  I checked if the values in h1 were the same using =IF(AND(H2=0,H3=0),G3,0) starting in the second row of data.

I then used helper column 3 (h3) to bring over any values from h1 and h2 that were not zeros into a single column with =IF(H2<>0,H2,IF(AND(H2=0,I2=0),0,I2))

I recognize there may be a more elegant solution to this in excel, but it worked. I need a way to achieve this same conditional data aggregation in power query so that I don't have to clean the excel data the same way every month and can just dump the raw excel report into the folder the Power BI report is pointed at and let Power Query run the steps.

 

 

Thanks team

 

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

7 Replies

    • MRM_CCM's avatar
      MRM_CCM
      Regular Visitor
      1 year ago

      I tried to uplad a csv and a xlsx but kept getting an error that those weren't supported

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

    • MRM_CCM's avatar
      MRM_CCM
      Regular Visitor
      1 year ago

      Expirimenting with  test data below I tried this: 
      = Table.AddColumn(#"Changed Type", "Custom", each if Table.Column(#"Changed Type","Column1"){0} = Table.Column(#"Changed Type","Column1"){1} then 4 else 5)
      which got me a custom column with all 4's

      Column1Column2

      1

      		3
      14
      24
      35
      33
      42
      55
      54

       

  • MRM_CCM's avatar
    MRM_CCM
    Regular Visitor
    1 year ago

    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.