Power Query Challenge - Find Latest Value and filter out the rest

[Nothnless]

Hi, I'm Using Get&Transform in Excel which is equivalent to Power Query in Power BI. It even uses the same M language.

Is there a way to use the query to get a result that displays the following?

If there are duplicate errors then keep the latest error of those duplicates and filter out the rest.

Original table:

Time	Name	Error
1:00	John	Y
2:00	John	Y
3:00	Smith	N
4:00	Dave	Y
3:00	John	Y

Result:

Time	Name	Error
3:00	Smith	N
4:00	Dave	Y
3:00	John	Y

In this case the duplicate John's with errors were removed except for the John at 3:00

Thank you,

 

[Nothnless]

I other words, I don't really care about the Error code column or the last name column but it should still be displayed, when grouping it hides these for some reason.

 

[pjmorris]

I think if you follow my post #7 you should achieve your aim.

 

[sandy666]

like this?

I think you should decide what you want to achieve

anyway you got a template so try yourself

 

[Nothnless]

like this?

View attachment 5288

I think you should decide what you want to achieve

anyway you got a template so try yourself

No I would be looking for this result:

Name	Error	Time	Last Name	Error Code
John	Y	3:00:00 AM​	Doe	3333​
Smith	N	3:00:00 AM​	Cane	3333​
Dave	Y	4:00:00 AM​	Coop	4444​

 

[Nothnless]

I think if you follow my post #7 you should achieve your aim.

No, unfortunately it ungroups it when you expand. Which is a shame because why wouldn't you want to group it and also see all the columns next to it?

 

[pjmorris]

Now I see what you actually want to achieve, its possible, but a bit more complicated!

After expanding your columns in the Powerquery editor, add a conditional column and compare the Max value with Time set the result to True when they are the same and False when not.
Filter the new column so only True is showing, delete the new column.

Close and Load as before.

These were the steps I used, obviously column names will be different:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type number}, {"Name", type text}, {"Error", type text}, {"Rubbish", type text}, {"Stuff", type text}, {"Nonsense", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Error"}, {{"Max", each List.Max([Time]), type number}, {"Other Rows", each _, type table [Time=number, Name=text, Error=text, Rubbish=text, Stuff=text, Nonsense=text]}}),
    #"Expanded Other Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Other Rows", {"Time", "Rubbish", "Stuff", "Nonsense"}, {"Time", "Rubbish", "Stuff", "Nonsense"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Other Rows", "Custom", each if [Time] = [Max] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

 

[Nothnless]

Now I see what you actually want to achieve, its possible, but a bit more complicated!

After expanding your columns in the Powerquery editor, add a conditional column and compare the Max value with Time set the result to True when they are the same and False when not.
Filter the new column so only True is showing, delete the new column.

Close and Load as before.

These were the steps I used, obviously column names will be different:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type number}, {"Name", type text}, {"Error", type text}, {"Rubbish", type text}, {"Stuff", type text}, {"Nonsense", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Error"}, {{"Max", each List.Max([Time]), type number}, {"Other Rows", each _, type table [Time=number, Name=text, Error=text, Rubbish=text, Stuff=text, Nonsense=text]}}),
    #"Expanded Other Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Other Rows", {"Time", "Rubbish", "Stuff", "Nonsense"}, {"Time", "Rubbish", "Stuff", "Nonsense"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Other Rows", "Custom", each if [Time] = [Max] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Thanks so much! Sorry I should have made it clear but didn't realize it would ungroup like that. I really appreciate the help, I was getting ready to duplicate the table and do a Left Outer join on the grouped table to see if that would work. Thanks again!

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    Group = Table.Group(Type, {"Name", "Error", "Last Name"}, {{"Time", each List.Max([Time]), type time}, {"Count", each _, type table}}),
    ErrCode = Table.AddColumn(Group, "Error Code", each List.Last(List.Distinct(Table.Column([Count],"Error Code"))))
in
    ErrCode

 

[pjmorris]

Thanks Nothnless, a pleasure to help and thanks for the feedback.

 

[Nothnless]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Name", type text}, {"Error", type text}, {"Last Name", type text}, {"Error Code", type text}}),
    Group = Table.Group(Type, {"Name", "Error", "Last Name"}, {{"Time", each List.Max([Time]), type time}, {"Count", each _, type table}}),
    ErrCode = Table.AddColumn(Group, "Error Code", each List.Last(List.Distinct(Table.Column([Count],"Error Code"))))
in
    ErrCode

View attachment 5297

Thanks Sandy, I'll take a look at this but if I had 30 more columns would you have to duplicate your code in ErrCode 30 times?