remove duplicates based on latest version using multiple selected columns

[RajuM]

Hi Team,

there are 4 columns username, inventorydevicename, applicationproduct,version

username, inventorydevicename, applicationproduct, 3 columns values has same values but VERSION is having different for each row.

While deleting the duplicate rows then I have to use 4 columns, delete olderversion and keep latest version

source table:

username, inventorydevicename, applicationproduct,version

Assigned user - User name	Inventory device - Inventory device name	Application - Product	Installation - Version
Abbott, Rohan	AUADL1LT911	12d Model	10
Abbott, Rohan	AUADL1LT911	12d Model	12
Abbott, Rohan	AUADL1LT911	12d Model	14
Abbott, Rohan	AUADL1LT911	12d Model	11

Expected output:

Abbott, Rohan

AUADL1LT911

12d Model

14

[RajuM · Accepted solution]

I'm keeping other columns apart from "Assigned user - User name", "Inventory device - Inventory device name", "Application - Product". but its not removing the duplicates in this case. could you pls suggest me

#"Grouped Rows" = Table.Group( #"Changed Type", {"Assigned user - User name", "Inventory device - Inventory device name", "Application - Product", "Installation - Publisher", "Installation - Application name", "Assigned user - Business Line","Inventory device - Location - last level","Assigned user - Corporate structure - 1st level","Assigned user - Corporate structure - 2nd level","Assigned user - Corporate structure - 3rd level","Assigned user - Corporate structure - last level","Inventory device - Cost center - 3rd level","Installation - Last used date","Calculated user - Email","Inventory device - Inventory date"},{{"Installation - Version",

each List.Max([#"Installation - Version"]), type nullable number}})
in
#"Grouped Rows"

[Vijay_A_Verma]

I gave you a new query in previous response. Does that work? If not, I will need sample output from your side.

[RajuM]

@Vijay_A_Verma 

#"Grouped Rows" = Table.Group(#"Changed Type", {"Assigned user - User name", "Inventory device - Inventory device name", "Application - Product"}, {{"Installation - Version", each List.Max([#"Installation - Version"]), type nullable number}})
in
#"Grouped Rows"

in this M query: how do i keep invendotrydevicename,applicationprudction for max

List.Max([#"Installation - Version"])

[Vijay_A_Verma]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKyi8p0VEIys9IzFPSUXIMdXTxMfQJsTQ0BPIMjVIUfPNTUnNAbAOlWB2SNBiRqsGEVA2GSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Assigned user - User name" = _t, #"Inventory device - Inventory device name" = _t, #"Application - Product" = _t, #"Installation - Version" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Assigned user - User name", type text}, {"Inventory device - Inventory device name", type text}, {"Application - Product", type text}, {"Installation - Version", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Assigned user - User name"}, {{"Inventory device - Inventory device name", each List.Max([#"Inventory device - Inventory device name"]), type nullable text}, {"Application - Product", each List.Max([#"Application - Product"]), type nullable text}, {"Installation - Version", each List.Max([#"Installation - Version"]), type nullable number}})
in
    #"Grouped Rows"

[RajuM]

hi @Vijay_A_Verma ,

when i use above code for groupby its working as expected...but its extracting 4 columns based on composite PK columns.... 

but sometimes inventorydevicename,application will have different product then we have to pick those column values.... 

I'm unable to attache the pbix file here..could u pls guide me on thisso that i would attach pbix file

[Vijay_A_Verma]

Easiest way is to group on first 3 columns and choose the max for 4th column

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("i45WckxKyi8p0VEIys9IzFPSUXIMdXTxMfQJsTQ0BPIMjVIUfPNTUnNAbAOlWB2SNBiRqsGEVA2GSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Assigned user - User name" = _t, #"Inventory device - Inventory device name" = _t, #"Application - Product" = _t, #"Installation - Version" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Assigned user - User name", type text}, {"Inventory device - Inventory device name", type text}, {"Application - Product", type text}, {"Installation - Version", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Assigned user - User name", "Inventory device - Inventory device name", "Application - Product"}, {{"Installation - Version", each List.Max([#"Installation - Version"]), type nullable number}})
in
    #"Grouped Rows"