Return duplicate rows only on same dates and same quantity

[p1nkman]

Hi guys,

I am pretty much a beginner in PBI. I have a table where I have repeating ID's on the same date with sometimes the same quantity number and sometimes different. 

ID	Date	QTY
123	15/11/2022	2000
123	15/11/2022	2000
1358	16/11/2022	2400
1053	16/11/2022	1200
1457	20/11/2022	1500
1457	20/11/2022	1500
943	22/11/2022	200
843	22/11/2022	100
123	25/11/2022	210
1358	26/11/2022	420
5345	27/11/2022	1300
5345	27/11/2022	1210

I need to match the same ID on the same dates which has the same quantities. Final solution should look like this:

ID	Date	QTY
123	15/11/2022	2000
123	15/11/2022	2000
1457	20/11/2022	1500
1457	20/11/2022	1500

Thank you for your time!

[Anonymous · Accepted solution]

Hi @p1nkman ,

1.Filter out duplicate values back in the Power BI visual, so you need to create a column with different numeric values, first you can create an Index column in Power Query and then apply it to the report

^{2.Next, to create a measure in the report, enter the following code}

 Measure = var a = FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Table'[Date])&&'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[QTY]=SELECTEDVALUE('Table'[QTY])) return IF(COUNTAX(a,[ID])>1,1,0)

^{3.Apply this measure to view filtering}

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[p1nkman]

@Anonymous 

Hey never mind my last reply, I messed up the data types. It works! Thanks a lot !!

[p1nkman]

Hey @Anonymous , I didn't get the first step 😞 Could you please elaborate bit more? I am fairly new to Power BI. Thank you!

[Mikelytics]

HI @p1nkman ,

Sorry I did not read correctly. But here is the solution. 🙂

my samples starting point. As you can see there are duplicates.

In Power Query there is also a "Keep Duplicates" feature

After

you can also do that with more than 2 columns.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[p1nkman]

Thanks for the reply. It won't work because I need to return the same IDs on the same dates with same Qty. It can happen that one date has duplicates but the IDs are different on those dates. Hope I could explain it clearly

[Mikelytics]

Hi @p1nkman 

Did you try it? Because it should work and based on your sample data you will get the result you asked for

Can you please do the steps and show me where it does not work?

[Mikelytics]

Hi @p1nkman 

When you load the data into power query you can use the remove duplicate feature to get unique rows. Before you use it you have to reduce the columns on the granularity you need.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[p1nkman]

I'd like to keep the duplicates that are on the same date and same qty. How would removing duplicates help? Maybe I missed your point, sorry