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.
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!
You are welcome.