How do I get the latest record in the transform step

[EaglesTony]

Hi,

  I have the following table coming in

Created                             Key

8/29/2023 6:42:28 PM      A

7/26/2023 5:22:58 PM      A

7/12/2023 9:00:01 AM      A

6/27/2023 6:56:33 PM      A

10/21/2023 7:52:22 PM    B

What I need is the latest record only, so it would end up being

Created                             Key

8/29/2023 6:42:28 PM      A

10/21/2023 7:52:22 PM    B

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created", type datetime}, {"Key", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"Count", each Table.Max(_,"Created")}}),
    #"Expanded Count1" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Created"}, {"Created"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count1",{{"Key", type text}, {"Created", type datetime}})
in
    #"Changed Type1"

Hope this helps.

[v-yangliu-msft · Accepted solution]

Hi  @EaglesTony ,

You can also consider using Dax to solve this problem:

Create calculated table.

Table 2 =
FILTER(
    'Table',
    'Table'[Ceated]=
    MAXX(
        FILTER(ALL('Table'),'Table'[Key]=EARLIER('Table'[Key])),[Ceated]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EaglesTony]

I got it.

1) Sort the field Created Descending

2) Wrap it around Table.Buffer:

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