cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jacobtsung
Frequent Visitor

Help Selecting Only One Quantity in Duplicate Lines of Data (for Matrix Table)

Hi everyone,

 

The community has been so helpful for me on Power BI journey, but I need some help once again. 

Could one of you Power BI wizards help mewith this? 

 

My SQL database has a tendency to duplicate purchase orders within the database even though there should be only 1 line of that PO. I need my tables in Power BI to only take one of these quantities rather than summing all of the quantities together. 

 

Thank you! 

 

EDIT: Sorry I must add - I would like to keep DIRECT QUERY MODE on rather than impotr tables (since remove duplicates requires changing to Import mode)

 

Example:

 

SQL Database in Power BI

 

2019-11-15_11-35-40.jpg

 

What my Matrix Table Shows on the Report Screen

 

2019-11-15_11-35-40.jpg

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User II
Super User II

@jacobtsung 

Can you change your SQL code to use

 

MAX ( qty_ordered )

GROUP BY po_no

 

obviously you would have to group by the other columns in the query as well but that should get rid of the duplicate problem.

View solution in original post

You would need a uniquie identifier on every row in order to force it to display in a visual.  If you had the following data and you pulled it into a table or matrix visual it will either group or sum the amount.

PO Amount
PO1 200
PO1 200
PO1 200
PO1 200
PO1 200

 

Grouped:

PO Amount
PO1 200

 

Summed

PO Amount
PO1 1000

 

You would need an ID column  that makes every row unique in order to force it to split the lines and you would need to pull the ID into the table or matrix display.

ID PO Amount
1 PO1 200
2 PO1 200
3 PO1 200
4 PO1 200
5 PO1 200

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User II
Super User II

@jacobtsung 

Can you change your SQL code to use

 

MAX ( qty_ordered )

GROUP BY po_no

 

obviously you would have to group by the other columns in the query as well but that should get rid of the duplicate problem.

View solution in original post

Thanks for the response! 

 

I'm pretty new to this all so could you help me out by explaining/writing out how to approach this?

I would appreciate it alot

 

Also would this affect the Direct Query thing? I would to keep it direct

Hello @jacobtsung 

I'm assuming that you have DBA's that maintain your SQL environment.  They would understand the strucure of the tables and be able to help you with building the query.  That being said, if you copy your SQL code here we can take a look at it.

Actually I figured it out. Your comment helped me alot. Thank you!

Also, what if I wanted the opposite to be true? If I wanted to list all duplicates?

Basically all columns would not be summed up, but individually shown as a seperate row in the matrix table. 

You would need a uniquie identifier on every row in order to force it to display in a visual.  If you had the following data and you pulled it into a table or matrix visual it will either group or sum the amount.

PO Amount
PO1 200
PO1 200
PO1 200
PO1 200
PO1 200

 

Grouped:

PO Amount
PO1 200

 

Summed

PO Amount
PO1 1000

 

You would need an ID column  that makes every row unique in order to force it to split the lines and you would need to pull the ID into the table or matrix display.

ID PO Amount
1 PO1 200
2 PO1 200
3 PO1 200
4 PO1 200
5 PO1 200

View solution in original post

Perfect thank you. Yes our system does provide a unqiue ID for each transaction so I can do this. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors