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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vnqt
Helper IV
Helper IV

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors