Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Ken Puls blogged about this here:
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Ken Puls blogged about this here:
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
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
@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.
HI @rfaure,
I test on your query but it seems works on my side.
Regards,
Xiaoxin Sheng
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.
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |