Forum Discussion

rfaure's avatar
rfaure
Frequent Visitor
8 years ago
Solved

[Power Query - M] Text.Contains Part 2

Hello again,

 

I follow my previous problem. (Link of topic)

 

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

 

Thanks for your help

 

Need Help
Tutorial Requests

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi rfaure,

     

    Can you please share the detail steps of 'patient' table? Based on error message, it seems like you reference previous steps and it caused the dead cycle.

     

    Regards,

    Xiaoxin Sheng

    • rfaure's avatar
      rfaure
      Frequent Visitor
      8 years ago

      Anonymous Hi,

       

      Thanks for your help.

       

      let
    Source = Folder.Files("C:\MonOneDrive\OneDrive\Power BI - BDD\Cosium\Patient"),
    AddCol_MonFichier = Table.AddColumn(Source, "MonFichier", each [Folder Path]&[Name]),
    AddCol_FuncRecup = Table.AddColumn(AddCol_MonFichier, "FuncRecup", each RecupPatient([MonFichier])),
    #"FuncRecup développé" = Table.ExpandTableColumn(AddCol_FuncRecup, "FuncRecup", {"Nom", "Prénom", "Denomination", "Date Naiss.", "Age", "Centre", "Num. client", "Caché", "Exclus", "Date création", "Numéro de voie", "Type de voie", "Adresse", "Nº app. ou BAL-Etage-Couloir-Escalier", "AdresseL3", "AdresseL4", "Adr. CP 1", "Adr. ville 1", "NPAI", "Origine", "N° de sécu", "CPAM", "Complémentaire", "Date dernière facture", "Prescripteur Aud", "Prix vendu", "Date vente", "Num. série", "Statut", "Code Produit", "Libellé", "Intra/Cont.", "Durée Garantie", "Côté", "Marque"}, {"FuncRecup.Nom", "FuncRecup.Prénom", "FuncRecup.Denomination", "FuncRecup.Date Naiss.", "FuncRecup.Age", "FuncRecup.Centre", "FuncRecup.Num. client", "FuncRecup.Caché", "FuncRecup.Exclus", "FuncRecup.Date création", "FuncRecup.Numéro de voie", "FuncRecup.Type de voie", "FuncRecup.Adresse", "FuncRecup.Nº app. ou BAL-Etage-Couloir-Escalier", "FuncRecup.AdresseL3", "FuncRecup.AdresseL4", "FuncRecup.Adr. CP 1", "FuncRecup.Adr. ville 1", "FuncRecup.NPAI", "FuncRecup.Origine", "FuncRecup.N° de sécu", "FuncRecup.CPAM", "FuncRecup.Complémentaire", "FuncRecup.Date dernière facture", "FuncRecup.Prescripteur Aud", "FuncRecup.Prix vendu", "FuncRecup.Date vente", "FuncRecup.Num. série", "FuncRecup.Statut", "FuncRecup.Code Produit", "FuncRecup.Libellé", "FuncRecup.Intra/Cont.", "FuncRecup.Durée Garantie", "FuncRecup.Côté", "FuncRecup.Marque"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"FuncRecup développé",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "MonFichier"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"FuncRecup.Date Naiss.", type datetime}, {"FuncRecup.Date création", type datetime}, {"FuncRecup.Date dernière facture", type date}, {"FuncRecup.Date vente", type datetime}}),
    #"DenominationHomme" = Table.AddColumn(#"Type modifié", "Genre1", each if [FuncRecup.Denomination] = "Abbe" then "Homme" else if [FuncRecup.Denomination] = "Fr" then "Homme" else if [FuncRecup.Denomination] = "Frère" then "Homme" else if [FuncRecup.Denomination] = "M" then "Homme" else if [FuncRecup.Denomination] = "M." then "Homme" else if [FuncRecup.Denomination] = "Maitre" then "Homme" else if [FuncRecup.Denomination] = "Mon Père" then "Homme" else if [FuncRecup.Denomination] = "Monseigneur" then "Homme" else if [FuncRecup.Denomination] = "Monsieur" then "Homme" else if [FuncRecup.Denomination] = "Monsieur l' Abbé" then "Homme" else if [FuncRecup.Denomination] = "Mr" then "Homme" else if [FuncRecup.Denomination] = "Mr.Me." then "Homme" else if [FuncRecup.Denomination] = "Pere" then "Homme" else if [FuncRecup.Denomination] = "Père" then "Homme" else if [FuncRecup.Denomination] = "Pr." then "Homme" else ""),
    #"DenominationFemme" = Table.AddColumn(#"DenominationHomme", "Genre2", each if [FuncRecup.Denomination] = "Ma Soeur" then "Femme" else if [FuncRecup.Denomination] = "Madame" then "Femme" else if [FuncRecup.Denomination] = "Mademoiselle" then "Femme" else if [FuncRecup.Denomination] = "Mell" then "Femme" else if [FuncRecup.Denomination] = "Melle" then "Femme" else if [FuncRecup.Denomination] = "MLE" then "Femme" else if [FuncRecup.Denomination] = "Mlle" then "Femme" else if [FuncRecup.Denomination] = "Mlle." then "Femme" else if [FuncRecup.Denomination] = "Mm" then "Femme" else if [FuncRecup.Denomination] = "Mme" then "Femme" else if [FuncRecup.Denomination] = "Mme." then "Femme" else if [FuncRecup.Denomination] = "Mms" then "Femme" else if [FuncRecup.Denomination] = "Soeur" then "Femme" else if [FuncRecup.Denomination] = "Sr" then "Femme" else ""),
    #"DenominationEnfant" = Table.AddColumn(#"DenominationFemme", "Genre3", each if [FuncRecup.Denomination] = "Enf" then "Enfant" else if [FuncRecup.Denomination] = "Enf." then "Enfant" else if [FuncRecup.Denomination] = "Enfant" then "Enfant" else if [FuncRecup.Denomination] = "Enfant." then "Enfant" else ""),
    #"Personnalisée ajoutée" = Table.AddColumn(DenominationEnfant, "Genre", each if [Genre1] = "Homme" then "Homme" else if [Genre2] = "Femme" then "Femme" else if [Genre3] = "Enfant" then "Enfant" else "Autre"),
    #"Lignes vides supprimées" = Table.SelectRows(#"Personnalisée ajoutée", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Doublons supprimés" = Table.Distinct(#"Lignes vides supprimées", {"FuncRecup.Num. client"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Doublons supprimés"," ans","",Replacer.ReplaceText,{"FuncRecup.Age"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"FuncRecup.Age", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Type modifié1",{"Genre1", "Genre2", "Genre3"})
in
    #"Removed Columns"

      If you want something else, tell me.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI rfaure,

         

        I test on your query but it seems works on my side.

         

        Regards,

        Xiaoxin Sheng