Forum Discussion
[Power Query - M] Text.Contains
hi, rfaure
1. Import to TABLE 1
2. Import to TABLE2
3. Change the query in TABLE1
4. In QUERY EDITOR (the code below)let Fonte = Excel.Workbook(File.Contents("C:\Users\adm\Desktop\KUNDOO 345233\KUNDOO_345233.xlsx"), null, true), DATES_Sheet = Fonte{[Item="TABELA1",Kind="Sheet"]}[Data], #"Cabeçalhos Promovidos" = Table.PromoteHeaders(DATES_Sheet, [PromoteAllScalars=true]), #"Colunas Renomeadas" = Table.RenameColumns(#"Cabeçalhos Promovidos",{{"Column1", "Column0"}}), #"Personalização Adicionada" = Table.AddColumn(#"Colunas Renomeadas", "TABELAS", each TABELA2 ), #"TABELAS Expandido" = Table.ExpandTableColumn(#"Personalização Adicionada", "TABELAS", {"Column1", "Column2"}, {"Column1", "Column2"}), #"Personalização Adicionada1" = Table.AddColumn(#"TABELAS Expandido", "Personalizar", each if Text.PositionOf([Column0],[Column1])>=0 then "Ok!" else "Nk!"), #"Tipo Alterado" = Table.TransformColumnTypes(#"Personalização Adicionada1",{{"Column0", type text}, {"Column1", type text}, {"Column2", type text}, {"Personalizar", type text}}) in #"Tipo Alterado"Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
rfaure - are you aware of the fact, that multiple strings can be contained in your data? What is your expected result then?
In your first post for example:
Column1:
AAA-CCC/CCC --->AGRA
CCC BBB/DDD ---> BGRB
DDD-AAA CCC ---> AGRA
But what if u have
AAA-BBB/CCC ---> both "AAA" and "BBB" are matched. What should be displayed in the other column? Only AGRA? Only BGRB? Both?
eniX Hello,
In these conditions, I take the first value found. Anyway the original problem comes from the database used which is not clean. We are looking to fix this problem on the SQL server. But it takes time because we go through a provider.
Rfranca Your solution does not work and does not answer the problem. sometimes the result is wrong. I thank you all for your help. I take again your example to explain what do not agree.
I come back to my function from the beginning.
I have a table named "MatriceOCAM" with two columns "Nom OCAM" and "Réseau"
"Nom OCAM" contains the elements to search in a string. "Network" contains the searched elements (when an element of "OCAM Name" has been found, I want to recover the associated "Network" element)
For this function I create in "Get Data" a new "Blank Query"
WordSearch = Table.Column(#"MatriceOCAM", "Nom OCAM"),
Here I try to load in a variable the column "Nom OCAM" of the table "MatriceOCAM"
WordWanted = Table.Column(#"MatriceOCAM", "Réseau"),
Here, this is the same but for the column "Réseau"
SearchWord = (InputText, Position) => if Text.Contains(InputText,WordSearch(Position))= true then Result = WordWanted(Position) else @SearchWord(InputText, Position+1)
Then here, I created a recursive function with an input parameter string and the last known position. In the function I search with the function "Text.Contains" if my string contains one of the elements of the column "Nom OCAM" of the table "MatriceOCAM". A "Position" counter allows me to know the position in the list at each loop turn. If the "Text.Contains" function returns me "true" then I get the element from the "Réseau" column linked to the "Position" counter. Otherwise I call my recursive function and I increment "Position".
The advanced editor sends me the following error on my function: