Equivalent of Select * from Table with Group by Max(Date). Thought it would be easy?

[dpl73]

Hi

I thought that this would be easy, but maybe I'm missing something.

I've got a simple table, and I want to return (transform, filter,..?) only the latest/last row for each customer with the results displayed in a filtered table (or new, if needs be)

From what I've found, I'm guessing that this might involve a few steps (e.g. x-join, etc..), but I wanted to check if there was an in-built method.

Thanks.

DATA:

TransID CustID Data1 Data2 Data3 Data4 Data5 Date
1 1 D3 B3 C3 T1 30/08/2016 09:13:30
2 1 B6 T1 A1 30/08/2016 10:33:40
3 1 D3 C5 T2 A1 01/09/2016 19:53:30
4 2 D1 30/08/2016 09:23:30
5 2 D4 B1 C2 T3 A3 08/09/2016 14:53:30
6 3 D5 A2 25/07/2016 10:15:10
7 4 B2 T2 02/09/2016 20:04:11
8 4 T7 02/09/2016 20:04:30

RESULTS:

TransID CustID Data1 Data2 Data3 Data4 Data5 Date
3 1 D3 C5 T2 A1 01/09/2016 19:53:30
5 2 D4 B1 C2 T3 A3 08/09/2016 14:53:30
6 3 D5 A2 25/07/2016 10:15:10
8 4 T7 02/09/2016 20:04:30

 

[Ken Puls]

Yeah, so this one needs a bit of a trick. Here's what you need to do:

• Bring your data into Power Query
• Sort the Date Column --> Descending
• On the left side of the formula bar (activate it from the View tab if you don't have it) is a little fx button. Click that
• Modify the formula so that it is surround in Table.Bufffer(). For me that looks like this, as the previous step was "Sorted Rows"
  • = Table.Buffer(#"Sorted Rows")
• Right click the Cust Id column and choose "Remove Duplicates'

The weird part here is that if you skip the buffering of the table (which essentially reads the data into memory and prevents recalculation by the M engine), it ignores the sort when you try to remove the duplicates. By buffering we lock the sort in place so it will work.

HTH,

 

[dpl73]

Hi Ken

Thanks for the reply.

Plugging this into my "actual" query as

= Table.Buffer(Table.Sort(#"Changed Type",{{"Date", Order.Descending}}))

appears to be yielding correct results ;-)

I did try a rather more complex Grouping followed by Expansion, etc.. that also seemed to yield correct results.

However, your solution was simpler and what I was after. Thanks again.