Power Query how to get listed rows from the table for the Min or Max Date?
CodePudding user response:
Are you trying to get the rows that are either the min or max date of the Grouped column?
#"Filtered Rows" = Table.SelectRows(#"YourPriorStepNameGoesHere", each ([Grouped] = List.Min(#"YourPriorStepNameGoesHere"[Grouped]) or [Grouped] =List.Max(#"YourPriorStepNameGoesHere"[Grouped])))
Edit per question:
to do this by Component, best way is to group on Component then expand. I am assuming one row per date, otherwise I'd have to do it another way
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Grouped", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Component"}, {{"data", each
Table.FirstN(Table.Sort(_,{{"Grouped", Order.Ascending}}),1) &
Table.LastN(Table.Sort(_,{{"Grouped", Order.Ascending}}),1)
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Grouped"}, {"Grouped"})
in #"Expanded data"