Forum Discussion
Group by continuous sequence
Hello everybody.
I have problem with power query
I would like to transform a table like the exemple by grouping with IdDemand + Team with. I want the Min DateTime and the Max DateTime for each continuous sequence of IdDemand + Team
The team can learn at different times later
Below the example:
Datas
| IdDemand | Team | CreateDateTime |
| 1 | A | 1/7/23 12:00 |
| 1 | B | 1/7/23 14:00 |
| 1 | B | 2/7/23 2:00 |
| 1 | C | 4/7/23 5:00 |
| 1 | B | 5/7/23 5:30 |
| 1 | B | 6/7/23 1:00 |
| 1 | B | 6/7/23 20:30 |
| 1 | A | 7/7/23 0:50 |
| 2 | C | 8/7/23 0:00 |
| 2 | C | 9/7/23 0:00 |
| 2 | B | 10/7/23 0:00 |
| 2 | B | 11/7/23 0:00 |
| 2 | B | 12/7/23 0:00 |
| 2 | B | 12/7/23 23:00 |
| 2 | A | 13/7/23 0:00 |
| 2 | B | 14/7/23 0:00 |
| 2 | B | 15/7/23 0:00 |
| 2 | C | 16/7/23 0:00 |
Thank you all for your attention
Hi Anonymous ,
You can use the GroupKind argument to do this.
First, multi-select (Ctrl+Click) [IdDmand] and [Team].
Go to the Home tab > Group By.
In the dialog, create aggreagted columns for [MinDate] as MIN of [CreatedDateTime], and similar for MAX.
Once you've applied this, just add this little bit to the end of your Group By step:
Full example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBJCoAwDEWvIlkLZmisulOPId7/Gg5poS0GF9m8l0+G4wCCHta7aIgDS0e8IMLZm9gKEVrBJqrAflcwrm2/Zi41H9OAtj9xxjLwrBpN4KLGOQ2eMseazx/8vQw9QZ7gH8FSmvev4kWCJ9S5g8ZSnBc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IdDemand " = _t, Team = _t, CreateDateTime = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"IdDemand ", Int64.Type}, {"Team", type text}, {"CreateDateTime", type datetime}}), groupIdTeamLocal = Table.Group(chgTypes, {"IdDemand ", "Team"}, {{"minDate", each List.Min([CreateDateTime]), type nullable datetime}, {"maxDate", each List.Max([CreateDateTime]), type nullable datetime}}, GroupKind.Local) in groupIdTeamLocalExample query output:
Pete
3 Replies
Hi Anonymous ,
You can use the GroupKind argument to do this.
First, multi-select (Ctrl+Click) [IdDmand] and [Team].
Go to the Home tab > Group By.
In the dialog, create aggreagted columns for [MinDate] as MIN of [CreatedDateTime], and similar for MAX.
Once you've applied this, just add this little bit to the end of your Group By step:
Full example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBJCoAwDEWvIlkLZmisulOPId7/Gg5poS0GF9m8l0+G4wCCHta7aIgDS0e8IMLZm9gKEVrBJqrAflcwrm2/Zi41H9OAtj9xxjLwrBpN4KLGOQ2eMseazx/8vQw9QZ7gH8FSmvev4kWCJ9S5g8ZSnBc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IdDemand " = _t, Team = _t, CreateDateTime = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"IdDemand ", Int64.Type}, {"Team", type text}, {"CreateDateTime", type datetime}}), groupIdTeamLocal = Table.Group(chgTypes, {"IdDemand ", "Team"}, {{"minDate", each List.Min([CreateDateTime]), type nullable datetime}, {"maxDate", each List.Max([CreateDateTime]), type nullable datetime}}, GroupKind.Local) in groupIdTeamLocalExample query output:
Pete
No problem, happy to help.
Feel free to give a thumbs-up on any posts that help you 👍
Pete