Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Statu...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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's avatar
MFelix
Super User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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