Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rfaure
Helper I
Helper I

[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
Rfranca
Resolver IV
Resolver IV

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

View solution in original post

14 REPLIES 14
Rfranca
Resolver IV
Resolver IV

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

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

 

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

I try again to adapt your solution this week. 

 

Thanks again

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

Clipboard03.gif

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

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!".

You can find my data on xlsx in a zip with this link :

 

https://takeafile.com/?f=tuyegijesu

 

Thanks you

 

hi, @rfaure 

 

Hi
 
Check if this is what you need?
One only question that remained is when an [Complémentaire] has two [Réseau] like you want to do with it.

 

Clipboard011.gifClipboard012.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

I'm sorry, I did not really understand the manipulation. My adaptation was completely erratic. But your solution works properly. thank you very much

hi, @rfaure

 

 

To help you apply to your database, I'm sending the sample file.

 

https://takeafile.com/?f=gisafojiju

 

Please feel free to ask if you have any other issue.

Best Regards,
Rfranca

I now have a new problem to adapt the solution to the actual database. he returns the following error :

 

Capture.PNG

 

I think I know where this error comes from. Currently my database is created thanks to a multitude of CSV file (monthly extraction). This file is compiled together with a query function that I adapted.

 

Example : 

 

I have the following directory consists of several csv file that I have to compile into one to form a single table.

 

Capture.PNG

 

Then using a query function I compile all the files.

 

Capture.PNG

 

RecupPatient :

 

let
    RecuperationPatient = (MonFichier) =>
let
    Source = Csv.Document(File.Contents(MonFichier),[Delimiter=";", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Premières lignes supprimées" = Table.Skip(Source,2),
    #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true])
in
    #"En-têtes promus"
in
    RecuperationPatient

 

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.