Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vsslasd1
Helper III
Helper III

Get the Most Recent Values from a table

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:

Rev.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

View solution in original post

v-rzhou-msft
Community Support
Community Support

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:

1.png

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:

2.png

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. 

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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:

1.png

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:

2.png

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
Super User
Super User

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 

 

SU18_powerbi_badge

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.