How to remove rows based on conditions

Hi All,

I have a data set like below. My requirement is to remove the duplicate entries based on other columns values.

Each strategy name is repeated multiple times with the same USD value. And for each row a status will be assigned like Approved or Not Approved. A strategy name can have both approved and not approved status or only approved /not approved status. Based on the status and strategy name I would like to keep only one row for each strategy. If it is approved and not apporved both then only one row with approved status. If it is not approved then only not approved. The highlighed rows are the ones I would need to keep. Please help.

Strategy name	Value	Approval Status
A_Test	100	Approved
A_Test	100	Approved
B_Test	200	Approved
B_Test	200	Approved
B_Test	200	Not Approved
C_Test	300	Approved
C_Test	300	Approved
C_Test	300	Not Approved
C_Test	300	Not Approved
D_Test	200	Not Approved
D_Test	200	Not Approved

Many Thanks

Regards

Ankhi

Need Help

az38
6 years ago
Hi Anonymous
as you have a requirement "If it is approved and not apporved both then only one row with approved status" try a calculated table
Table = SUMMARIZE('Table1'; Table1[Strategy name];Table1[Value];"Approval Status";MIN(Table1[Approval Status]))

do not hesitate to give a kudo to useful posts and mark solutions as solution
Anonymous
6 years ago
Sorry Anonymous

Some modification required in DAX.

Condition Check = (Test[Approval Status]="Not Approved" && NOT(ISEMPTY(FILTER(Test,Test[Strategy name]=EARLIER(Test[Strategy name]) && Test[Value]=EARLIER(Test[Value]) && Test[Approval Status]="Approved"))))

and filter it to FALSE only.
v-joesh-msft
6 years ago
Hi Anonymous ,
You can try the following methods:
1. As some previous responses, first, remove Duplicates in the Query Editor.

2. Then go back to the Data View and create the calculated column as follows:
Column = Test[Approval Status] = "Not Approved" && CALCULATE ( MIN ( Test[Approval Status] ), ALLEXCEPT ( Test, Test[Strategy name] ) ) = "Approved"
3. Filter values in the calculated column that are equal to False:
Here is a demo, please try it:
https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea9CbtwjxyNFp2tCMhjtBFEB0YTYjOYE4G-3sZhGv4bNmA?e=2PNDNT
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

16 Replies

az38
Community Champion
6 years ago
Hi Anonymous
as you have a requirement "If it is approved and not apporved both then only one row with approved status" try a calculated table
Table = SUMMARIZE('Table1'; Table1[Strategy name];Table1[Value];"Approval Status";MIN(Table1[Approval Status]))

do not hesitate to give a kudo to useful posts and mark solutions as solution
- Anonymous
  Not applicable
  6 years ago
  Try this one,
  
  1.First go to QUery editior
  2.Select these three columns only by pressing ctrl + Click on columns one by one(Strategy Name,Value,Approval status)
  3.Remove deplicate rows.
  4.Close & save.
  
  Now create one calculate column with below dax.
  
  Condition Check = Test[Approval Status]="Approved" || (Test[Approval Status]="Approved" && NOT(ISEMPTY(FILTER(Test,Test[Strategy name]=EARLIER(Test[Strategy name]) && Test[Value]=EARLIER(Test[Value]) && Test[Approval Status]<>"Approved"))))
  
  and filter it to TRUE only.
  - Anonymous
    Not applicable
    6 years ago
    Sorry Anonymous
    
    Some modification required in DAX.
    
    Condition Check = (Test[Approval Status]="Not Approved" && NOT(ISEMPTY(FILTER(Test,Test[Strategy name]=EARLIER(Test[Strategy name]) && Test[Value]=EARLIER(Test[Value]) && Test[Approval Status]="Approved"))))
    
    and filter it to FALSE only.
- Anonymous
  Not applicable
  6 years ago
  Hi az38,
  
  Thanks for the help. I have created calculated tables using your solution and using max(date_created) as using only approval_status was still giving me duplicates and it seems to work now. I am a bit new in Dax so could you please expalin to me how MIN(Approval_status) will pick up 'Approved' whenever it gets 'Approved & Not Approved for the same strategy ?
  
  Though it is working fine I need to apply the same for other datasets also so just wanted to be a bit more clear on this.
  
  Once again many thanks for your help.
  
  Regards
  Ankhi
  - az38
    Community Champion
    6 years ago
    Hi Anonymous
    Approval = MIN because it starts from "A" 🙂
FrankAT
Community Champion
6 years ago
Hi,
Open Power Query Editor.
Select all columns from your tabel.
On Home tabe >> Remove Rows >> Remove Duplicates.
Regards FrankAT
- Anonymous
  Not applicable
  6 years ago
  Hi Frank,
  
  Thanks for the reply. But my table has other columns which actually make these rows unique like created_date which is different for each row with same project name. So it will not be deleted. :-(.
  
  Any other solution that can be used?
  
  Many Thanks
  Regards
  Ankhi
  - v-joesh-msft
    Solution Sage
    6 years ago
    Hi Anonymous ,
    You can try the following methods:
    1. As some previous responses, first, remove Duplicates in the Query Editor.
    
    2. Then go back to the Data View and create the calculated column as follows:
    Column = Test[Approval Status] = "Not Approved" && CALCULATE ( MIN ( Test[Approval Status] ), ALLEXCEPT ( Test, Test[Strategy name] ) ) = "Approved"
    3. Filter values in the calculated column that are equal to False:
    Here is a demo, please try it:
    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea9CbtwjxyNFp2tCMhjtBFEB0YTYjOYE4G-3sZhGv4bNmA?e=2PNDNT
    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MartynRamsden
Solution Sage
6 years ago
Hi Anonymous

FrankAT 's solution will get you most of the way there but you'll still have multiple entries for some Strategy Names (one record for Approved status and another for Not Approved status).

To get your required result, you need to:
Open Power Query Editor.
Select all columns from your table.
On Home tab >> Remove Rows >> Remove Duplicates.
Select only the 'Strategy name' column
On Home tab >> Remove Rows >> Remove Duplicates.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.
- Anonymous
  Not applicable
  6 years ago
  Hi Martyn,
  
  Thanks for your reply. If I remove duplicates based on only Strategy name then the Approval_status which for each startegy can be 'Approved/Not Approved' might have wrong values. As I believe POWER BI will keep the 1st entry and remove all the duplicate entries there after. Please correct me if I am wrong.
  
  Many Thanks
  Regards
  Ankhi
  - MartynRamsden
    Solution Sage
    6 years ago
    Hi Anonymous
    
    Yes, Power Query will only keep the first matching record in the result of duplicate entries.
    The only way around this is to order your data before it's loaded into Power BI (e.g. in your SQL view).
    
    Best regards,
    Martyn
    
    If I answered your question, please help others by accepting it as a solution.

Forum Discussion

How to remove rows based on conditions

16 Replies

Related content

Remove Records based on Date Condition

Remove column based on condition

How to hide/remove duplicates based on condition

Remove Duplicates based on Condition

Remove rows in Power Query based on multiple Condition