How to remove specific rows based on conditions?

[Daniel_Jesus]

Hey guys,

I have a data like in the example below, with thousands of rows following the same ideia. I'm trying to remove all rows that contains a company who only did purchase operation and not a sale, but I couldn't handle with it.

Data example:

COMPANY	OPERATION
Company A	Sale
Company A	Purchase
Company B	Purchase
Company C	Sale
Company D	Purchase
Company D	Sale

Reinforcing what I intend to do: company A did a sale and a purchase, it stays in my table; company C only did a sale and stays in my table, but company B only did a purchase, so I need to remove that from my data.

I'm intending to do something like:

COMPANY	OPERATION
Company A	Sale
Company A	Purchase
Company C	Sale
Company D	Purchase
Company D	Sale

Many thanks in advance 😃

[v-eqin-msft]

Hi @Daniel_Jesus ,

Did I answer your question? Please mark my reply as solution, thank you~

Best regards,

Eyelyn Qin

[v-eqin-msft]

Hi @Daniel_Jesus ,

According to my understanding, you want to remove the companies whose OPERATION only do Purchase , right?

For my test ,I created a "flag" for the two Operations, then sum it based on each company.

If the sum>0 , it means that the company has “Sale” operation.

So you could use the following formula:

flag =
IF (
    SELECTEDVALUE ( Purchase[OPERATION] ) = "Purchase",
    0,
    IF ( SELECTEDVALUE ( Purchase[OPERATION] ) = "Sale", 1 )
)

sumFlag =
IF (
    CALCULATE (
        SUMX ( 'Purchase', [flag] ),
        ALLEXCEPT ( 'Purchase', 'Purchase'[COMPANY] )
    ) > 0,
    1,
    0
)

After apply sumFlag to filter (set as “is 1”),My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"COMPANY"}, {{"All Operations", each Text.Combine(List.Distinct([OPERATION]), ", "), type text}}),
    Joined = Table.Join(Source, "COMPANY", #"Grouped Rows", "COMPANY"),
    #"Uppercased Text" = Table.TransformColumns(Joined,{{"All Operations", Text.Upper, type text}}),
    #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each if [All Operations]="PURCHASE" then "Ignore" else "Consider"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Consider")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"All Operations", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[Daniel_Jesus]

@Ashish_Mathur well done! You answered just what I needed! I dared to do some changes, but it worked as expected. Thanks a lot and have a nice day, you helped me a lot!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@Daniel_Jesus - Perhaps:

Table 4 = FILTER(ADDCOLUMNS(SUMMARIZE('Table (20)',[Company],[Operation]),"Include",COUNTROWS(FILTER('Table (20)',[Company]=EARLIER('Table (20)'[Company])&&[Operation]="Sale"))),[Include]>=1)

PBIX is attached below sig. Table (20) and Table 4

[Daniel_Jesus]

@Greg_Deckler In fact what you suggested worked fine, but I was concerned with the performance, once I have thousands of data and by duplicating my table I could cause some delays.  Thanks for supporting me.

[Greg_Deckler]

@Daniel_Jesus - I didn't realize you wanted a Power Query solution or I would have moved this thread to the Power Query forum.

[amitchandak]

@Daniel_Jesus , In edit query mode you can filter all others.

or

https://community.powerbi.com/t5/Desktop/M-Power-Query-Removing-entire-rows-based-on-condition/td-p/...

https://gcomsolutions.co.uk/blog/power-bi/removing-unnecessary-rows-in-power-query/

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a1cfd615-...

https://www.quora.com/In-Power-Query-can-I-remove-rows-based-on-a-particular-value-in-a-column

[Daniel_Jesus]

@amitchandak I read all material you suggested, but I couldn't find a solution or come up with any ideia to solve my problem, but I learned new things, thank you a lot.