Forum Discussion

Daniel_Jesus's avatar
Daniel_Jesus
Frequent Visitor
6 years ago
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...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
Daniel_Jesus
Frequent Visitor
5 years ago

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's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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