Search code examples
datemaxpowerquerymin

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?enter image description here

Here is the code that is currently not working

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Activity ID", type text}, {"Activity Name", type text}, {"System Locator-g", type text}, {"Milestone-g", type text}, {"Original Duration", Int64.Type}, {"Test Work Type-g", type text}, {"MXO_WONUM", type text}, {"Start", type any}, {"Finish", type any}, {"Activity Status", type text}, {"MXO_EQUIPTAGS", type any}, {"Discipline-g", type any}, {"Restraints", type any}, {"Empty row", type any}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"MXO_WONUM"}, #"Append1 Loc Max Dump & Loc Max Dump", {"wonum"}, "Append1 Loc Max Dump & Loc Max Dump", JoinKind.LeftOuter),

#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Milestone-g"}, #"Milestone Sequence", {"Milestone-g"}, "Milestone Sequence", JoinKind.LeftOuter),
#"Expanded Milestone Sequence" = Table.ExpandTableColumn(#"Merged Queries1", "Milestone Sequence", {"Milestone Sequence"}, {"Milestone Sequence.Milestone Sequence"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Milestone Sequence",{{"Milestone Sequence.Milestone Sequence", "Milestone Sequence"}}),

#"Merged Queries2" = Table.NestedJoin(#"Changed Type3", {"Component"}, #"CTM P6 Update Tab (2) - unpivoted", {"Component"}, "CTM P6 Update Tab (2) - unpivoted", JoinKind.LeftOuter),
#"Expanded CTM P6 Update Tab (2) - unpivoted" = Table.ExpandTableColumn(#"Merged Queries2", "CTM P6 Update Tab (2) - unpivoted", {"Test Status "}, {"CTM P6 Update Tab (2) - unpivoted.Test Status "}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded CTM P6 Update Tab (2) - unpivoted",{{"CTM P6 Update Tab (2) - unpivoted.Test Status ", "Test Status"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns2", {"Component", "Milestone-g", "Milestone Sequence", "Activity Status", "Activity ID", "Activity Name"}, {{"Grouped", each Table.FirstN(Table.Sort(#"Renamed Columns2",{"Grouped",Order.Ascending}),1), type table}})
in  #"Grouped Rows"
Solution

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