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

[Anonymous]

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

What my Matrix Table Shows on the Report Screen

[jdbuchanan71 · Accepted solution]

@Anonymous 

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.

[jdbuchanan71 · Accepted solution]

@Anonymous 

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.

[Anonymous]

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

[jdbuchanan71]

Hello @Anonymous 

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.

[Anonymous]

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. 

[jdbuchanan71 · Accepted solution]

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

[Anonymous]

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