Check if Column Contains Item from List in Power Query - Create Text.ContainsAny!

if you're curious how you can use a text contains function but then input multiple items then this video is for you in this video i will show you multiple ways on how you can make sure that your value contains like a partial string and that you can actually input a whole list of those to make things easy for you okay stay tuned the screen we're now looking at is what we ended up on our previous video and in the previous video i showed how you can do an exact match and simply input an entire list to check with so you can have a list like we have up here and check whether one of the items in the full name list is equal to one of those and as we said the downside here is crawl done is the first one here if i would remove the first part and say i'm looking for carl i'm looking for miss pearson i'm looking for chan and weaver you will see that this formula doesn't work anymore let me show you a way on how you can make this work as well we will create a custom function here a custom formula and we're going to use something that is split split text by and then you get a whole list of options here but we're going to split the text by any delimiter which is the first one then we open our brackets here and now what we're going to do is we're going to provide a list of items that we want to look for so we're going to have a splitter split text by any delimiter and actually input all the names that we had right there so we can go we have girl then we have amir we have adrian and we have carlos sure and then we close our curly bracket because this was a list and we close our parenthesis if you now press ok you'll see that this is a function because by itself it doesn't return you the right result but this is the first step that you'll do you just need to make a slight adjustment which is after closing these brackets you can open another bracket and then you're just inputting the column name within the square brackets so you so the function knows where to apply it on and you close your regular bracket press ok now what's happening here is is transforming the items that we have here is splitting the text by each of the items that it finds here in the list so on the first one here wallace is a single item so nothing has been split but we know for the second item we have carl in our search criteria that there is now two items curl has been split and removed and there is two items now here denton wasn't part of it but amir is part of it so this is the first step what you can then do is because this creates a list we can do a list count of this and the list count formula simply counts the amount of items that are in the list here and as you can see the result right here shows you a single value if it's not been found but if it's able to split your first column by one of our keywords in our list it will return a 2 here and this is helpful because now we can use this and we can just add our conditional formula just like with the other items so we could say if the list count is bigger than one then we want to return an a and if that's not the case we're going to return just a bunch of b's like before i'm going to call this one splitter split text just to be sure and if we press ok here you're going to find that these are the actual values that you're looking for so in fact what you've done is the text contains formula you replicated this and why have i replicated this let me still show you that because there is a building function which is text contains and it needs your full name first and then it can look for all the items that we're looking for so we're looking for coral and in this case the crawl line says true but if you would want to put like the multiple items here then it's not possible for us to create a list and put it in here so here's carl and we could for example see if we can add amir in here too close our list item and you'll find that this doesn't work and creates an error so we just replicated a way and how we can do that text contains part now this was method one there is another way if you find this one difficult and the other one that i'm talking about it's not an easy one either but it works like this uh let me first copy the code that we had here earlier that saves us some time so you create a custom column and we're going to use a function that's called list transform the list that we want to transform is the list that we're looking for as a next argument we're going to write something in between parentheses substring and we're going to make the substring come back and the text continues function just like we did earlier text contains then we're going to look into the full name here and for each of these items here each of the substrings you will need to look for that so we can write substring here close our bracket close our bracket let's call this column list transform press ok so the first one shouldn't match at all so if we click on the list that has been created we see that it's been checking and none of these items is true the second item here has one true item in there and that's the first item so carl when you do your text contains function it actually finds it on this row crawl is true and on this row the second item is true which is amir so this is a good start and again what you could do here is either you could go for like um let's go back to the function here and there is a function that is called lists dot any true and the list of any true function looks into the list that you provide so the code that we had here was a list that we provided and from the code that we provide is checking if any of those in the list has a true value now if we press ok you'll find that also these items here return true on the right part and then the less step to replicate exactly what we did earlier would be to wrap this in an if this part that we just checked is true then return as an a else return as a bunch of b's like earlier and exactly just like that we have replicated a text contains function but then for multiple items in your input okay thanks for watching guys those were two methods in which you can replicate the text contains function with multiple items to check for was that difficult do you know another way please let me know in the comments below because i love just finding different ways on how to approach the problem solving here also if you're new to the channel or if this was valuable a like really helps my channel so please like my video and i hope to see you in the next one [Music] you

Channel: BI Gorilla

Views: 63,654

Keywords: power bi desktop tutorials for beginners, power bi desktop, power bi desktop tutorial, power bi, business intelligence, power query, power query tutorial, power query for beginners, text.contains, table.selectrows, value contains list item, text.contains multiple items, contains multiple substrings, contains substring, filter from list, power query filter list, filter using list, power query filter column contains list, list as filter argument, filter column based on list

Id: lXqRceLYlMQ

Length: 8min 4sec (484 seconds)

Published: Wed Sep 01 2021