Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joshua1990
Post Prodigy
Post Prodigy

Unpivot 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?

1 ACCEPTED SOLUTION
jsaunders_zero9
Responsive Resident
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

 

jsaunders_zero9_0-1672414516030.png

 

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"

 

 

 

View solution in original post

1 REPLY 1
jsaunders_zero9
Responsive Resident
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

 

jsaunders_zero9_0-1672414516030.png

 

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"

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors