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
JonKho
Frequent Visitor

How to find duplicate values in one column while another column has different values?

Hi all

 

I need help to find the the rows in 'green' shown below.

Controller_Node_IdBarCodeMerged Column
1096310010963100
1096110010961100
1096320010963200
1096320010963200
1096130010961300
1096130010961300

I tried merged column but I realised my concept is flawed. Also, I can't seem to find similiar sitution in the community forums too.

 

Thank you for  reading this topic and I hope to get some insights.

2 ACCEPTED SOLUTIONS
v-cgao-msft
Community Support
Community Support

Hi @JonKho ,

Based on the information you have provided, it appears that you are trying to identify records that do not have duplicate values in the [merged columns]?
If yes, please refer to the m code below to group and count the table by [Merge Columns] and then filter the count to find the matching records.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwNDNW0gHSBmASyAMxY3UgUoYoUobIUiBdRghdRsRJgQw0RhhoTEAqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Controller_Node_Id = _t, BarCode = _t, #"Merged Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Controller_Node_Id", Int64.Type}, {"BarCode", Int64.Type}, {"Merged Column", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Merged Column"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Controller_Node_Id=nullable number, BarCode=nullable number, Merged Column=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Controller_Node_Id", "BarCode"}, {"Controller_Node_Id", "BarCode"})
in
    #"Expanded Data"

vcgaomsft_0-1715134668174.png

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula

Column = if(CALCULATE(DISTINCTCOUNT(Data[Controller_Node_Id]),FILTER(Data,Data[BarCode]=EARLIER(Data[BarCode])))>1,"Duplicate","Unique")

Hope this helps.

Ashish_Mathur_0-1715139457035.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula

Column = if(CALCULATE(DISTINCTCOUNT(Data[Controller_Node_Id]),FILTER(Data,Data[BarCode]=EARLIER(Data[BarCode])))>1,"Duplicate","Unique")

Hope this helps.

Ashish_Mathur_0-1715139457035.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

sergievs_0-1716202716231.png

 

 

Hi @Ashish_Mathur 

 

Thank you for the reply and it is something that I want. This is what I have done

\
Column = if(CALCULATE(DISTINCTCOUNT(Scanned_Item_Two_Controllers[Scan_Controller_Node_Id]),FILTER(Scanned_Item_Two_Controllers,Scanned_Item_Two_Controllers[Page_Barcode]=EARLIER(Scanned_Item_Two_Controllers[Page_Barcode])))>1,"Duplicate Found!","Unique")
 

However, I can't see the results that can be auto-refreshed. I think it is calculated coulmn allows only manual refresh only.

 

I have create another new measure that represent what I want to do where I can detect, 'Duplicate Found!'

 

Filter Value =
CALCULATE(
    COUNTA('Scanned_Item_Two_Controllers'[Column]),
    'Scanned_Item_Two_Controllers'[Column] IN { "Duplicate Found!" }
)
 
Thank you for the help on this.

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Fthrs_Analytics
Frequent Visitor

hey @JonKho is there a possible scenario where there is a valid one row per combination of controller nod and bar code or will all valid combination have matching duplicate values?

hi @Fthrs_Analytics 

 

There will be a chance where it will has additional duplicate rows like the one below shown below.

 

Controller_Node_IdBarCode
10963100
10961100
10963100
10961100
10963200
10963200
10961300
10961300

 

However, I  want to find a way to detect the two green rows that is shown below like I mentioned in my first post.

 

Controller_Node_IdBarCode
10963100
10961100
10963200
10963200
10961300
10961300

 

Thank you.

v-cgao-msft
Community Support
Community Support

Hi @JonKho ,

Based on the information you have provided, it appears that you are trying to identify records that do not have duplicate values in the [merged columns]?
If yes, please refer to the m code below to group and count the table by [Merge Columns] and then filter the count to find the matching records.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwNDNW0gHSBmASyAMxY3UgUoYoUobIUiBdRghdRsRJgQw0RhhoTEAqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Controller_Node_Id = _t, BarCode = _t, #"Merged Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Controller_Node_Id", Int64.Type}, {"BarCode", Int64.Type}, {"Merged Column", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Merged Column"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Controller_Node_Id=nullable number, BarCode=nullable number, Merged Column=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Controller_Node_Id", "BarCode"}, {"Controller_Node_Id", "BarCode"})
in
    #"Expanded Data"

vcgaomsft_0-1715134668174.png

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hi Gao

 

Thank you for the reply. After much thinking and testing of concept.

 

The conditions for detecting the rows that I want are:

 

1) Detecting the same bar code of '100' then

2) detecting the different values under column, 'Controller_Node_Id' 

 

Controller_Node_IdBarCode
10963100
10961100

 

Thank you for the help.

 

I will keep your solution in mind and I believe I can use it in the near future.

 

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