cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rfaure Frequent Visitor
Frequent Visitor

[Power Query - M] Text.Contains

Hello everyone,

I am a beginner in the language M. And I come to call for your help.
I wish in advance to apologize for my writing, I am not good in English. (Thank you Google Translate)

The problem :

I have two tables

first table with the following data:

 

Coloumn1
AAA-CCC/CCC
CCC BBB/DDD
DDD-AAA CCC

second table with the following table :

 

Column1Column2
AAAAGRA
BBBBGRB

In my first table, I try to check each character. If a string corresponds to an element of my column 1 of my second table, I get the corresponding element of my column 2.

I used the following code but I don't understand how to declare some variable :

 

let
    WordSearch = Table.Column(#"MatriceOCAM", "Nom OCAM"), 
    WordWanted = Table.Column(#"MatriceOCAM", "Réseau"),
    SearchWord = (InputText, Position) => if Text.Contains(InputText,WordSearch(Position))= true then Result = WordWanted(Position) else @SearchWord(InputText, Position+1)
in
    Result

Thanks , bye

 

1 ACCEPTED SOLUTION

Accepted Solutions
Rfranca Member
Member

Re: [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"

 

 

Clipboard01.gifClipboard02.gifClipboard03.gif

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

14 REPLIES 14
Rfranca Member
Member

Re: [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"

 

 

Clipboard01.gifClipboard02.gifClipboard03.gif

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 Frequent Visitor
Frequent Visitor

Re: [Power Query - M] Text.Contains

Thanks for you answer and take time for my probleme,

 

But I did not clearly explain my problem in real condition.

 

The principle stays the same. I want to add the values ​​in the "Réseau" column (see PICTURE_2.png) to a new column in the "Patient" table. To do this I search in the patient table, "Complementaire" column (see PICTURE_1.png), the values ​​corresponding to the column "Nom OCAM" (see PICTURE_2.png). But to do this I have to use the Text.Contains function because the data is not cleaned up.

 

PICTURE_1.png

 

PICTURE_2.png

 

Rfranca Member
Member

Re: [Power Query - M] Text.Contains

hi, @rfaure

 

OK!
You must include the "Réseau" column of the table "MatriceOCAM"
However, the correspondence must be through the "Text.Contains" function, ie for each line of the "Complementaire" field of the "Patient" table, it should look in the "Nom OCAM" column of the "Réseau" table.

it is?
If it is not very different from what I sent, but do it here and send it ...

rfaure Frequent Visitor
Frequent Visitor

Re: [Power Query - M] Text.Contains

I try again to adapt your solution this week. 

 

Thanks again

eniX Member
Member

Re: [Power Query - M] Text.Contains

@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?

rfaure Frequent Visitor
Frequent Visitor

Re: [Power Query - M] Text.Contains

@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.

rfaure Frequent Visitor
Frequent Visitor

Re: [Power Query - M] Text.Contains

@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.

Clipboard03.gif

rfaure Frequent Visitor
Frequent Visitor

Re: [Power Query - M] Text.Contains

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:

Capture.PNG

Rfranca Member
Member

Re: [Power Query - M] Text.Contains

HI, @eniX

 

sorry

Some alternatives to solve your problem:
1. Send the two columns of data as a sample to validate my solution.

or
2. I believe that if you just need the records that match your image, try deleting the [COLUMN1] column and filter the [CUSTOM] column with only the "Ok!".

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 353 members 3,028 guests
Please welcome our newest community members: