Remove Duplicate Values in Column

[gmasta1129]

Hello,

I am trying to create a table by Client ID using the data in the top table of the screenshot below.  

When summing the eligible amount by client id, I receive a value of $10,000,000 for client id #1 in my table.  This value should be $5,000,000 since the master port code is the same (1111).  Its the same portfolio. This value is being doubled when summing by client id.  

We have it broken down into two lines because the portfolio code is different and some of the other columns (not shown here) in the report do contain different numbers so i cannot remove the entire row as i need those values for other reports i am creating but the eligible amount will always be the same value as long as the master port code is the same #.   

How can i create this table so only 5,000,000 will pull in instead of 10,000,000? 

[gmasta1129]

I created a new table using the same exact csv file and then created a key as you mentioned above and it worked.  Thanks so much for all your help! 🙂

[TheoC]

Great to hear it worked @gmasta1129 ! Well done!

[TheoC]

Hi @gmasta1129 

Okay, so I've put the following Calculated Column together for you and provided you with the output and how it achieves what you are after:

Return Max Value = 

VAR _1 = CALCULATE ( COUNTROWS ('Table' ) , FILTER ('Table' , 'Table'[Client ID] = EARLIER ('Table'[Client ID] ) ) )

RETURN

IF ( _1 = 1 , 'Table'[Amount] , MAX ('Table'[Amount] ) )

The AMOUNT column is all amounts to each Client ID added.  I've purposely made Client ID "2" have two records of the same value (i.e. 32 and 32 which total equals 64, similar to your $5m and $5m).

All the best and I hope this achieves the solution you're after 🙂

Theo

[gmasta1129]

Hello,

@TheoC 

I am not able to remove duplicates because my data has other columns that are needed for tables I created.  

[TheoC]

Hi @gmasta1129 

Okay, no worries. The reason the $10,000,000 is showing is because you have two distinct Post Codes for Client 1.  Client 1 has Post Code 1111 and Post Code 1112 and each of these have $5,000,000 (therefore, $5m x 2 = $10m).   You can do either of the following:

1. Create a Key using calculated column ( Column = Table[Client] &" " & Table[Post Code] ) OR
2. You can remove the Post Code column from your table OR
3. Correct the Post Code on Client 1 so that Client 1 only has one Post Code.

Hope this helps 🙂

Theo

[gmasta1129]

@TheoC  I dont think those will work since i have other columns i am pulling into the table.  The example above was just a short version but i have about 10 more columns where the other columns need to pull in data for both 1111 and 1112. so for example, another column in the report is titled cash.  I need cash to pull in for client 1 from both portfolios (1111 + 1112) but then for eligible amount, i only need the value from portfolio code 1111.  

[TheoC · Accepted solution]

@gmasta1129, two ways to achieve what you're after is:

1. Create a unique key that merges Client ID and Post Code, OR

2. Use MAX to return the maximum amount for the respective Client ID.

Firstly, though, I recommend you attempt the earlier options I provided so that assumptions are tested for fact and proven not to work.

All the best with your challenge.

Theo 🙂

[TheoC]

Hi @gmasta1129 

Do the following:

1. Click on Transform Data in the Home tab of the Power BI ribbon.

2. Click on the column you want to remove duplicates.

3. Right click on the column title and click "Remove Duplicates"

Hope this helps 🙂