Forum Discussion
Solved
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 sa...
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 , 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.