cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bobbybamber Frequent Visitor
Frequent Visitor

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Search a Field Within A Query List

Hi @bobbybamber,

 

Try this calculated column formula

 

=FIRSTNONBLANK(FILTER(VALUES(Country[Country]),SEARCH(Country[Country],[Text],1,0)),1)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
MarcelBeug Super Contributor
Super Contributor

Re: Search a Field Within A Query List

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"
Specializing in Power Query Formula Language (M)
NatashaSchuster Regular Visitor
Regular Visitor

Re: Search a Field Within A Query List

@MarcelBeug

 

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

MarcelBeug Super Contributor
Super Contributor

Re: Search a Field Within A Query List

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.

 

 

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: Search a Field Within A Query List

Hi @bobbybamber,

 

Try this calculated column formula

 

=FIRSTNONBLANK(FILTER(VALUES(Country[Country]),SEARCH(Country[Country],[Text],1,0)),1)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

bobbybamber Frequent Visitor
Frequent Visitor

Re: Search a Field Within A Query List

Just what I was looking for, thank you!

Super User
Super User

Re: Search a Field Within A Query List

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
NatashaSchuster Regular Visitor
Regular Visitor

Re: Search a Field Within A Query List

Thanks @MarcelBeug

 

It's really powerful feature.  I just noticed that there is no comma between [Splitted])m and  ([Column1], so  [Splitted])([Column1])  are squashed together.  How is this even possible to have two separate objects as a single parameter for Splitter.SplitTextByEachDelimiter?   Is this pertains only to the splitter functions or it's normal to see across other M functions? 

 

Thank you

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Search a Field Within A Query List

Natasha: In my previous post I explained which parts can be distinguished in my formula's.

 

Maybe it will be clearer if I separate the 2 functions of the first split:

 

So step MySplitterOnCountryFunction adds a column with a function in each row thet can be used to split a string on the delimiters in CountryList. So the parameter here is CountryList.

Step SplittedOnCountry uses those functions to perform the actual split of the strings in Column1. So the parameter here is Column1.

 

In my original solution this is combined in 1 step, so it appears like: functionx(parameter1)(parameter2)

If you split this in 2 steps it looks like:

functiony = functionx(parmeter1)

FinalResult = functiony(parameter2)

 

 

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}}),
    MySplitterOnCountryFunction = Table.AddColumn(#"Changed Type","MySplitterOnCountryFunction", each Splitter.SplitTextByAnyDelimiter(CountryList)),
    SplittedOnCountry = Table.AddColumn(MySplitterOnCountryFunction,"Splitted", each [MySplitterOnCountryFunction]([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,{"MySplitterOnCountryFunction","Splitted"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)
NatashaSchuster Regular Visitor
Regular Visitor

Re: Search a Field Within A Query List

@MarcelBeug

 

Intersting! So, the splitter funcitionality is clear. 

 

Does this mean that  ([Column1]is a part of the Table.AddColumn function, not splitter function,  right? 

 

 

SplittedOnCountry = Table.AddColumn(#"Changed Type","Splitted", each Splitter.SplitTextByAnyDelimiter(CountryList)([Column1]))



Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 156 members 1,757 guests
Please welcome our newest community members: