how to remove records of previous version?

[SikY]

Hi everyone!

I have a Table, lets say Table1, there could be many versions of items with kinds of stages, e.g. Submit, Pending, Win.

Some projects may have multiple version with Win stage. Are there any methods to remove previous Win Stage record, only retain the latest version of Win stage and all other stages?

Table1:

project	version	status
proj 1	1	Submit
proj 1	2	Win
proj 1	3	Win
proj 2	1	Submit
proj 3	1	Submit
proj 3	2	Win
proj 3	3	Win

Desired Table:

project	version	status
proj 1	1	Submit
proj 1	3	Win
proj 2	1	Submit
proj 3	1	Submit
proj 3	3	Win

Any suggestion is appreciated!!

Nelson

[Anonymous]

Hi @SikY ,

You may firstly try @Vijay_A_Verma 's method,which is suitable for different scenarios.

Or if each project and each version just has a single Submit status, you could simply use:

Or use DAX to create a new table:

New Table = 
var _t1= FILTER('Data',[status]="Submit")
var _t2= SUMMARIZE(FILTER('Data',[status]="Win"),[project], "version",MAX('Data'[version]),"status",LASTNONBLANK('Table'[status],TRUE())) 
return UNION(_t1,_t2)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

For this scenario, it appears sufficient to group by project and status and take the max over version.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz1IwVNJRAuHg0qTczBKlWB0kcSMgDs/MQxU0Rhc0wmGCMR5xDJONsZkMEjCBCcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, version = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"version", Int64.Type}, {"status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"project", "status"}, {{"Version", each List.Max([version]), type nullable number}})
in
    #"Grouped Rows"

If you have additional columns you want to preserve, then you can inner join this group by step with the prior step.

Example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz1IwVNJRAuHg0qTczBIgI1EpVgdJzgiIwzPzgGQSqoQxXCIZIWGEbloKQs4YXS4VVQ5hUxqqBMKmdFQJE7hEhlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, version = _t, status = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"version", Int64.Type}, {"status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"project", "status"}, {{"version", each List.Max([version]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"project", "status", "version"}, #"Grouped Rows", {"project", "status", "version"}, "Grouped Rows", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Grouped Rows"})
in
    #"Removed Columns"

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz1IwVNJRAuHg0qTczBKlWB0kcSMgDs/MQxU0Rhc0wmGCMR5xDJONsZkMEjCBCcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, version = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"version", Int64.Type}, {"status", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([status] = "Win")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"project"}, {{"Max", each List.Max([version]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"project", "version"}, #"Grouped Rows", {"project", "Max"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Max"}, {"Max"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Grouped Rows", each ([Max] = null)),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type", {"project", "version"}, #"Filtered Rows1", {"project", "version"}, "Removed Columns", JoinKind.LeftAnti),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries1",{"Removed Columns"})
in
    #"Removed Columns1"