Recreating excel operations

[MRM_CCM]

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

[MRM_CCM]

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.

[MRM_CCM]

Lastname	Firstname	InPunchDate	InPunchTime	OutPunchDate	OutPunchTime	EarnHours	h1	h2	h3
Jones	Indiana	1/2/2025	6:27 AM	1/2/2025	10:57 AM	4.5	8.08		8.08
Jones	Indiana	1/2/2025	11:56 AM	1/2/2025	3:31 PM	3.58	0	0	0
Jones	Indiana	1/3/2025	6:28 AM	1/3/2025	10:55 AM	4.45	8.05	0	8.05
Jones	Indiana	1/3/2025	11:54 AM	1/3/2025	3:30 PM	3.6	0	0	0
Jones	Indiana	1/4/2025	6:58 AM	1/4/2025	12:00 PM	5.03	0	5.03	5.03
Jones	Indiana	1/6/2025	6:28 AM	1/6/2025	10:55 AM	4.45	8.05	0	8.05
Jones	Indiana	1/6/2025	11:54 AM	1/6/2025	3:30 PM	3.6	0	0	0
Jones	Indiana	1/7/2025	6:28 AM	1/7/2025	10:54 AM	4.43	8.05	0	8.05
Jones	Indiana	1/7/2025	11:53 AM	1/7/2025	3:30 PM	3.62	0	0	0
Conner	Sarah	1/2/2025	7:28 AM	1/2/2025	12:13 PM	4.75	8.57	0	8.57
Conner	Sarah	1/2/2025	1:11 PM	1/2/2025	5:00 PM	3.82	0	0	0
Conner	Sarah	1/3/2025	7:29 AM	1/3/2025	12:11 PM	4.7	8.52	0	8.52
Conner	Sarah	1/3/2025	1:11 PM	1/3/2025	5:00 PM	3.82	0	0	0
Conner	Sarah	1/6/2025	7:30 AM	1/6/2025	12:54 PM	5.4	8.52	0	8.52
Conner	Sarah	1/6/2025	1:54 PM	1/6/2025	5:01 PM	3.12	0	0	0
Conner	Sarah	1/7/2025	7:29 AM	1/7/2025	12:43 PM	5.23	8.53	0	8.53
Conner	Sarah	1/7/2025	1:43 PM	1/7/2025	5:01 PM	3.3	0	0	0

[SundarRaj · Accepted solution]

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]

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]

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

Column1	Column2
1	3
1	4
2	4
3	5
3	3
4	2
5	5
5	4

[adudani]

Hello @MRM_CCM ,

Kindly provide the sample input/output in a usable format (excel, csv, table etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

Thanks,

[MRM_CCM]

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