Copy to Summarise data based on 2 criteria: User ID and gaps in continuous day segments for same user

**ayazgreat** · 12-05-2022, 07:47 PM

**sandy666** · 12-06-2022, 01:30 AM

my three cents with Power Query

Numbers	ID	StartDate	EndDate
12345	22	03/01/2022	06/01/2022
23456	22	03/01/2022	04/01/2022

Code:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"ID", type text}, {"Date", type date}}),
    Group = Table.Group(Type, {"Numbers", "ID"}, {{"StartDate", each List.Min([Date]), type date}, {"EndDate", each List.Max([Date]), type date}})
in
    Group

**ayazgreat** · 12-06-2022, 02:00 AM

Originally Posted by sandy666

my three cents with Power Query

Numbers	ID	StartDate	EndDate
12345	22	03/01/2022	06/01/2022
23456	22	03/01/2022	04/01/2022

Code:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"ID", type text}, {"Date", type date}}),
    Group = Table.Group(Type, {"Numbers", "ID"}, {{"StartDate", each List.Min([Date]), type date}, {"EndDate", each List.Max([Date]), type date}})
in
    Group

Thanks for your reply I just want to know that where should I copy these power query code to run further I have 5 lacs rows of data so how much does it take time to complete ?

**sandy666** · 12-06-2022, 04:05 AM

1. don't quote whole post, this is not necessary
2. update your profile about excel version
3. your source data should be an Excel Table
4. whole work took me approx. 10 seconds to take the result table (including drinking coffee)

5. FYI: this is NOT vba

**sandy666** · 12-06-2022, 04:18 AM

.
.
here is a few examples of Power Query (from real posts)

and here much more about Power Query : Learn Power Query

**ayazgreat** · 12-06-2022, 02:31 PM

I am facing an issue of incorrect result and furthermore if add following column in result sheet so how should power query be updated

**sandy666** · 12-06-2022, 07:00 PM

as you can see there is no date 02/04/2022 in the source data (copied from mrexcel)
attach proper example with detailed description what you want to achieve and we will see what will be

**ayazgreat** · 12-07-2022, 10:50 AM

My Actual required result in attached file in result sheet if anybody can solve it please through VBA code ?

**DocAElstein** · 12-09-2022, 02:40 AM

Hello
I have the same problem that everyone else is having with you.
You are not explaining clearly enough for anyone to understand what you want, and you keep changing your data and results.

So it is impossible for anyone to help you.

For example, in your latest file this is the data

_Row\^Col	A	B	C
1	Numbers	ID	Date
2	12345	22	01/03/2022
3	12345	22	01/03/2022
4	12345	22	01/03/2022
5	12345	22	01/03/2022
6	12345	22	01/05/2022
7	12345	22	01/05/2022
8	12345	22	01/06/2022
9	12345	22	01/06/2022
10	12345	22	04/02/2022
11	12345	22	04/02/2022
12	12345	22	04/02/2022
13	12345	22	04/03/2022
14	12345	22	04/03/2022
15	12345	22	04/04/2022
16	23456	22	01/03/2022
17	23456	22	01/03/2022
18	23456	22	01/03/2022
19	23456	22	01/04/2022
20	23456	22	01/04/2022
21

Worksheet: Data

, and this is your results.

_Row\^Col	A	B	C	D	E	F	G
1	Number	ID	Start Date	End Date	Days	Working Days
2	12345	22	01/03/2022	04/04/2022	92	64	Incorrect
3	23456	22	01/03/2022	01/04/2022	2	1
4
5
6
7	12345	22	01/03/2022	01/06/2022	4	3	Correct
8	12345	22	04/02/2022	04/04/2022	3	0

Worksheet: Result

It is impossible to understand why you have more than one result for 12345

Alan

**ayazgreat** · 12-09-2022, 08:18 AM

Alan,

Please find attached updated file for your question that why I have more than 1 result for 12345.
Actually if you see result sheet I am calculating total days and total working days between start date and end date and all result is based on each number and different dates of each number.