Forum Discussion
Search a Field Within A Query List
Hi guys,
Apologies if this is obvious/has already been asked - I can't really work out what to even search for to find the answer so figured it'd be quicker to ask.
Anyway - I have a dataset of campaign names that *contain* country names, but also other information. I'm wondering if there's a scalable way of created a new column that searches the text in the field of the campaign name against a query list with a list of countries in it, then if it finds it produces that country name as the result.
For example:
Say I have a raw doc, the Campaign name column contains these names:
"Campaign 1 - Spain - Branding"
"CPGN 1 - Spain - Direct Response"
"Campaign 21 - Germany - Branding"
"Campaign 1 - UK - Branding"
(In other words, campaign names with no pattern/naming consistency).
Could I create a query list that contained:
Spain
Germany
UK
As three options, then use a formula basically to say - attempt to find any of the countries from the query list in the campaign name and, if successful, put the result in that new column.
-
FWIW - It's possible in Excel - although I've only really borrowed this formula - if this is any help this is what I use:
=LOOKUP(1E+100,SEARCH(Table4[Country List],[@Campaign]),Table4[Country List])
-
Thanks,
Hi bobbybamber,
Try this calculated column formula
=FIRSTNONBLANK(FILTER(VALUES(Country[Country]),SEARCH(Country[Country],[Text],1,0)),1)
19 Replies
My suggestion would be to first split the column on (any) country names and use the result as delimiters to split the original text again, so the country will remain.
(The Source step is just the result from entering data via option "Enter Data").
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MLUjMTM9TMFTQVQgGMvOAtFNRYl5KZl66UqwOUEWAux+KrEtmUWpyiUJQanFBfl5xKkQRzBgjkEr31KLcxLxKDJOQ7Qr1Rpf2yk8tyIQYB7Eqs1jB3xvMD8+ohBmKkFeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), SplittedOnCountry = Table.AddColumn(#"Changed Type","Splitted", each Splitter.SplitTextByAnyDelimiter(CountryList)([Column1])), RemovedBlankListItems = Table.TransformColumns(SplittedOnCountry,{{"Splitted", each List.Select(_, each _ <> "")}}), AddedCountry = Table.AddColumn(RemovedBlankListItems, "Country", each Splitter.SplitTextByEachDelimiter([Splitted])([Column1])), FirstNonBlankOrNull = Table.TransformColumns(AddedCountry,{{"Country", each List.First(List.Select(_, each _ <> ""),null), type text}}), #"Removed Columns" = Table.RemoveColumns(FirstNonBlankOrNull,{"Splitted"}) in #"Removed Columns"Very interesting solution.
There is not much documentation on these Splitter functions.I see you are using optional quoteStyle as a second parameter which is [Column1] in both Splitter functions. What does this optional quoteStyle really do. Do you mind explain to us ?
Also, how are you returning countries back with Splitter.SplitTextByEachDelimiter ... it's done on the previous step RemovedBlankListItems and no countries are there ...
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), SplittedOnCountry = Table.AddColumn(#"Changed Type","Splitted", each Splitter.SplitTextByAnyDelimiter(CountryList)([Column1])), RemovedBlankListItems = Table.TransformColumns(SplittedOnCountry,{{"Splitted", each List.Select(_, each _ <> "")}}), AddedCountry = Table.AddColumn(RemovedBlankListItems, "Country", each Splitter.SplitTextByEachDelimiter([Splitted])([Column1])), FirstNonBlankOrNull = Table.TransformColumns(AddedCountry,{{"Country", each List.First(List.Select(_, each _ <> ""),null), type text}}), #"Removed Columns" = Table.RemoveColumns(FirstNonBlankOrNull,{"Splitted"}) in #"Removed Columns"Thanks
Thanks Natasha.
As a matter of fact, I'm not using the second parameter of the splitter functions.
In this code part of the second split:
Splitter.SplitTextByEachDelimiter([Splitted])([Column1])
the red part is the invocation of the splitter function.
The output from the splitter function is another function on its own, so you can regard the red part as a function that splits text on the delimiters in [Splitted]. The parameter for that red function is [Column1], i.e. the original text strings.
From the first split, all other text parts but the country code remain (if a text is splitted, the result is a list without the delimiters).
So when the original text is split again with those parts as delimiters, just the opposite happens, and only the country code remains.
With the second split, the function Splitter.SplitTextByEachDelimiter is used to ensure that the splits are done in the right sequence: the text is first split on the first delimiter, the next split is on the second delimiter, and so on.
Suppose the string would be "abUKa" and "UK" is filtered out as country code in the first split, leaving "ab" and "a" as delimiters for the second split. If these wouldn't be applied in turn, then the first "ab" might be split after the "a". By applying the delimiters in turn, the result is a list with 3 items <blank>,UK,<blank>. As blanks are filtered out, "UK" remains.
Hi bobbybamber,
Try this calculated column formula
=FIRSTNONBLANK(FILTER(VALUES(Country[Country]),SEARCH(Country[Country],[Text],1,0)),1)
Just what I was looking for, thank you!
You are welcome.
Hi Ashish,
What if a string has more than 1 country. For me, I am trying to find our 3 or more than 3 values from a column in a string, and return.
rgds,
Vikrant SinghHi,
Could you show some data and also share the expected result.
