Filter ticket column based on last update

Hi Looking4Answers 

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

Hi Looking4Answers 

Not really sure what you are hoping for.  Sounds like you want to modify your source data, rather than do any filtering within PBI?

Not sure how I could modify your source data from outside PBI?  What do you mean by modify my query to change my dataset automatically 

What query are you referring to?

If you bring this dataset into PBI you can create a table and then show the MAX date for each Ticket ID.

Regards

Phil

Hi Looking4Answers 

Then load the CSV files using the from folder connector and the subsequent query stesp will still be the same.

Regards

Phil

Hi Looking4Answers 

Download example PBIX file.

This file loads 2 CSV files from a folder on my PC.  I'm using that as the example here.  You will need to create your own query to load your CSV files but you can then add on my query steps to yours, starting from 1. below.

To illustrate loading from CSVs in folder,

In the next step choose Combine and Transform Data

You end up with this table in Power Query and a load of Helper queries created by PQ to load and transform the CVS files.  Now you can start doing your own transformations

Then what I've done is :

1. Delete the Source.Name column

2. Grouped Rows 

3. Merged tables.  This step is done manually.  What is happening is that the query is merging the table created by the #"Grouped Rows" step, and the table created by the #"Changed Type" step. 

Open the Advanced Editor and enter the Merge step as shown below.  Just copy/paste my code.  Change the in section to return Merge

4. Expand Grouped Rows.  You only need Subject and Status.

5. Reorder columns

Regards

Phil

Here's my report with some random data (and the right column names).

Hopefully this will make it a bit more clear how I created it. 🤓

Thanks!

https://1drv.ms/u/s!At_sUiFlMcIFg45nbcHGJD_M6evLHw?e=EhOhqm