Forum Discussion
search for value in multiple column based on filter with hierarchy
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.
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"Hi citadel555 ,
As per your clarification, please check if this is the solution you need.
Hope this helps.
8 Replies
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"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?
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?
Oh appolgies thats a typo it should be A
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.Hi citadel555 ,
As per your clarification, please check if this is the solution you need.
Hope this helps.
