How do I filter for only the latest versions of a data line in Power Query?

user_14325383 0
Viewed 50

I have a dataset where new data is appended as a 'Change' (last column) and numbered, with the higher number being the latest outcome. I'm trying to correctly filter the latest rows based on the highest change number available and get a result as per the image below.

While I've managed to get the right rows with columns Contract, Period, Person Company & Person Name, I can't seem to get the rest of the data to appear. Can anyone suggest what I'm missing?

Original Dataset vs. Intended Result

EDIT - GOT SOMETHING WORKING I don't understand it yet, but the below code from another question finally worked. Thanks to Olly on Power Query - Group by MAX Column Value

let
    Partitions = Table.Group(Sheet1, {"Person Name"}, {{"Data", each Table.FirstN(Table.Sort(_,{{"Change", Order.Descending}}),1), type table}}),
    Combined = Table.Combine(Partitions[Data])
in
    Combined
powerquery
2 Answers

Here is another approach (assuming that your original dataset is formatted in a table called Table1.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract", Int64.Type}, {"Period", type datetime}, {"Person Company", type text}, {"Person Name", type text}, {"Person Role", type text}, {"Gender", type text}, {"Age", type text}, {"Employment Costs", Int64.Type}, {"Monthly Hours", Int64.Type}, {"Change", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person Name"}, {{"AllData", each _, type table [Contract=nullable number, Period=nullable datetime, Person Company=nullable text, Person Name=nullable text, Person Role=nullable text, Gender=nullable text, Age=nullable text, Employment Costs=nullable number, Monthly Hours=nullable number, Change=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max_Change", each List.Max([AllData][Change])),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Custom", "AllData", {"Contract", "Period", "Person Company", "Person Role", "Gender", "Age", "Employment Costs", "Monthly Hours", "Change"}, {"Contract", "Period", "Person Company", "Person Role", "Gender", "Age", "Employment Costs", "Monthly Hours", "Change"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AllData", "Filter", each if [Change] = [Max_Change] then "Yes" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Filter] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Filter", "Max_Change"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Contract", "Period", "Person Company", "Person Name", "Person Role", "Gender", "Age", "Employment Costs", "Monthly Hours", "Change"})
in
    #"Reordered Columns"

The idea is to create a column that carry the maximum change (by using List.Max()) of each agent and then filter by rows that fill the condition [Change] = [Max_Change]

user_196034190

you basically have the answer, but for instructions

click select all the columns you want to group on (contract, period, person company, person name), righ click group by use operation all rows and hit ok

go into home...advanced editor, and on the group line, replace

... each _, type table [...}})

with

.... each Table.FirstN(Table.Sort(_,{{"Change", Order.Descending}}),1), type table }})

hit done, then use arrows atop the new column to expand the extra columns

thats going to sort, in descending order by change #, then take the first row only

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract", Int64.Type}, {"Period", type date}, {"Person company", type text}, {"Person Name", type text}, {"Person Role", type text}, {"Gender", type text}, {"Age", Int64.Type}, {"Employment Costs", Int64.Type}, {"Monthly Hours", Int64.Type}, {"Change", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Contract", "Period", "Person company", "Person Name"}, {{"Count", each Table.FirstN(Table.Sort(_,{{"Change", Order.Descending}}),1), type table }}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Person Role", "Gender", "Age", "Employment Costs", "Monthly Hours", "Change"}, {"Person Role", "Gender", "Age", "Employment Costs", "Monthly Hours", "Change"})
in  #"Expanded Count"
user_92642300
Related