Group by continuous sequence

[Anonymous]

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

[Anonymous]

I'm so happy, I spent a few hours on this problem only to discover that I was missing two words GroupKind.Local...
Thank you so much

[BA_Pete]

No problem, happy to help.

Feel free to give a thumbs-up on any posts that help you 👍

Pete

[BA_Pete · Accepted solution]

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
    groupIdTeamLocal

Example query output:

Pete