Forum Discussion
getting words that contains diacritics from text
Hi, I have a column with names. Names sometimes consist of several words and I need to extract only the words that have diacritics. For example, from the 1st row "Bühnen/Buehnen/Buhnen", I need to get the word "Bühnen" only. The diacritics characters are specified in an separate list.
I wanted to use List.Intersect function with custom comparer. I found several articles that made me think this should be doable:
https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/
https://gorilla.bi/power-query/complete-guide-to-lists/#remove-values-from-lists
I also tried to adapt a solution provided in attached pbix file from ImkeF in this post, but I was unsuccessful.
When I try to use custom comparer I get an error: "A custom comparer cannot be used in this context."
I am not sure whether this is a limitation of this List.Intersect function or whether I got the syntax wrong. I thought that by using (x,y) as function signature, x would stand for an item from "Name Parts" list, and y would stand for an item from DiacriticsList, and that List.Intersect will make the comparison recursively for all items in the list.
Here is my code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RU/LCsIwEPyVpSelFr/BClasFlG8WHowcduEpinkcdAPyym3/Jg1ip6G2Zndma3rJA+eSZTL3OIX35A0izo5UxacJJaySK/pBmbXUQhUkMJGBbcVnLJ5FFfKWAWFCI72kyHO1ukRDsERFPqhDQ64PIx/Ei1F8NWTWNVNN88GeWeQMsl7KAbySd0Ff+pQQmG1gdJKbbQZ2zaLYjnlrXsZl29MZHCR9yhUwe0FwlGNLYdcWc1+H/nT1K55AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
FullNames = Table.TransformColumnTypes(Source,{{"Name", type text}}),
DiacriticsList = List.Buffer({"ö", "ü"}),
SeparatorsList = List.Buffer({",","-"," ","&",".","/","(",")"}),
DuplicatedFullNames = Table.DuplicateColumn(FullNames, "Name", "Name Copy"),
ConvertedNamePartsToList = Table.SplitColumn(DuplicatedFullNames, "Name Copy", Splitter.SplitTextByAnyDelimiter(SeparatorsList, QuoteStyle.None), {"Name Parts"}, null, ExtraValues.List),
NamePartsWDiacriticsOnly = Table.AddColumn(ConvertedNamePartsToList, "Name Parts with Diacritics", each List.Intersect({[Name Parts], DiacriticsList}, (x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), List.Type)
in
NamePartsWDiacriticsOnly
Any help would be really appreciated.
Just use Text.Combine additionally.
NamePartsWDiacriticsOnly = Table.AddColumn(ConvertedNamePartsToList, "Name Parts with Diacritics", each try Text.Combine(List.Select([Name Parts], (x)=>List.ContainsAny(Text.ToList(x),DiacriticsList, Comparer.OrdinalIgnoreCase)),", ") otherwise null)Regarding words part, I will come back later as I am trying to have a one line solution for words matching rather than character matching.
7 Replies
- Vijay_A_Verma
Most Valuable Professional
Replace the statement before in with this
NamePartsWDiacriticsOnly = Table.AddColumn(ConvertedNamePartsToList, "Name Parts with Diacritics", each try List.Select([Name Parts], (x)=>List.ContainsAny(Text.ToList(x),DiacriticsList, Comparer.OrdinalIgnoreCase)){0} otherwise null)Thank you very much Vijay_A_Verma,
it really solved the problem at hand. Interesting workaround - split the text into individual characters and then check whether any of the diacritics characters are there!
Still, I am curious how could this be handled if I needed to look for substrings, not just for single characters.
Let's say I would like to search also for words where the diacritics were transcribed into multiple ANSI characters. For example, if the DiacriticsList would look like this:
DiacriticsList = List.Buffer({"ö", "ü", "oe", "ue"})Any ideas how this could be handled?
Hi Vijay_A_Verma, there is one thing I realized as I used this solution with my full data set which was not clear from the example data set. Sometimes there is more than one word with diacritics in the text. However, this solution keeps just the first word. I would actually need the list of all words with diacritics.
For example, if you have "Z+F (Züller + FröHlich)" instead of "Z+F (Zoller + FröHlich)", only word "Züller" would be kept. Second word will be omitted even though I need it as well.
Any idea how could I get this?
- Vijay_A_Verma
Just use Text.Combine additionally.
NamePartsWDiacriticsOnly = Table.AddColumn(ConvertedNamePartsToList, "Name Parts with Diacritics", each try Text.Combine(List.Select([Name Parts], (x)=>List.ContainsAny(Text.ToList(x),DiacriticsList, Comparer.OrdinalIgnoreCase)),", ") otherwise null)Regarding words part, I will come back later as I am trying to have a one line solution for words matching rather than character matching.