Find repeating items in a column and compare with another column to exclude it

[BI_Analyticz]

Hi Friends,

I have a data like below. I want to exclude the rows if the 1st column has similar values but the 2nd column has blank.

Example: ABC is in 2 rows but one row has a blank value in the 2nd column. I want to exclude this from dataset. Is this is possible in Power BI?

Computer Name	Domain
ABC	NIS
ABC
BD11
DE11	MIS
CD11	MIS

[rogletree]

If you're just trying to get rid of the blank rows, then in power query the "Remove Rows" gives an option to remove blank rows.

Or do you have some situations where your Computer Name column will have a unique name that has a blank and you want to keep that?

[BI_Analyticz]

What you are saying is correct. if the computer name is unique but the domain column is blank then keep it. If the computer name is not unique then remove the particular row where the domain value is empty

[CNENFRNL]

Hi, @BI_Analyticz , what if some records read like this?

Computer Name	Domain
XYZ
XYZ

You will remove all of them or keep at least one record of its kind?

[BI_Analyticz]

I have to remove XYZ with Domain column as blank..

When the computer name appears more than one time in the 1st coumn but in the second column if the domain dns name is blank then I have to exclude it

[Anonymous]

Hi,

If you want to exclude it from your dataset you have to go in Power Query :

- Create a custom column1 to concatenante both columns

- Then create another custom column with condition : if Computer = custom column 1 then "to exclude"

- Then filter your dataset based on the last column created

This should work, hope it is clear enough,

[BI_Analyticz]

If you look at ABC the one of the row has Empty value and I wanna exclude it. But if you look at the next one its unique and domain is also empty. I want to keep that.. 

[v-kelly-msft]

Hello @BI_Analyticz ,

Create a measure as shown below:

_Domain = 
var _count=CALCULATE(COUNT('Table'[Computer Name]),FILTER(ALL('Table'),'Table'[Computer Name]=MAX('Table'[Computer Name])))
Return
IF(_count>1&&MAX('Table'[Domain])=BLANK(),BLANK(),MAX('Table'[Domain]))

And you'll see:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Have I answered your question? Mark my position as a solution!