Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • az38's avatar
    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's avatar
    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's avatar
    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.

     

MartynRamsden's avatar
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:

  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's avatar
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's avatar
    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.