Remove duplicate with condition

[vnqt]

Hi, 

I would like to remove the duplicated names in the table below but before that I would like to copy the Reference value if they have the same name. 

I do this because after deleting the duplicates, the References are deleted too.

OR how could I delete the duplicate with the condition that IF [name] = [name] AND [reference] = null THEN copy  [reference] (of course in the same name) 

name	reference
WASLR001	null
WASLR001	N/A-SQL-LR
SQLWI003	null
SQLWI003	N/A-SQL-WI
SQLWI002	null
SQLWI002	null
SQLWI001	null
SQLWI001	N/A-SQL-WI
ORAMU001	N/A-ORA-MU
ORAMU001	null
MYSLR001	null
MYSLR001	N/A-MYS-MU
MUTWI003	null
MUTWI003	N/A-MUT-WI
MUTWI002	null
MUTWI002	N/A-MUT-WI
MUTWI001	null
MUTWI001	N/A-MUT-WI
MUTMU999	null
MUTMU999	N/A-MUT-MU
MUTLR050	N/A-MUT-MU
MUTLR050	null
MUTLR002	null
MUTLR002	N/A-MUT-LR
MUTLR001	null
MUTLR001	N/A-MUT-LR
MQSMU001	N/A-MQS-MU
MQSMU001	null
JBOLR001	null
JBOLR001	null
DB2MU001	null
DB2MU001	N/A-DB2-MU
WASWI001	null
WASWI001	N/A-WAS-WI
WASLR001	null
WASLR001	N/A-WAS-LR
SQLWI001	N/A-SQL-WI
SQLWI001	null
SIDMU001	null
SIDMU001	SID-SQL-WI
MYSLR001	null
MYSLR001	N/A-MyS-LR
MUTWI002	N/A-MUT-WI
MUTWI002	null
MUTWI001	null
MUTWI001	N/A-MUT-WI
MUTWI001	null
MUTLR050	null
MUTLR050	N/A-MUT-MU
MUTLR006	N/A-MUT-LR
MUTLR006	null
MUTLR005	null
MUTLR005	N/A-MUT-LR

Expecting result : 

name	reference
WASLR001	N/A-SQL-LR
WASLR001	N/A-SQL-LR
SQLWI003	N/A-SQL-WI
SQLWI003	N/A-SQL-WI
SQLWI002	null
SQLWI002	null
SQLWI001	N/A-SQL-WI
SQLWI001	N/A-SQL-WI
ORAMU001	N/A-ORA-MU
ORAMU001	N/A-ORA-MU
MYSLR001	N/A-MYS-MU
MYSLR001	N/A-MYS-MU
MUTWI003	N/A-MUT-WI
MUTWI003	N/A-MUT-WI
MUTWI002	N/A-MUT-WI
MUTWI002	N/A-MUT-WI
MUTWI001	N/A-MUT-WI
MUTWI001	N/A-MUT-WI
MUTMU999	N/A-MUT-MU
MUTMU999	N/A-MUT-MU
MUTLR050	N/A-MUT-MU
MUTLR050	N/A-MUT-MU

Thank you in advance for your help 

[v-jingzhang]

Hi @vnqt 

I see there are many duplicated rows in the expecing result, does this mean the result before moving duplicates? Let's take WASLR001 for example, why the first reference is kept and the second reference is removed? Does it mean that you only need to keep the first non-null reference for every name?

Best Regards,
Community Support Team _ Jing

[lbendlin · Accepted solution]

I would like to copy the Reference value if they have the same name. 

I don't understand that part.

What should happen in this scenario?

Do you expect both references to be listed?

Or more like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNBCoMwEPyLZ6XRouBR8WIxFY1BiviD0lsP/X034uqabMRbdmYy2U0m0xSMhWp6IeIgDD7f9zuYwwP2vBWR6pqo6RcGlmMtxJ2qCYbqsaZMwqg5LPZglmvbF1LvDJSR1Daz+ciXOx/BjAOU6CD14MxHsEWtB+xkZRJGnXjVMaOOWbXUeZ5basRQvfcNE6XilKE+Zn67b8TQYX3zlbH7PtwgUXeKvg6UWyc7s/k8ytbx5rCqTJy9BDMnQYknQX6dmyaYUUOJN33hBxj18QcwuWRTXFdO3wSDJXW4ktWfIu9ynjQul9fTx6k9SfLnTmTePGVMnlIPRh3mPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, reference = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"reference"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"name"}, {{"Rows", each _, type table [name=nullable text, reference=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Reference", each List.First(List.RemoveNulls([Rows][reference]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"name", "Reference"})
in
    #"Removed Other Columns"