GROUP with MIN and MAX

[joshua1990]

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?

[v-jingzhang · Accepted solution]

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.

[Migasuke]

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?