Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vnqt
Helper V
Helper V

Remove duplicate with condition

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) 

 

namereference
WASLR001null
WASLR001N/A-SQL-LR
SQLWI003null
SQLWI003N/A-SQL-WI
SQLWI002null
SQLWI002null
SQLWI001null
SQLWI001N/A-SQL-WI
ORAMU001N/A-ORA-MU
ORAMU001null
MYSLR001null
MYSLR001N/A-MYS-MU
MUTWI003null
MUTWI003N/A-MUT-WI
MUTWI002null
MUTWI002N/A-MUT-WI
MUTWI001null
MUTWI001N/A-MUT-WI
MUTMU999null
MUTMU999N/A-MUT-MU
MUTLR050N/A-MUT-MU
MUTLR050null
MUTLR002null
MUTLR002N/A-MUT-LR
MUTLR001null
MUTLR001N/A-MUT-LR
MQSMU001N/A-MQS-MU
MQSMU001null
JBOLR001null
JBOLR001null
DB2MU001null
DB2MU001N/A-DB2-MU
WASWI001null
WASWI001N/A-WAS-WI
WASLR001null
WASLR001N/A-WAS-LR
SQLWI001N/A-SQL-WI
SQLWI001null
SIDMU001null
SIDMU001SID-SQL-WI
MYSLR001null
MYSLR001N/A-MyS-LR
MUTWI002N/A-MUT-WI
MUTWI002null
MUTWI001null
MUTWI001N/A-MUT-WI
MUTWI001null
MUTLR050null
MUTLR050N/A-MUT-MU
MUTLR006N/A-MUT-LR
MUTLR006null
MUTLR005null
MUTLR005N/A-MUT-LR

 

Expecting result : 

namereference
WASLR001N/A-SQL-LR
WASLR001N/A-SQL-LR
SQLWI003N/A-SQL-WI
SQLWI003N/A-SQL-WI
SQLWI002null
SQLWI002null
SQLWI001N/A-SQL-WI
SQLWI001N/A-SQL-WI
ORAMU001N/A-ORA-MU
ORAMU001N/A-ORA-MU
MYSLR001N/A-MYS-MU
MYSLR001N/A-MYS-MU
MUTWI003N/A-MUT-WI
MUTWI003N/A-MUT-WI
MUTWI002N/A-MUT-WI
MUTWI002N/A-MUT-WI
MUTWI001N/A-MUT-WI
MUTWI001N/A-MUT-WI
MUTMU999N/A-MUT-MU
MUTMU999N/A-MUT-MU
MUTLR050N/A-MUT-MU
MUTLR050N/A-MUT-MU

 

Thank you in advance for your help 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

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?

 

lbendlin_0-1668871193191.png

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"

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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?

vjingzhang_0-1669017314701.png

Best Regards,
Community Support Team _ Jing

lbendlin
Super User
Super User

 

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?

 

lbendlin_0-1668871193191.png

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors