Forum Discussion
Deleting Multiple Rows for multiple conditions
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!
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.
3 Replies
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
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.
