search for value in multiple column based on filter with hierarchy

[citadel555]

Hello everyone, I am working on a project in power query where I need to search specific value in multiple column based on filter list with hiearchcal order 

example Senario:
[Column 1] [Column 2] [Column 3] 
       A                  B                B
       C                   B                C
        A                   B                C

Filter list
A
B
C

so what I need is power query to search column based of filter list so in this in first row if it find value A it will stop and return value A in a custom columun.

what result should look like

[Column 1] [Column 2] [Column 3] [Custom Column]
       A                  B                B                       A
       C                  B                C                       B
       A                  B                C                       C

Is this possible with power query? I have it that I can I search one column based on dynamic filter using List.contain function but situation above is bit more complex for me after searching on google, youtube and multiple forms I found nothing. Thank you for your help. 

[Vijay_A_Verma · Accepted solution]

Use the following formula in a custom column

= List.First(List.Intersect({{"A","B","C"},Record.ToList(_)}))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclbSUXICYmelWJ1oJUcUnjOYBeOBxCHyMDmICEzOGUOfE1wOqjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, B.1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each List.First(List.Intersect({{"A","B","C"},Record.ToList(_)})))
in
    #"Added Custom"

[Vijay_A_Verma · Accepted solution]

Use the following formula in a custom column

= List.First(List.Intersect({{"A","B","C"},Record.ToList(_)}))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclbSUXICYmelWJ1oJUcUnjOYBeOBxCHyMDmICEzOGUOfE1wOqjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, B.1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each List.First(List.Intersect({{"A","B","C"},Record.ToList(_)})))
in
    #"Added Custom"

[citadel555]

This excatly what I needed Thank you so much, 

= List.First(List.Intersect({{"A","B","C"},Record.ToList(_)}))

this worked perfectly and intergrating my dynamic custom filter list was easy. For future reference anyone reading this in the future I am referncing to this Link.  

= List.First(List.Intersect({Filter,Record.ToList(_)}))​

In this case "Filter" would be the name of your dynamic list.

I have another question related to this is there any where this command only effects selected columns instead of every column in query?  For sake of making data processing faster. For example look back at my example lets say their are four columns but I only want this code to effect three columns without having rearange the column in my query is there a way to do this?

[mussaenda]

Hi @citadel555 ,

Please state your logic behind your answer to help you better.

Thanks

[citadel555]

First of all I made a typo in the last row for custom column should've have been A.

Any way so the code will search through the columns row at a time, so incase of my answer the code will look at first row A B B and in the custom columun it will return A because on the condition A>B>C .
In second row C B C it will return B because B>C. and etc hopefully this clearup the confusion. 

[mussaenda · Accepted solution]

Hi @citadel555 ,

As per your clarification, please check if this is the solution you need.

Hope this helps.

[citadel555]

Hey thank you so much for your reply, this excatly how I was thinking of appoarching my problem but the only downside with this method is that I have more than three condition, and having too many if statements might slow down my queries so I wanted to see if there was an alternative. List.First works perfectly. I can make changes to my dynamic list(metion in one of my replies) without having to touch the code. 

[Vijay_A_Verma]

What is the rationale behind the answer? If you can explain why the answer should be A, B and C that would be great. 

Why the third row answer is C?

[citadel555]

Oh appolgies thats a typo it should be A