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

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

    • rfaure's avatar
      rfaure
      Frequent Visitor
      8 years ago

      I try again to adapt your solution this week. 

       

      Thanks again

      • eniX's avatar
        eniX
        Helper III
        8 years ago

        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?