Get the Most Recent Values from a table

[vsslasd1]

Hello: 

I have a Table defined as follows:
let
Source = PowerBIRESTAPI.Navigation(),
AppWorkspace = Source{[Key="AppWorkspace"]}[Data],
RefreshHistory = AppWorkspace{[Key="RefreshHistory"]}[Data],
#"Removed Columns" = Table.RemoveColumns(RefreshHistory,{"Workspace ID", "Request ID", "Refresh Type", "End Time", "Error Code", "Error Description", "Duration In Minutes"})
in
#"Removed Columns"

Which Returns the following:

I'd like the query to display only one DatasetId with the newest time stamp (the Max) , and that specific status that pertains to that newest time stamp. 
Not certain how to edit this syntax to do that. 

Thank you

[AlB · Accepted solution]

Hi @vsslasd1 

Assuming there's only one DatasetID in the table, you can use

Table.Max(#"Removed Columns", "Start Time")

If there are more, you can use a combination of Group By and Table.Max()

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-rzhou-msft · Accepted solution]

Hi @vsslasd1 

Please try my M Query in you Power Query Editor.

You can copy my M Query and paste it in your Advanced Editor.

My Sample:

My M Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDdasMwDAXgVwm+LkSSJUv2Xdau+4E9QehFx3JRaNlge3+WRNkw1JcyfJzjM45hCLtAgMo5m8b1IOyxt84KYMHcDW/z6/F8uU4f4bQbw4OLlDAmqoQuIhWgO7GfT8ygJKq2CegR+7wQLiBODpfv8/sfOjiSqJCyVWhtFv9z9p+3r+v0M63o0VFkyyJQIfUkig10dIRiBIYVSguiQtxATwuynFOKWet64ghaSc+OVJMm1grxtrY00IsjMZN5vwrFrR400KsjBgA2rhD5emQNNDgiEAWuEXa6/ulu8tMv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dataset ID" = _t, #"Rerfresh ID" = _t, #"Start Time" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dataset ID", type text}, {"Rerfresh ID", Int64.Type}, {"Start Time", type datetime}, {"Status", type text}}),
    #"Filter Row" =
    let
    MaxTime = Table.SelectRows(Source, each ([Start Time] = List.Max(Source[Start Time])))
    in
    Table.SelectRows(Source, each ([Dataset ID] = List.Max(MaxTime[Dataset ID])))
in
    #"Filter Row"

Result:

You may upgade your code as below:

let
Source = PowerBIRESTAPI.Navigation(),
AppWorkspace = Source{[Key="AppWorkspace"]}[Data],
RefreshHistory = AppWorkspace{[Key="RefreshHistory"]}[Data],
#"Removed Columns" = Table.RemoveColumns(RefreshHistory,{"Workspace ID", "Request ID", "Refresh Type", "End Time", "Error Code", "Error Description", "Duration In Minutes"}),
 #"Filter Row" =
    let
    MaxTime = Table.SelectRows(Source, each ([Start Time] = List.Max(Source[Start Time])))
    in
    Table.SelectRows(Source, each ([Dataset ID] = List.Max(MaxTime[Dataset ID])))
in
    #"Filter Row"

Best Regards,

Rico Zhou

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

[AlB · Accepted solution]

Hi @vsslasd1 

Assuming there's only one DatasetID in the table, you can use

Table.Max(#"Removed Columns", "Start Time")

If there are more, you can use a combination of Group By and Table.Max()

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers