Help needed with grouping of data

[sdhasan]

Hi guys,

I need help with sorting data in the below table. As you can see, the same person no (62) has two different cost center (1210 & 1220). I want a new table which basically mentions the first date of the cost center and the last date of the cost center as shown below:

Current table:

Required Table:

 

[JGordon11]

in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    tbl1 = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
    tbl2 = Table.AddColumn(tbl1, "IsStart", each try if [Cost Centre] <> tbl1[Cost Centre]{[Index] - 1}  or [#"Pers.No."] <> tbl1[#"Pers.No."]{[Index] - 1} then true else false otherwise true),
    tbl3 = Table.AddColumn(tbl2, "IsEnd", each try if [Cost Centre] <> tbl1[Cost Centre]{[Index] + 1}  or [#"Pers.No."] <> tbl1[#"Pers.No."]{[Index] + 1} then true else false otherwise true),
    tbl4 = Table.SelectRows(tbl3, each [IsStart] or [IsEnd]),
    tbl5 = Table.FromRecords(Table.TransformRows(tbl4, each if not [IsEnd] then Record.TransformFields(_, {"End Date", (x)=> null}) else _)),
    tbl6 = Table.FillUp(tbl5,{"End Date"}),
    tbl7 = Table.SelectRows(tbl6, each [IsStart]),
    Result = Table.RemoveColumns(tbl7,{"Index", "IsStart", "IsEnd"})
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J
1	Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre
2	62	1/1/2022	1/9/2022	1		62	1/1/2022	1/9/2022	1
3	62	1/10/2022	1/31/2022	2		62	1/10/2022	3/15/2022	2
4	62	2/1/2022	2/28/2022	2		62	3/16/2022	5/10/2022	1
5	62	3/1/2022	3/15/2022	2		62	5/11/2022	8/19/2022	2
6	62	3/16/2022	3/31/2022	1
7	62	4/1/2022	4/30/2022	1
8	62	5/1/2022	5/10/2022	1
9	62	5/11/2022	5/31/2022	2
10	62	6/1/2022	6/30/2022	2
11	62	7/1/2022	7/31/2022	2
12	62	8/1/2022	8/19/2022	2
13
Sheet1

 

[sdhasan]

in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    tbl1 = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
    tbl2 = Table.AddColumn(tbl1, "IsStart", each try if [Cost Centre] <> tbl1[Cost Centre]{[Index] - 1}  or [#"Pers.No."] <> tbl1[#"Pers.No."]{[Index] - 1} then true else false otherwise true),
    tbl3 = Table.AddColumn(tbl2, "IsEnd", each try if [Cost Centre] <> tbl1[Cost Centre]{[Index] + 1}  or [#"Pers.No."] <> tbl1[#"Pers.No."]{[Index] + 1} then true else false otherwise true),
    tbl4 = Table.SelectRows(tbl3, each [IsStart] or [IsEnd]),
    tbl5 = Table.FromRecords(Table.TransformRows(tbl4, each if not [IsEnd] then Record.TransformFields(_, {"End Date", (x)=> null}) else _)),
    tbl6 = Table.FillUp(tbl5,{"End Date"}),
    tbl7 = Table.SelectRows(tbl6, each [IsStart]),
    Result = Table.RemoveColumns(tbl7,{"Index", "IsStart", "IsEnd"})
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J
1	Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre
2	62	1/1/2022	1/9/2022	1		62	1/1/2022	1/9/2022	1
3	62	1/10/2022	1/31/2022	2		62	1/10/2022	3/15/2022	2
4	62	2/1/2022	2/28/2022	2		62	3/16/2022	5/10/2022	1
5	62	3/1/2022	3/15/2022	2		62	5/11/2022	8/19/2022	2
6	62	3/16/2022	3/31/2022	1
7	62	4/1/2022	4/30/2022	1
8	62	5/1/2022	5/10/2022	1
9	62	5/11/2022	5/31/2022	2
10	62	6/1/2022	6/30/2022	2
11	62	7/1/2022	7/31/2022	2
12	62	8/1/2022	8/19/2022	2
13
Sheet1

Ola Gordon!

I've never used a power query, could you guide me how to use it?

Best Regards,
Syed Hasan Danish

 

[Peter_SSs]

If you wanted a formula approach ..

If you have the HSTACK function you can use the formula in F2, otherwise two formulas as in K2 and M2.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

22 12 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre
2	62	1/01/2022	9/01/2022	1		62	1/01/2022	9/01/2022	1		62	1/01/2022	9/01/2022	1
3	62	10/01/2022	31/01/2022	2		62	10/01/2022	15/03/2022	2		62	10/01/2022	15/03/2022	2
4	62	1/02/2022	28/02/2022	2		62	16/03/2022	10/05/2022	1		62	16/03/2022	10/05/2022	1
5	62	1/03/2022	15/03/2022	2		62	11/05/2022	19/08/2022	2		62	11/05/2022	19/08/2022	2
6	62	16/03/2022	31/03/2022	1
7	62	1/04/2022	30/04/2022	1
8	62	1/05/2022	10/05/2022	1
9	62	11/05/2022	31/05/2022	2
10	62	1/06/2022	30/06/2022	2
11	62	1/07/2022	31/07/2022	2
12	62	1/08/2022	19/08/2022	2
13
Group

Cell Formulas
Range	Formula
F2:I5	F2	=HSTACK(FILTER(A2:B12,D2:D12<>D1:D11),FILTER(C2:D12,D2:D12<>D3:D13))
K2:L5	K2	=FILTER(A2:B12,D2:D12<>D1:D11)
M2:N5	M2	=FILTER(C2:D12,D2:D12<>D3:D13)
Dynamic array formulas.

 

[sdhasan]

If you wanted a formula approach ..

If you have the HSTACK function you can use the formula in F2, otherwise two formulas as in K2 and M2.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

22 12 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre		Pers.No.	Start Date	End Date	Cost Centre
2	62	1/01/2022	9/01/2022	1		62	1/01/2022	9/01/2022	1		62	1/01/2022	9/01/2022	1
3	62	10/01/2022	31/01/2022	2		62	10/01/2022	15/03/2022	2		62	10/01/2022	15/03/2022	2
4	62	1/02/2022	28/02/2022	2		62	16/03/2022	10/05/2022	1		62	16/03/2022	10/05/2022	1
5	62	1/03/2022	15/03/2022	2		62	11/05/2022	19/08/2022	2		62	11/05/2022	19/08/2022	2
6	62	16/03/2022	31/03/2022	1
7	62	1/04/2022	30/04/2022	1
8	62	1/05/2022	10/05/2022	1
9	62	11/05/2022	31/05/2022	2
10	62	1/06/2022	30/06/2022	2
11	62	1/07/2022	31/07/2022	2
12	62	1/08/2022	19/08/2022	2
13
Group

Cell Formulas
Range	Formula
F2:I5	F2	=HSTACK(FILTER(A2:B12,D2:D12<>D1:D11),FILTER(C2:D12,D2:D12<>D3:D13))
K2:L5	K2	=FILTER(A2:B12,D2:D12<>D1:D11)
M2:N5	M2	=FILTER(C2:D12,D2:D12<>D3:D13)
Dynamic array formulas.

Hey! Thank you so much, the HSTACK function worked like a charm!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.