cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vnqt
Helper III
Helper III

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors