Forum Discussion
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)
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
Awesome! Thank you very much. Now, all is clear. Thanks for the video it is super helpful.