Forum Discussion

rfaure's avatar
rfaure
Frequent Visitor
8 years ago
Solved

[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

 

  • Rfranca's avatar
    Rfranca
    8 years ago

    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

14 Replies

  • Rfranca's avatar
    Rfranca
    Resolver IV
    8 years ago

    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's avatar
      rfaure
      Frequent Visitor
      8 years ago

      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's avatar
        Rfranca
        Resolver IV
        8 years ago

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