Forum Discussion
How to remove specific rows based on conditions?
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 š
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.
10 Replies
- Ashish_Mathur
Super User
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.
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.
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
- amitchandak
Daniel_Jesus , In edit query mode you can filter all others.
or
https://gcomsolutions.co.uk/blog/power-bi/removing-unnecessary-rows-in-power-query/
https://www.quora.com/In-Power-Query-can-I-remove-rows-based-on-a-particular-value-in-a-column
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.
- Greg_Deckler
Community Champion
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
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.
Hi Daniel_Jesus ,
Did I answer your question? Please mark my reply as solution, thank you~
Best regards,
Eyelyn Qin
