Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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"
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
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"
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.