Forum Discussion
Solved
Filter ticket column based on last update
Hi All, I have a lot of double records in my dataset. Here's an example: I only want to have unique "Ticket ID" records based on "Last Updated Time" and (obv.) filter out the older ones. ...
I've added in the necessary steps to your query. I didn't change your Source location as I aded the files to my PC in the same path.
After the Grouped Rows step, I manually entered the Merge step. This merges the #"Grouped Rows" step with the table that results from the step immediately above it, #"Removed Columns".
The result is a table with just the Latest Updated rows.
Regards
Phil
PhilipTreacy
Super User
Super User
Download sample PBIX file with the following code
You can do this in Power Query by loading the source data, grouping by Ticket ID on the Latest Update, then merging the table that results from that step with the original source data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrEwUNJRCnK1UnBOLE5VMDQyNjEFCjjn5BenpgAZJvqGhvpGBkaGSrE6OFX7F6TmASkjNLWGyGojIqMQKo0Jq4S7wNAAoTgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, Subject = _t, Status = _t, #"Last Updated Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Subject", type text}, {"Status", type text}, {"Last Updated Time", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket ID"}, {{"Last Update", each List.Max([Last Updated Time]), type nullable date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Ticket ID", "Last Update"}, #"Changed Type", {"Ticket ID", "Last Updated Time"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Subject", "Status"}, {"Subject", "Status"})
in
#"Expanded Grouped Rows"
Regards
Phil