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)
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.
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"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]))