Combine data

[Thiviya]

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

[Thiviya]

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

[lbendlin]

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.

[Thiviya]

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

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin · Accepted solution]

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"

https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

@ImkeF 

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?