Delete rows from dataset with rule if has more than one row

[Anonymous]

Hello everyone!

I'm new to PowerQuery/PowerBI, I've got a table that I'd like to remove some rows if a rule applies.

What I need:

If  column "VALID" has "YES" in the data, delete rows that contains "NO" (if they are the same information only).

I filtered the data to show the example below, in this case, I'd like to delete the first row that contains "NO" and keep only the row with "YES", because all the rest are the same.

If rows contains only the value "NO", so do nothing.

Example:ExampleThanks!

[v-frfei-msft]

Hi @Anonymous ,

We can use calculated table to work around instead of M code in power query.

CleanTable =
VAR T =
    ALL ( 'table' )
VAR Y =
    FILTER ( T, [VALID] = "YES" )
VAR YN =
    SELECTCOLUMNS (
        Y,
        "COD_EMPRS", [COD_EMPRS],
        "NUM_RGTRO_EMPRG", [NUM_RGTRO_EMPRG],
        "NUM_IDNTF_RPTANT", [NUM_IDNTF_RPTANT],
        "VALID", "NO",
        "EMP/MAT/REP", [EMP/MAT/REP]
    )
RETURN
    EXCEPT ( T, YN )

[Anonymous]

I couldn't open your file, it said my version was newer than yours.

But I'm going to merge this table with another table, so I need only one result.

Rule:

If has "YES" and "NO", keep only "YES".

Otherwise, bring wherever has in the column.

[Stachu]

if you have to do it in M then the Group By + List.Max I posted earlier will do the job

[Stachu]

I'd suggest using GroupBy with Max of VALID, like this

List.Max can be used with strings following alphabetic order, so Yes will always come ahead of No

[Anonymous]

I didn't try this solution yet, but I think it wont wok. Because I'm going to merge this table with another table, so it'll bring both results.