cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CARUSO93
Helper I
Helper I

Duplicates of 'Art nr' and....!

Hi everyone,

I need your expertise on this specific matter which I am not entirely sure myself how to solve right now. 
I have a column with 'Article Numbers' (we are talking thousands of them) then I have another column 'Warehouse' which states which warehouse the article is connected to. Worth noting is that we have 2 warehouses.

 

I first want to check for whether there are duplicates in 'Article nr' and then a formula (I guess?) that checks whether the duplicates considers which Warehouse it is connected to.
More specifically, because, if there is a duplicate for an article, one of the duplicates could be written on one of the Warehouse and the other on the other warehouse, if you see what I mean.
For ex;

 

Article nr: '123456' 
Warehouse: 'A', 'B'.


If there are two Art nr of '123456' existing, it could be that one of them is written on Warehouse A and the other one on Warehouse B. Whereareas I only want it to display '123456' for one of the warehouses.

My issue now then is how I go on solving this.
I was first thinking of removing duplicates in 'Power Query' but as the 'Art nr' could be written on either 'A' OR 'B', something tells me that, that's not how to solve it.

 

Hope you can help me in this.

Many thanks!!  

1 ACCEPTED SOLUTION
tamerj1
Community Champion
Community Champion

Hi @CARUSO93 
Please let me know if you still have any question

1.png2.png3.png

View solution in original post

9 REPLIES 9
CARUSO93
Helper I
Helper I

@tamerj1 

To display the duplicates and which warehouse they belong to.

tamerj1
Community Champion
Community Champion

Hi @CARUSO93 
Apologies for the late reply. I got destracted with some other business. If I correctly understand then this is a simple requirement. Just create a matrix visual with Art No on rows and Warehouse on columns and this measure on values 

 

Duplicate = 
SUMX ( 
    VALUES ( Warehouses[Art Nr] ),
    COUNTROWS ( VALUES ( Warehouses[Warehouse] ) )
)

 

From the filter pane do the following

1.png

2.png

@tamerj1 No worries. Thanks for your reply.

Do you add the measure to the matrix? And if so, is it possible to delete the duplicates? (as I just like we did know first only want see how many duplicates there are and then delete each duplicate for either Warehouse A or B as it's only necessary for the 'Art nr' to be written on of the Warehouses.

 

Many thanks again!

tamerj1
Community Champion
Community Champion

HI @CARUSO93 
You can just create a new table as follows
1.png

Warehouses No Duplicates = 
SUMMARIZE ( Warehouses, Warehouses[Art Nr], "Warehouse", MAX ( Warehouses[Warehouse] ) )

@tamerj1 
Do you drag the measure to the table or? And does the measure takes the duplicates into consideration. If not how can I delete those?

Many thanks again.

tamerj1
Community Champion
Community Champion

Hi @CARUSO93 
Please let me know if you still have any question

1.png2.png3.png

CARUSO93
Helper I
Helper I

Hi @tamerj1,

Absolutely, here comes. I have 'bolded' the two articles that are duplicated but has different Warehouses. That's the thing with my file, there are thousands of rows.
Therefore, I want in my Power BI, to find all the duplicates and see which/how many are written on Warehouse A and how many on B.

 

Art NrWarehouse

4768

A
4452A

6325

B

9562

A

8515

B
9845B
6650B
1165B
7662A
9563A
3465B
9531A

6156

B
7513A
7346B
9512A
2563A
4561B
6325A
1637B
4768B

 

Many thanks!!

 

tamerj1
Community Champion
Community Champion

@CARUSO93 

Thank you. What is the expected result based on same sample data?

tamerj1
Community Champion
Community Champion

Hi @CARUSO93 

I like this type of problems. Would you please create some dummy sample data and share it along with the expected results? Thank you

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors