How to remove rows based on conditions

[Anonymous]

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

[az38 · Accepted solution]

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 · Accepted solution]

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.

[MartynRamsden]

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:

1. Open Power Query Editor.
2. Select all columns from your table.
3. On Home tab >> Remove Rows >> Remove Duplicates.
4. Select only the 'Strategy name' column
5. 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]

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]

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.

[az38 · Accepted solution]

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]

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]

Hi @Anonymous 

Approval = MIN because it starts from "A" 🙂

[Anonymous]

oh okk 🙂

Thank you 

[Anonymous]

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 · Accepted solution]

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]

Hi PR,

Thanks a lot for the solution. I have tried only the below one as of now and it works fine. Only one question , Can i create this in the query editior as e new column and filter it as false so my dataset has only these rows ? Or else I think for evry visual that I create I have to use this as filter and set it to false. Please let me know.

Many Thanks

Regards

Ankhi

[Anonymous]

If it resolves your problem mark it as solution and give kudos.

Thanks & regards,

Pravin Wattamwar

www.linkedin.com/in/pravin-p-wattamwar

[Anonymous]

One more way is just simple create index column in your table.

add below two columns.

Duplicate_Check = VAr new=LOOKUPVALUE(Test[Strategy name],Test[Index],Test[Index]-1,Test[Approval Status],Test[Approval Status],Test[Value],Test[Value])

return IF(Test[Strategy name]=new,-1,1)

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  Duplicate_Check to 1 and Condition Check=Flase at visual level filter.

If it resolve your problem mark it as a solution and give kudos.

Thanks & regards,

Pravin Wattamwar

https://www.linkedin.com/in/pravin-p-wattamwar/

[FrankAT]

Hi,

1. Open Power Query Editor.
2. Select all columns from your tabel.
3. On Home tabe >> Remove Rows >> Remove Duplicates.

Regards FrankAT

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

Hi Joesh,

Thanks for the solution. It works . 🙂

Regards

Ankhi