cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

[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 :

 

Capture.PNG

 

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.

 

Capture.PNG

 

Then using a query function I compile all the files.

 

Capture.PNG

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: [Power Query - M] Text.Contains Part 2

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: [Power Query - M] Text.Contains Part 2

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: [Power Query - M] Text.Contains Part 2

@v-shex-msft 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.

Highlighted
Solution Specialist
Solution Specialist

Re: [Power Query - M] Text.Contains Part 2

Highlighted
Community Support
Community Support

Re: [Power Query - M] Text.Contains Part 2

HI @rfaure,

 

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

9.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: [Power Query - M] Text.Contains Part 2

Hello everyone,

 

With the solution of @drewlewis15, I was able to solve my problem. But the base becomes very heavy. The original flat files are already heavy. Maybe should go through an ETL dedicate.

 

Thank you all.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors