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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
133 | |
93 | |
90 |