Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
christo45
Frequent Visitor

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:

 

Power1.PNG

 

Datas

IdDemand TeamCreateDateTime
1A1/7/23 12:00
1B1/7/23 14:00
1B2/7/23 2:00
1C4/7/23 5:00
1B5/7/23 5:30
1B6/7/23 1:00
1B6/7/23 20:30
1A7/7/23 0:50
2C8/7/23 0:00
2C9/7/23 0:00
2B10/7/23 0:00
2B11/7/23 0:00
2B12/7/23 0:00
2B12/7/23 23:00
2A13/7/23 0:00
2B14/7/23 0:00
2B15/7/23 0:00
2C16/7/23 0:00

 


Thank you all for your attention

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @christo45 ,

 

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:

BA_Pete_0-1694012974533.png

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:

BA_Pete_1-1694013037421.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
christo45
Frequent Visitor

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

 

No problem, happy to help.

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

Hi @christo45 ,

 

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:

BA_Pete_0-1694012974533.png

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:

BA_Pete_1-1694013037421.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors