Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |