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,

 

[pjmorris]

Take your data into PowerQuery ('From Table' in the 'Get & Transform' tab of the Data ribbon.
Group the data by Name and Error and select Max [Time] as the aggregation.
Close and Load.

HTH

 

[sandy666]

edit: didn't refresh thread

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Name", type text}, {"Error", type text}}),
    Group = Table.Group(Type, {"Name", "Error"}, {{"Time", each List.Max([Time]), type time}}),
    Result = Table.ReorderColumns(Group,{"Time", "Name", "Error"})
in
    Result

 

[Nothnless]

Take your data into PowerQuery ('From Table' in the 'Get & Transform' tab of the Data ribbon.
Group the data by Name and Error and select Max [Time] as the aggregation.
Close and Load.

HTH

This is AWESOME and so easy, I didn't think about the aggregation. Thanks!

 

[Nothnless]

Take your data into PowerQuery ('From Table' in the 'Get & Transform' tab of the Data ribbon.
Group the data by Name and Error and select Max [Time] as the aggregation.
Close and Load.

HTH

One more thin though, this is just obviously sample data. I have other columns in my real data, how do I display those other columns? If I expand the All Columns option it unrolls the results.

 

[sandy666]

this is just obviously sample data.

so show representative example

 

[pjmorris]

At the group by stage add another aggregation column, called 'Other Rows' as shown here:

After clicking OK, you'll see another column with 'Table's in. When you expand those you'll have the choice of which columns you wish to see, plus you'll have the new column 'Max'.

Hopefully that will be sufficient.

 

[Nothnless]

so show representative example

Here you go:
As you can see the Error Code may be different but I still want to show Error Code 3333 John at 3:00. If this possible?

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

 

[pjmorris]

where's the error code come from? Previously you were talking about time, now its codes!

 

[Nothnless]

where's the error code come from? Previously you were talking about time, now its codes!

Because I only care about the latest time value as far as the grouping is concerned but I still need to show all the columns in the table. So yes getting the John and 3:00 is exactly the result I need but I would still need to see the last name column, the Error Code column and the 30 other columns next to those.