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
Or...
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"
jsaunders_zero9
Responsive Resident
Responsive ResidentHi 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"