Home > Software design >  Power Query how to get listed rows from the table for the Min or Max Date?
Power Query how to get listed rows from the table for the Min or Max Date?

Time:08-11

Power Query how to get listed rows from the table for the Min or Max Date?enter image description here

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"
  • Related