Unpivot and Group by Min and Max Value

[joshua1990]

Hi all!

I have a transactional table that I would like to transform in an appropriate structure so I can consume the result in Power BI using DAX.

The structure is like this:

Article	Order	Key 1	Key 2	Date
1	01	AC	1	01.01.2020
1	01	BD	1	01.01.2020
1	01	BD	2	02.01.2020
1	01	BD	3	08.01.2020
1	01	FG	1	15.01.2020

For each Article, each Order and each Key 1 I would like to get the MIN Date if Key 2 = 1 and the MAX date if the Key 2 = 2 or 3

The result should look like this:

Article	Order	Key 1	MIN Date	MAX Date
1	01	AC	01.01.2020
1	01	BD	01.01.2020	08.01.2020
1	01	FG	15.01.2020

How is that possible using PQ?

[jsaunders_zero9 · Accepted solution]

Hi   @joshua1990 

Group by columns Article, Order and Key 1 and then for each date column filter the grouped data for only Key 2 = x as required and select the max and min dates

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", Int64.Type}, {"Order", Int64.Type}, {"Key 1", type text}, {"Key 2", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article", "Order", "Key 1"}, {{"data", each _, type table [Article=nullable number, Order=nullable number, Key 1=nullable text, Key 2=nullable number, Date=nullable date]}}),
    #"Added Min Column" = Table.AddColumn(#"Grouped Rows", "MIN Date", each List.Min(Table.Column(Table.SelectRows([data],each [Key 2] = 1),"Date")), type date),
    #"Added Max Column" = Table.AddColumn(#"Added Min Column", "MAX Date", each List.Max(Table.Column(Table.SelectRows([data],each [Key 2] <> 1),"Date")), type date),
    #"Removed data Column" = Table.RemoveColumns(#"Added Max Column",{"data"})
in
    #"Removed data Column"