Forum Discussion
joshua1990
Post Prodigy
Post ProdigyUnpivot and Group by Min and Max Value
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?
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"
1 Reply
- jsaunders_zero9
Responsive Resident
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"