-
Copy to Summarise data based on 2 criteria: User ID and gaps in continuous day segments for same user
-
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
-
Quote:
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 ?
-
1 Attachment(s)
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
-
-
1 Attachment(s)
I am facing an issue of incorrect result and furthermore if add following column in result sheet so how should power query be updated
-
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
-
1 Attachment(s)
My Actual required result in attached file in result sheet if anybody can solve it please through VBA code ?
-
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 |
D |
|---|
| 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 |
|
|
|
|
, 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 |
|
It is impossible to understand why you have more than one result for 12345
Alan
-
1 Attachment(s)
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.
