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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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