Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bobbybamber
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
Ashish_Mathur
Super User
Super User

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
Ashish_Mathur
Super User
Super User

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/

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 Singh

Hi,

 

Could you show some data and also share the expected result.


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

Ashish, 

 

They are two seperate tables.

 

Column/List A - Query1:

 

Key Information.1

ACCOUNTNBR:
APPNAME:
ANNVOLUME:
ASTM:
BILLTYPE:
CICONTACT:
CIPN:
COMPONENT:
CORE DIA:
DELIVERY:
DEPT:
DEST:
DIA:
DIMENSIONS:
DRWNUM:
ECT:
ENDUSE:
EQUIPMENT:
FEETYPE:
FLANGE DIA:
FORM:
GRADE:
HANDLE:
HAZMAT:
HEIGHT:
HIMETAL:
ITEM:
JOB TITLE:
LENGTH:
LOCATION:
MATERIAL:
METERNBRS:
MFGPN:
MFRNAME:
MILESTONE:
MODEL:
ORIGIN:
OTHERINFO:
PACKFACTOR:
PACKTYPE:
PAYTERM:
PERFORATION:
PRIMARY NAME:
PRINTING:
PROBLEM:
PROCESS:
QUOTE:
RAM:
REQUESTOR:
SERIALNUM:
SERVICEFREQ:
SERVICELOC:
SIZE:
SOW:
SPEC:
SPECIAL FEATURES:
SUBCAT:
SUPCONTACT:
TASK:
TERM:
THKS:
TYPE:
UNIT:
UNITCOST:
UOM:
VERSION:
WIDTH:

 

Column/List B - From Query 2 (It's a long list, I am sharing just 2 for example: 

 

PO Line Description
CONVERGINT,TYPE:Embedded Technical Resource - Chris Cordes,SOW:System Administration,JOB TITLE:System Administration,BILLTYPE:Monthly,FEETYPE:Fixed Fee,CICONTACT:Mike Jackson,TERM:1 year,SERVICELOC:SP-ZV-02,OTHERINFO:6/16/17-6/16/18 - RBC1030048P
CONSULTING (PROJECT MANAGER AND MASTER DATA ARCHITECT),TYPE:Corning Global Template Design,SOW:In Place ,FEETYPE:Hourly Rate,CICONTACT:Karin Stroh,TERM:Feb 12, 2018 - May 4, 2018

 


List A Contains the Key information, out of which 3 or more should be available within each decription in List B.

 

I just want to have the results, in a way that, query looks for each value of List A, in list B, and if present then return in in a new column.

 

Our Results should look like below. (I've used some of the methods, however none of them are effective. Your Previous solution looks good, however, it's returning only one of the valus. I'll really appreciate your support.

PO Line DescriptionKey Information Used (Result)
CONVERGINT,TYPE:Embedded Technical Resource - Chris Cordes,SOW:System Administration,JOB TITLE:System Administration,BILLTYPE:Monthly,FEETYPE:Fixed Fee,CICONTACT:Mike Jackson,TERM:1 year,SERVICELOC:SP-ZV-02,OTHERINFO:6/16/17-6/16/18 - RBC1030048PType:, SOW:, JOB TITLE:, BILLTYPE:, FEETYPE:, CICONTACT:, TERM:, SERVICELOC:, OTHERINFO:
CONSULTING (PROJECT MANAGER AND MASTER DATA ARCHITECT),TYPE:Corning Global Template Design,SOW:In Place ,FEETYPE:Hourly Rate,CICONTACT:Karin Stroh,TERM:Feb 12, 2018 - May 4, 2018Type:, SOW:, JOB TITLE:, FEETYPE:, CICONTACT:, TERM:

Hi,

 

I am sorry but i do not know how to solve thsi problem.


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

Just what I was looking for, thank you!

You are welcome.


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

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)

@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

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)

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

 

 

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)

@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]))



MarcelBeug;

 

Great Method, My List is  have like

 

TR

TRQ

TRP

 

The Spliier Picks up TR only even my string has TR and TRQ and TRP; It splits only TR.

 

Text Examples

PT-TR-Test0

PT-TRQ-Test1

PTTTT-TRP-Test2

 

 

Query results

TR

TR

TR

 

TR

TR

 

Results rexpected as :

TR

TRQ

TRP

 

Please share your advise on this

That depends on how you define "is part of". Actually, to me that is rather confusing.

I think the question should be: which part is parameter for which function?

 

The parameters for function Table.AddColumn are:

#"Changed Type"

"Splitted"

each Splitter.SplitTextByAnyDelimiter(CountryList)([Column1])

 

The parameter for function Splitter.SplitTextByAnyDelimiter is:

CountryList

 

The parameter for function Splitter.SplitTextByAnyDelimiter(CountryList) is:

Column1

 

So if I translate "is part of" to "is parameter of" then the answer is: no.

Column1 is not "part of" function Table.AddColumn, but

Column1 is "part of" function Splitter.SplitTextByAnyDelimiter(CountryList)

 

Specializing in Power Query Formula Language (M)

@MarcelBeug

 

Wow! That's an unexpected twist....  Column1 is a parameter of  Splitter.SplitTextByAnyDelimiter(CountryList). 

Sorry I am taking too much of your time with this but just one more question.   

 

 

May I please know how exactly  Column1 acts as splitter funciton parameter if splitter function is closed with right parentheses after CountryList .  Splitter.SplitTextByAnyDelimiter(CountryList) ([Column1]) <--- right parentheses closes the splitter function...

 

This is the first time I see such behavior ....   

 

Thank you very much for your time @MarcelBeug

Well, there are 2 splitter functions:

 

Splitter.SplitTextByAnyDelimiter is a splitter function that creates another splitter function.

Splitter.SplitTextByAnyDelimiter(CountryList) creates a splitter function that splits a string on the items in CountryList.

 

Splitter.SplitTextByAnyDelimiter(CountryList) is the second splitter function that actually splits a string.

Splitter.SplitTextByAnyDelimiter(CountryList)(Column1) splits the string in Column1, resulting in a list.

 

So the output of function Splitter.SplitTextByAnyDelimiter is a function.

And the output of function Splitter.SplitTextByAnyDelimiter(CountryList) is a list with substrings.

 

It may be easier to understand if you split Splitter.SplitTextByAnyDelimiter(CountryList)(Column1) in 2 steps (as I already explained in one of my previous posts), e.g.:

SplitTextOnCountry = Splitter.SplitTextByAnyDelimiter(CountryList),

SplittedColumn1 = SplitTextOnCountry(Column1)

 

A simple example in the query below, with a video that takes you through the steps.

 

let
    Text = "This text will be split on spaces,and on commas",

//  Option 1: 1 line    
    SplittedText1 = Splitter.SplitTextByAnyDelimiter({" ",","})(Text),

// Option2: 2 lines, doing exactly the same as the previous 1 line:
    SplitTextOnSpacesAndCommas = Splitter.SplitTextByAnyDelimiter({" ",","}),
    SplittedText2 = SplitTextOnSpacesAndCommas(Text)
    
in
    SplittedText2

 

Specializing in Power Query Formula Language (M)

@MarcelBeug

 

Awesome! Thank you very much. Now, all is clear. Thanks for the video it is super helpful. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.