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
Icon for Community Champion rankCommunity Champion
8 years ago

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"
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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

         

  • soni27's avatar
    soni27
    Icon for Helper I rankHelper I
    8 years ago

    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