Forum Discussion

bobbybamber's avatar
bobbybamber
Frequent Visitor
8 years ago
Solved

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 o...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi bobbybamber,

     

    Try this calculated column formula

     

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

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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)

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    MarcelBeug

     

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