Finding Max Record

[AlanP514]

Hi all, I need your help to solve this challenge 
Scenario is 

I have one table containing data like this, IT team triggers data many times a day, so I want to find the max date  values from each layer, Else I want to load only max date values to power bi 
Expected output is  

please help me to achieve this logic

[PC2790 · Accepted solution]

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:

[v-yanjiang-msft · Accepted solution]

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.

[PC2790 · Accepted solution]

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: