Finding Max Record

Hey AlanP514 ,

 

You can use the concept of gouping here in Power Query.

Go to Power Query--> Home --> Group by 

In the group by section, you can put all your columns you want to group by and get the max(date) out of there.

Then you can merge it with your original table to get the desired records.

Power Query code as below:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLzKksyUwuVtJRckksSQRSTkX5eVWpQIa5vpGhvpGBkZGCuZWhKVDAAIiNDHW9SnN0jYyUYnWI1W5oamVqRqz+4MycstQiFP2mVsYg7YYWZOo3MrIyNSTaAPf8nBQU7QYQ63VNyNJtaA6xHF17LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, #"Stream Group" = _t, Layer = _t, #"Load Date" = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Stream Group", type text}, {"Layer", type text}, {"Load Date", type datetime}, {"Value", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Layer"}, {{"Max LoadDate", each List.Max([Load Date]), type nullable datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Layer", "Max LoadDate"}, #"Changed Type", {"Layer", "Load Date"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Source", "Stream Group", "Value", "Date"}, {"Source", "Stream Group", "Value", "Date"})
in
    #"Expanded Grouped Rows"

 

Attaching a sample file for reference.

Output would be:

 

Hi AlanP514 ,

According to your description, here's my solution.

Create a measure:

Check =
IF (
    MAX ( 'Table'[Load Date] )
        = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Layer] = MAX ( 'Table'[Layer] ) ),
            'Table'[Load Date]
        ),
    1,
    0
)

Put the measure in the visual filter and select its value to1.

After apply filter, get the expected result.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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