Forum Discussion
getting words that contains diacritics from text
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.
Most Valuable ProfessionalReplace 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.
Actually, I realized that I can just remove {0} part. And also, I do not even need the error checking because for items that are not found it returns empty list. And in the next step I can expand the list (which returns null if there is no match and, of course, creates duplicate rows in case there are several words with diacritics but this is OK with me).
So this is what I have done:
AddedNamePartsWDiacriticsOnly = Table.AddColumn(#"Split Column by Character Transition", "Name Parts with Diacritics", each List.Select([Name Parts], (x)=>List.ContainsAny(Text.ToList(x),List.Buffer(DiacriticsList), Comparer.OrdinalIgnoreCase)), List.Type), ExpandedNamePartswithDiacritics = Table.ExpandListColumn(AddedNamePartsWDiacriticsOnly, "Name Parts with Diacritics"), FilteredOutNonDiacritics = Table.SelectRows(ExpandedNamePartswithDiacritics, each ([Name Parts with Diacritics] <> null))It should be obvious to me from the beginning. Thank you for your patience and persistence. And also thank you for looking into the substring-search solution.