Deleting multiple conditions ion ***POWER QUERY***

[Anonymous]

Hello, I am trying to delete Rows 

Problem:

Account_number   |      SEE_Number   |            SIO_Status

GA12234                |  SEE-234-567       |          NUll                ----------> This row should be deleted without the other

GA12234                |  SEE-234-567       |          Cancelled

GA 55555                |  SEE-001-009       |          NUll

The first account number is duplicate. So I want my query to retain the account_number and its "SIO_Status" if it has any and duplicate the other record BUT at the same time retain the null  values of the other Account_number.

Desired Solution: 

Account_number   |      SEE_Number   |            SIO_Status

GA12234                |  SEE-234-567       |          Cancelled

GA 55555                |  SEE-001-009       |          NUll

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnc0NDIyNlHSUQp2ddUFsnRNzcyBPL9QHx+lWB3cCpwT85JTc3JSU6CqTEEAqsrAwBCILeHGxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, SEE_Number = _t, SIO_Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"SEE_Number", type text}, {"SIO_Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Length([SIO_Status])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"SEE_Number", "Account"}, {{"Count", each List.Max([Custom]), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each if[Count] = 9 then "Cancelled" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"})
in
    #"Removed Columns"  

 

[Anonymous]

Thank you!!! That really helped.