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

rfaure's avatar
rfaure
Frequent Visitor
8 years ago

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:

Rfranca's avatar
Rfranca
Resolver IV
8 years ago

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

  • Rfranca's avatar
    Rfranca
    Resolver IV
    8 years ago

    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.

     

     

     

    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

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

  • rfaure's avatar
    rfaure
    Frequent Visitor
    8 years ago

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

     

     

    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.

     

     

    Then using a query function I compile all the files.

     

     

    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