Removing earlier versions of a each TFS Work Item from the Power Query

[grell]

I am working with TFS warehouse data, and am a novice in T-SQL. I need to utilize Work Item data and have a power query mostly done, but don't know how to eliminate all the earlier versions of each entry, that are appearing in the query. Has anyone solved this problem on the Power BI side of the fence? If no, on the SQL side?

[mike_honey]

I would usually build another Query which identifies the rows you want to keep, e.g. use a Group By with Max of some date/time field (e.g. Last Modified Date - I'm not familiar with the TFS schema).  Then you can Merge your first Query with the Group By query, using Join Kind = Inner to filter the rows.

You can build the Queries using "Reference" to avoid duplicating code - typically for this scenario you start with a "Base" query, the a "Group By" query, started by "Reference" to the "Base" query. Finally you add an "Output" query, also started by "Reference" to the "Base" query, with the Merge step to the "Group By" query.  The first 2 queries would be hidden from users.