Forum Discussion
Solved
GROUP with MIN and MAX
Hi experts!
I would like to pivot / group a huge table that has this structure:
| Order Nr | Status | Date |
| 451 | A | 01.01.2022 |
| 451 | A | 02.01.2022 |
| 451 | B | 05.01.2022 |
| 452 | A | 05.01.2022 |
| 452 | C | 08.01.2022 |
Now I would like to get for each Order Nr
- The MIN for Status A
- And the MAX for Status B & C
In the end it should look like that:
| Order NR | MIN | MAX |
| 451 | 01.01.2022 | 05.01.2022 |
| 452 | 05.01.2022 | 08.01.2022 |
How is this possible in Power Query?
Hi joshua1990
This is my solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1VNJRcgRiA0M9IDIyMDJSitVBkTDCIuEEkjBFkzCC6cAm4QySsEBIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Status = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Status", type text}, {"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "new Status", each if [Status] = "A" then "A" else "B & C"), #"Grouped Rows" = Table.Group(#"Added Custom", {"Order Nr", "new Status"}, {{"All Data", each _, type table [Order Nr=nullable number, Status=nullable text, Date=nullable date, new Status=text]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "MIN Date", each List.Min([All Data][Date])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "MAX Date", each List.Max([All Data][Date])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MIN", each if [new Status] = "A" then [MIN Date] else null), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "MAX", each if [new Status] = "B & C" then [MAX Date] else null), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"Order Nr", "MIN", "MAX"}), #"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"Order Nr"}, {{"MIN", each List.Max([MIN]), type nullable date}, {"MAX", each List.Max([MAX]), type nullable date}}) in #"Grouped Rows1"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi joshua1990 ,
In your case only simple group by based on Order Nr would be sufficient.
Can you give a better example with Status A and B,C?Hi joshua1990
This is my solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1VNJRcgRiA0M9IDIyMDJSitVBkTDCIuEEkjBFkzCC6cAm4QySsEBIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Status = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Status", type text}, {"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "new Status", each if [Status] = "A" then "A" else "B & C"), #"Grouped Rows" = Table.Group(#"Added Custom", {"Order Nr", "new Status"}, {{"All Data", each _, type table [Order Nr=nullable number, Status=nullable text, Date=nullable date, new Status=text]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "MIN Date", each List.Min([All Data][Date])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "MAX Date", each List.Max([All Data][Date])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MIN", each if [new Status] = "A" then [MIN Date] else null), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "MAX", each if [new Status] = "B & C" then [MAX Date] else null), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"Order Nr", "MIN", "MAX"}), #"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"Order Nr"}, {{"MIN", each List.Max([MIN]), type nullable date}, {"MAX", each List.Max([MAX]), type nullable date}}) in #"Grouped Rows1"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
