Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |