Distanct values by priority and clients M

[Violet_a]

Hello everyone, I need help for resolving one task so here it is :

The columns we are tring to distanct are : clients and resultats . All of the clients can have few resultats back so we need to disting the right ones. in the column resultats there are 6 possible values as : res1, res2, res3, res4, res5, and res6. All clients that have resived res1 we want to check if the same ones have also a res2 so the outcome would be res2. if the clients with res4 has also res2 the outcome should be res2, if clients with the res5 has also res2 the outcome should be res2 If the clients has res1 and res4 the outcome should be res4, If clients has res1 and res5 the outcome would be res5, if not returne the actual resultats. Here is what I have tried but not yet working : 

let results = [clients][resultats], hasRes1 = List.Contains(results, "res1"), hasRes2 = List.Contains(results, "res2"), hasRes4 = List.Contains(results, "res4"), hasRes5 = List.Contains(results, "res5") in if hasRes1 and hasRes2 then "res2" else if hasRes4 and hasRes2 then "res2" else if hasRes5 and hasRes2 then "res2" else if hasRes1 and hasRes4 then "res4" else if hasRes1 and hasRes5 then "res5" else (results)

[ToddChitt]

And what are you trying to get TO? Not sure I understand the request.

[v-junyant-msft]

Hi @Violet_a ,

@ToddChitt Thanks for your concern about this case!

Here is my sample data:

I use this M function to build a CustomFunction:

CustomFunction = (results) => 
        let
            resList = Text.Split(results, ","), //Split the string by "," for a list of words, please modify it according to your own data
            has_res1 = List.Contains(resList, "res1"),
            has_res2 = List.Contains(resList, "res2"),
            has_res4 = List.Contains(resList, "res4"),
            has_res5 = List.Contains(resList, "res5"),
            result = if has_res1 and has_res2 then "res2"
                else if has_res2 and has_res4 then "res2"
                else if has_res2 and has_res5 then "res2"
                else if has_res1 and has_res4 then "res4"
                else if has_res1 and has_res5 then "res5"
                else results
        in
            result

Then add column and the final output is as below:

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpKLTbUARJGSrE60UpJEBEjkIgJWCQZScQULJKCpAuiJhVJBKImDUmXMVgkHSJijFCTgWw7QjgTWSvCjiwk1TBDYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [clients = _t, results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"clients", type text}, {"results", type text}}),
    CustomFunction = (results) => 
        let
            resList = Text.Split(results, ","), //Split the string by "," for a list of words, please modify it according to your own data
            has_res1 = List.Contains(resList, "res1"),
            has_res2 = List.Contains(resList, "res2"),
            has_res4 = List.Contains(resList, "res4"),
            has_res5 = List.Contains(resList, "res5"),
            result = if has_res1 and has_res2 then "res2"
                else if has_res2 and has_res4 then "res2"
                else if has_res2 and has_res5 then "res2"
                else if has_res1 and has_res4 then "res4"
                else if has_res1 and has_res5 then "res5"
                else results
        in
            result,
    AddedCustomColumn = Table.AddColumn(#"Changed Type", "CustomColumn", each CustomFunction([results]))
in
    AddedCustomColumn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ToddChitt]

Probably best to include some sameple incoming data, and a sample of what the result should be, even if that result is shown in Excel. Screenshots, please!