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
Harshch
New Member

How to replace second and more instances of duplicate values with other value

Hello community,

 

I am having data as multiple duplicate records as mentioned in A column and want the output as 1st instance as same and other instances as _duplicate as mentioned in column B

Please provide a solution for the same

Harshch_0-1720452877263.png

 

 

Thanks In Advance

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Harshch Try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ1QyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"Min", each List.Min([Index]), type number}, {"Rows", each _, type table [Column1=nullable text, Index=number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Column2", each if [Min] = [Index] then [Column1] else [Column1] & "_DUPLICATE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min", "Index"})
in
    #"Removed Columns"

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Harshch
New Member

@Greg_Deckler Thanks for this solution, it worked
Would be really helpful if you can post logical understanding of this code

Thanks alot

@Harshch Sure, so the fist thing is to add an index column so that we can figure out "first" from "not first". We then group by the value column (A) and as aggregates we have 2 columns. One is the Min of the Index column, the second is all rows. We can then expand the all rows column retrieving just the Index value for the rows. Now, we can add a column that essentially just compares the Index column to our aggregated Min column. If the two are equal, then that is the first row. If they are not equal, then it is not the first row. We then just clean up our temporary Min and expanded Index columns.

 

Let me know if that is a sufficient explanation.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@Harshch Try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ1QyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"Min", each List.Min([Index]), type number}, {"Rows", each _, type table [Column1=nullable text, Index=number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Column2", each if [Min] = [Index] then [Column1] else [Column1] & "_DUPLICATE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min", "Index"})
in
    #"Removed Columns"

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors