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

[JonKho]

Hi all

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

Controller_Node_Id	BarCode	Merged Column
10963	100	10963100
10961	100	10961100
10963	200	10963200
10963	200	10963200
10961	300	10961300
10961	300	10961300

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.

[v-cgao-msft · Accepted solution]

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"

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

[Ashish_Mathur · Accepted solution]

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.

[sergievs]

[JonKho]

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.

[Ashish_Mathur]

You are welcome.

[Fthrs_Analytics]

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?

[JonKho]

hi @Fthrs_Analytics 

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

Controller_Node_Id	BarCode
10963	100
10961	100
10963	100
10961	100
10963	200
10963	200
10961	300
10961	300

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_Id	BarCode
10963	100
10961	100
10963	200
10963	200
10961	300
10961	300

Thank you.

[v-cgao-msft · Accepted solution]

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"

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

[JonKho]

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_Id	BarCode
10963	100
10961	100

Thank you for the help.

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