Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ge...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Below is a solution based on function (Edit - Couldn't make oneliner. Will try sometime in future to make one liner). Replace Source and DiacriticsList  appropriately. In the end, you can expand tables.

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FullNames = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    DiacriticsList = List.Buffer({"ö", "ü", "ó", "ã", "í","č","è", "Sa", "vad", "r" }),
    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),
    //Start of function
    fxConvertfunc=(InputList,DiacriticsList)=>
let
    #"Converted to Table" = Table.FromList(InputList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", (x)=> List.AnyTrue(List.Transform(DiacriticsList, each Text.Contains(x[Column1],_)))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table",
    //End of function
    #"Added Custom" = Table.AddColumn(ConvertedNamePartsToList, "Custom", each fxConvertfunc([Name Parts],DiacriticsList))
in
    #"Added Custom"

 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Vijay_A_Verma ,

 

thank you for looking further to another possible solution. It was really an interesting take on how to tackle the problem I needed to solve. I will add it to my toolbox of useful ideas.

 

In the end I have simplified your function like this:

    fxConvertfunc=(InputList,DiacriticsList)=>
let
    #"Converted to Table" = Table.FromList(InputList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", (x)=> List.AnyTrue(List.Transform(DiacriticsList, each Text.Contains(x[Column1],_)))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = #"Filtered Rows"[Column1]
in
    #"Removed Columns"

Having that transposed table as a result was actually a bit tricky. I had a lot of rows containing tables with different number of columns. PowerQuery was unable to determine correctly how many columns I actually have in those tables. If I wanted to expand them, it would require me to know ahead what is the maximum number of columns in the tables. Since I wanted to have a list of results in the individual rows, my adjustment was one of the ways how I could achieve this.

 

I really appreaciate all the effort you put into helping me.