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