Forum Discussion
Combine data
Hi,
How to summarise data by employee id , with the continus departure and arrival date and same location provide the total number of days away .
If same employee, same location but not continuouly based on departure and arrival date - should show as a separate transaction and the number of days separately.
| Employee ID | Employee.displayName | TemporaryTransferLocation | Count of TemporaryTransferLocation | DateofDeparture1 | DateofArrival1 | Sum of Number of days away |
| 201032 | Anthony Fitzgerald | Yarrawonga | 1 | 26/05/2024 | 30/05/2024 | 4 |
| 201032 | Anthony Fitzgerald | Yarrawonga | 1 | 11/07/2024 | 15/07/2024 | 4 |
| 201032 | Anthony Fitzgerald | Yarrawonga | 1 | 22/09/2024 | 30/09/2024 | 8 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 6/04/2024 | 19/04/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 20/04/2024 | 3/05/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 4/05/2024 | 17/05/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 19/05/2024 | 1/06/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 1/06/2024 | 14/06/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 15/06/2024 | 28/06/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 29/06/2024 | 12/07/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 13/07/2024 | 26/07/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 10/08/2024 | 23/08/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 24/08/2024 | 6/09/2024 | 13 |
| 202718 | Raymond Parkes (PTW) | Mudgee | 1 | 7/09/2024 | 20/09/2024 | 13 |
All you need to do is to create new rows for each of the days, and then use GroupKind.Local to collect the individual contiguous ranges.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLNCsIwEIRfpfSkUMhmk/7k6MWbUEQQKR4CLVXUFmpF6tO7PRiXntrcdgbyMbuZoggRJCgMo3DT9Je2GYLttf/UVWfvJZkn23X23Ta1JYGJgFggoCahwIlztAgjpYD0h5GxEwsxiAIMT2M4BlOZkb+3w6NtyiC33a16Bqv8cFyTvXuVdVXRQBtpF8U4sYiBwCBqcpWZDM0uK1M/xpj/DxGQ+DDcM5q1JyNmEMz8IGh4EpyUZG4SxZo2ttcLQt+bOYhyYtk6mkESv66mrO/I+n7+Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Employee.displayName = _t, TemporaryTransferLocation = _t, DateofDeparture1 = _t, DateofArrival1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateofDeparture1", type date}, {"DateofArrival1", type date}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([DateofDeparture1],Int64.From([DateofArrival1]-[DateofDeparture1])+1,#duration(1,0,0,0))), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Employee ID", Order.Ascending}, {"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Employee ID","Date","Index"}, {{"Days Away", each Table.RowCount(_), Int64.Type}, {"Rows", each _, type table [Employee ID=nullable text, Employee.displayName=nullable text, TemporaryTransferLocation=nullable text, DateofDeparture1=nullable date, DateofArrival1=nullable date, Date=nullable date, Index=number]}},GroupKind.Local,(first,current)=>Number.From(current[Employee ID]<>first[Employee ID] or Int64.From(current[Date]-first[Date])>current[Index]-first[Index])) in #"Grouped Rows"Note that your "Number of days" values are ambiguous. Is the range from 26/05/2024 to 30/05/2024 four days? Or is it five days?
5 Replies
All you need to do is to create new rows for each of the days, and then use GroupKind.Local to collect the individual contiguous ranges.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLNCsIwEIRfpfSkUMhmk/7k6MWbUEQQKR4CLVXUFmpF6tO7PRiXntrcdgbyMbuZoggRJCgMo3DT9Je2GYLttf/UVWfvJZkn23X23Ta1JYGJgFggoCahwIlztAgjpYD0h5GxEwsxiAIMT2M4BlOZkb+3w6NtyiC33a16Bqv8cFyTvXuVdVXRQBtpF8U4sYiBwCBqcpWZDM0uK1M/xpj/DxGQ+DDcM5q1JyNmEMz8IGh4EpyUZG4SxZo2ttcLQt+bOYhyYtk6mkESv66mrO/I+n7+Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Employee.displayName = _t, TemporaryTransferLocation = _t, DateofDeparture1 = _t, DateofArrival1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateofDeparture1", type date}, {"DateofArrival1", type date}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([DateofDeparture1],Int64.From([DateofArrival1]-[DateofDeparture1])+1,#duration(1,0,0,0))), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Employee ID", Order.Ascending}, {"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Employee ID","Date","Index"}, {{"Days Away", each Table.RowCount(_), Int64.Type}, {"Rows", each _, type table [Employee ID=nullable text, Employee.displayName=nullable text, TemporaryTransferLocation=nullable text, DateofDeparture1=nullable date, DateofArrival1=nullable date, Date=nullable date, Index=number]}},GroupKind.Local,(first,current)=>Number.From(current[Employee ID]<>first[Employee ID] or Int64.From(current[Date]-first[Date])>current[Index]-first[Index])) in #"Grouped Rows"Note that your "Number of days" values are ambiguous. Is the range from 26/05/2024 to 30/05/2024 four days? Or is it five days?
Thanks for the solution really helpful. Continuing on that., can you please assist on the below:
1. Same employee in a same location , continuouly away to be aggreegated the total number of days away
2. When aggreegating the number of days based on location, there are same locaiton but due to free text , there are misspelled words or short form is used. How can we aggregate thouse , if there way we can put some condition. Here are some examples
Employee id Location date Contigous end date Sum of days away 12345 HBT 24/8/2024 6/9/2024 14 12345 H.B.T Pt Waratah 7/9/2024 20/9/2024 14 12345 Port Waratah (HBT) 21/9/2024 4/10/2024 14 12345 PTWaratah (H.B.T) 5/10/2024 18/10/2024 14 Not exactly sure what you are asking. You could compare the intersection of the individual calendars with the overall min/max calendar and if the number of rows match then it's contiguous.
Not sure what the "misspelled words" have to do with that as long as the employee ID is the same.
Even employee id is same, the location is important for tax purpsoes. If the employee is continously in the same locafion for more than 90 days it is subject to tax. So even I group by employee id , I still need to group by location and date to see whether employee is " contiously" away from location and it should be same location over 90 days. That's were the location is important
