Deleting Multiple Rows for multiple conditions

[Anonymous]

Hello, I am 

Probelm:  As you can see, a Account Number can have multiple Status. I am trying to write a query where the query looks for the first account number and checks if it has a "SEE Status" of "Applicable" if it has then I want it to delete the row for the account number with Not Applicable "SEE Status". Now I want it to move to the next Account Number and Look for the same, if it does not have a "Applicable" Status then I want it to retain just one row from the "Not Applicable"  "SEE Status" and delete the other rows and so on.

Problem:

Account Number            SEE Status

10                                    Not Applicable

10                                    Applicale

11                                    Not Applicable    

Desired Out put:

10                                    Applicale

11                                    Not Applicable    

Please Help!

[Ashish_Mathur · Accepted solution]

Hi,

Based on your limited information, this DAX formula works

if(HASONEVALUE(Data[Account Number]),if(DISTINCTCOUNT(Data[SEE Status])>1,"Applicable",VALUES(Data[SEE Status])),BLANK())

Hope this helps.

[MFelix]

Hi @Anonymous ,

I'm assuming based on your model that you only have two SSE status (Applicable / Not Applicable), if that is true on the query editor do a group by with the SSE Status Minimum value, if you have more than one column on your table add them on your group by option.

Check M Code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lHyyy9RcCwoyMlMTkzKSVWK1YGKo4sZ4lBrhEccXcwYTSwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, #"SEE Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", Int64.Type}, {"SEE Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"SEE Status", each List.Min([SEE Status]), type text}})
in
    #"Grouped Rows"

On my example I have added a couple more accounts in order to test values that only have applicable.

Regards,

MFelix

[Anonymous]

CAN YPU PLEASE SHARE YOUR EXCEL SHEET WHICH HAS POWER QUERY IN IT?