Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
54 | |
27 | |
17 | |
13 |