Filtering out old version numbers

[Anonymous]

Name	Publisher details	Publisher	version#
cmp1234	example 1 data	example 1	11.1.123
cmp1234	example 1 data	example 1	11.2.123
cmp1234	example 2 data	example 2	3.1.1.1
cmp1234	example 2 data	example 2	3.2.1.0

So I'm trying to filter out old version numbers from a data set similar to the one above. The problem is that for each name there are multiple publishers that I will have to do this for. So for each name I will have to return all the publishers but only show the rows that contain the latest versions for each publisher. Does anyone have an idea on how I could achieve this? I think I would have to remove the periods seperating the numbers so that I could search for the largest number, but I'm not sure that is the right way to go about it. If someone could point me in the right direction I would really appreciate it. 

[wdx223_Daniel · Accepted solution]

[HotChilli]

I've had a go at this.

First off, I don't know if this is a working solution so please investigate it and test with your own data.

It's a Power Query solution.  I'll include the code and a brief explanation of the algorithm.

Power Query can sort data on multiple columns (using Ctrl to select each column and choosing a sort from the column header dropdown), so if we sort the data on Name and Publisher Details (both ascending) and version # (descending), we'll get the start of a solution.  The solution depends on the sort of the version # doing what you want it to do (This will need testing).  The Name and publisher Details can be swapped out for other fields if required (I didn't know what makes each group of publisher/name).

After that we add an Index column (to maintain order of what we just sorted).

Then we do a 'group by' to get each group of publisher/name 'sets'.

Then we add an Index within each group (a neat technique which is a good one to learn).  This will assign 1 to the top item in each set.

When we expand the sets, we can then filter to keep only the rows that have a 1 (which should be the max version number in each set).

Here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs4tMDQyNlHSUUqtSMwtyElVMFRISSxJRBYAsg0N9YDQyFgpVocUPUZ49Bih6zECso31wBaRpgVoi56BUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Publisher details" = _t, Publisher = _t, #"version#" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Publisher details", type text}, {"Publisher", type text}, {"version#", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Publisher details", Order.Ascending}, {"version#", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Name", "Publisher details", "Publisher"}, {{"all", each _, type table [Name=nullable text, Publisher details=nullable text, Publisher=nullable text, #"version#"=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "Count",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"version#", "Count"}, {"Custom.version#", "Custom.Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Count] = 1))
in
    #"Filtered Rows"

A few advanced techniques there but mostly done with the interface.

Let me know how it goes.