Alléger requêtes PowerQuery
Messieurs Dames,
Bonjour,
Dans un excel, j'ai réalisé 2 connexions (données, nouvelle requête) vers des fichiers excel particulièrement volumineux (40 Mo pour l'un).
Puis via PowerQuery j'ai effectué différent filtre, tris, suppression de colonnes etc.
Affin d'être sûr d'avoir des données à jours, j'ai paramétré une actualisation des données à chaque ouverture, et une suppression des données lorsque je ferme le fichier.
Tout fonctionne parfaitement.
Seulement, lorsque je lance ce fichier, l'ouverture est particulièrement longue et je souhaite l'optimiser, comment puis je faire ?
Merci d'avance pour votre retour.
Bonjour à tous !
Vous devriez joindre à votre demande un classeur allégé mais représentatif ainsi que votre requête.
N'oubliez pas de bien expliciter le résultat attendu (avec au besoin un exemple "manuel").
Remarque : Une volumétrie conséquente se traduira forcément par une latence.
Hey,
Également il faut jouer sur les paramètres de PowerQuery, la mémoire allouée et les niveaux de confidentialité ;)
@+
JFL,
Je ne peux pas partager le classeur même en l'allégeant.
Car vous n'auriez que les liaisons vers d'autre fichiers non accessible.
Voici les 3 requêtes utilisées :
let
Source = Excel.Workbook(File.Contents("T:\Env_PRD\LV\Extraction\LOG19_Gestion de la Charge_LVC010-LVC073etLVC114.xlsx"), null, true),
#"LOG19BIS_Gestion de la Charge_Sheet" = Source{[Item="LOG19BIS_Gestion de la Charge",Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(#"LOG19BIS_Gestion de la Charge_Sheet", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Source", type text}, {"Centre Charge", type text}, {"Description Centre Charge", type text}, {"Heures Machines", type number}, {"Annee Fabrication", Int64.Type}, {"Semaine Fabrication", Int64.Type}, {"Date Debut Preparation Execution", type datetime}, {"Date Fin Preparation Execution", type datetime}, {"Ordre Fabrication", type text}, {"Operation OF", Int64.Type}, {"Statut Operation", type text}, {"Tache", type text}, {"Description Tache", type text}, {"Projet", type text}, {"Article", type text}, {"Description Article", type text}, {"Planificateur", type text}, {"Nom Planificateur", type text}, {"Famille Planificateur", type text}, {"Priorite", Int64.Type}, {"Machine", type text}, {"Heure Main Oeuvre Allouees", type number}, {"Heure Main Oeuvre Passees", type number}, {"Temps Restant", type number}, {"Temps Restant Calcule", type number}, {"Quantite Planifiee", Int64.Type}, {"Groupe #(lf)Ordre SFC", type text}, {"Temps Preparation Moyen", Int64.Type}, {"Quantite Livree", Int64.Type}, {"Quantite Reste ALivrer", Int64.Type}, {"Quantite Cumulee Achevee", Int64.Type}, {"Quantite Cumulee Rejetee", Int64.Type}, {"Type Produit", type text}, {"Retard Date Debut Preparation", type text}, {"Retard Date Fin Preparation", type text}, {"Date Livraison Requise", type datetime}, {"Date Livraison Planifiee", type datetime}, {"Statut OF", type text}, {"Retard Date Livraison Requise", type text}, {"Commande Client", type text}, {"Position Commande Client", Int64.Type}, {"Sequence Commande Client", Int64.Type}, {"Tiers Acheteur", Int64.Type}, {"Nom Tiers Acheteur", type text}, {"Date MADCPrev", type datetime}, {"Type Commande Client", Int64.Type}, {"Service Vente", type text}, {"Date Debut OF", type datetime}, {"Annee Debut OF", Int64.Type}, {"Semaine Debut OF", Int64.Type}, {"Taille", type text}, {"Pilote Atelier", type text}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Source", "Heures Machines", "Annee Fabrication", "Semaine Fabrication"}),
#"Lignes triées" = Table.Sort(#"Colonnes supprimées",{{"Ordre Fabrication", Order.Ascending}}),
#"Lignes filtrées" = Table.SelectRows(#"Lignes triées", each true),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Lignes filtrées",{"Tache", "Description Tache", "Projet", "Famille Planificateur", "Priorite", "Machine", "Heure Main Oeuvre Allouees", "Heure Main Oeuvre Passees", "Temps Restant", "Temps Restant Calcule", "Groupe #(lf)Ordre SFC", "Temps Preparation Moyen", "Quantite Cumulee Achevee", "Quantite Cumulee Rejetee", "Type Produit", "Retard Date Debut Preparation", "Retard Date Fin Preparation", "Retard Date Livraison Requise", "Commande Client", "Position Commande Client", "Sequence Commande Client", "Tiers Acheteur", "Nom Tiers Acheteur", "Date MADCPrev", "Type Commande Client", "Service Vente", "Annee Debut OF", "Semaine Debut OF", "Taille", "Pilote Atelier"}),
#"Colonnes permutées" = Table.ReorderColumns(#"Colonnes supprimées1",{"Ordre Fabrication", "Statut OF", "Centre Charge", "Description Centre Charge", "Date Debut Preparation Execution", "Date Fin Preparation Execution", "Operation OF", "Statut Operation", "Article", "Description Article", "Planificateur", "Nom Planificateur", "Quantite Planifiee", "Quantite Livree", "Quantite Reste ALivrer", "Date Livraison Requise", "Date Livraison Planifiee", "Date Debut OF"}),
#"Lignes filtrées1" = Table.SelectRows(#"Colonnes permutées", each ([Statut Operation] = "Prêt") and ([Description Centre Charge] = "Transit")),
#"Colonnes supprimées2" = Table.RemoveColumns(#"Lignes filtrées1",{"Date Debut Preparation Execution", "Date Fin Preparation Execution"}),
#"Lignes filtrées2" = Table.SelectRows(#"Colonnes supprimées2", each ([Operation OF] <> 5)),
#"Colonnes supprimées3" = Table.RemoveColumns(#"Lignes filtrées2",{"Quantite Livree", "Quantite Reste ALivrer", "Date Livraison Requise", "Date Livraison Planifiee", "Date Debut OF"})
in
#"Colonnes supprimées3"let
Source = Excel.Workbook(File.Contents("T:\Env_PRD\LV\Extraction\LOG20_Charge Passee_LVC010_LVC073.xlsx"), null, true),
#"LOG20_Charge Passee_Sheet" = Source{[Item="LOG20_Charge Passee",Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(#"LOG20_Charge Passee_Sheet", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Date Transaction", type datetime}, {"Code Employe", type text}, {"Nom Employe", type any}, {"Temps Main Oeuvre", Int64.Type}, {"Heure Main Oeuvre Allouees OF", type number}, {"Centre Charge", type text}, {"Description Centre Charge", type text}, {"Heures Machines", type number}, {"Annee Fabrication", Int64.Type}, {"Semaine Fabrication", Int64.Type}, {"Date Debut Preparation Execution", type datetime}, {"Date Fin Preparation Execution", type datetime}, {"Ordre Fabrication", type text}, {"Operation OF", Int64.Type}, {"Statut Operation", type text}, {"Tache", type text}, {"Description Tache", type text}, {"Projet", type text}, {"Article", type text}, {"Description Article", type text}, {"Planificateur", type text}, {"Nom Planificateur", type text}, {"Famille Planificateur", type text}, {"Priorite", Int64.Type}, {"Machine", type text}, {"Heure Main Oeuvre Allouees", type number}, {"Heure Main Oeuvre Passees", type number}, {"Temps Restant", Int64.Type}, {"Temps Restant Calcule", Int64.Type}, {"Quantite Planifiee", Int64.Type}, {"Groupe Ordre SFC", type text}, {"Temps Preparation Moyen", Int64.Type}, {"Quantite Livree", Int64.Type}, {"Quantite Reste ALivrer", Int64.Type}, {"Quantite Cumulee Achevee", Int64.Type}, {"Quantite Cumulee Rejetee", Int64.Type}, {"Type Produit", type text}, {"Retard Date Debut Preparation", type text}, {"Retard Date Fin Preparation", type text}, {"Date Livraison Requise", type datetime}, {"Date Livraison Planifiee", type datetime}, {"Statut OF", type text}, {"Retard Date Livraison Requise", type text}, {"Date Achevement OF", type datetime}, {"Date Achevement Operation", type datetime}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Statut OF] = "Actif")),
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Code Employe", "Nom Employe", "Temps Main Oeuvre", "Heure Main Oeuvre Allouees OF", "Heures Machines", "Annee Fabrication", "Semaine Fabrication", "Priorite", "Machine", "Heure Main Oeuvre Allouees", "Heure Main Oeuvre Passees", "Temps Restant", "Temps Restant Calcule", "Groupe Ordre SFC", "Temps Preparation Moyen", "Quantite Cumulee Achevee", "Quantite Cumulee Rejetee", "Type Produit", "Retard Date Debut Preparation", "Retard Date Fin Preparation", "Retard Date Livraison Requise"}),
#"Colonnes permutées" = Table.ReorderColumns(#"Colonnes supprimées",{"Ordre Fabrication", "Date Transaction", "Centre Charge", "Description Centre Charge", "Date Debut Preparation Execution", "Date Fin Preparation Execution", "Operation OF", "Statut Operation", "Tache", "Description Tache", "Projet", "Article", "Description Article", "Planificateur", "Nom Planificateur", "Famille Planificateur", "Quantite Planifiee", "Quantite Livree", "Quantite Reste ALivrer", "Date Livraison Requise", "Date Livraison Planifiee", "Statut OF", "Date Achevement OF", "Date Achevement Operation"}),
#"Lignes triées" = Table.Sort(#"Colonnes permutées",{{"Ordre Fabrication", Order.Ascending}}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Lignes triées",{"Projet", "Famille Planificateur"}),
#"Lignes filtrées1" = Table.SelectRows(#"Colonnes supprimées1", each true),
#"Lignes groupées" = Table.Group(#"Lignes filtrées1", {"Ordre Fabrication"}, {{"Opération OF", each List.Max([Operation OF]), type number}, {"Date achevement", each List.Max([Date Achevement Operation]), type datetime}})
in
#"Lignes groupées"let
Source = Table.NestedJoin(#"LOG19BIS_Gestion de la Charge",{"Ordre Fabrication"},#"LOG20_Charge Passee",{"Ordre Fabrication"},"LOG20_Charge Passee",JoinKind.LeftOuter),
#"LOG20_Charge Passee développé" = Table.ExpandTableColumn(Source, "LOG20_Charge Passee", {"Ordre Fabrication"}, {"LOG20_Charge Passee.Ordre Fabrication"}),
#"Lignes filtrées" = Table.SelectRows(#"LOG20_Charge Passee développé", each ([LOG20_Charge Passee.Ordre Fabrication] <> null)),
#"Requêtes fusionnées" = Table.NestedJoin(#"Lignes filtrées",{"Ordre Fabrication"},#"LOG20_Charge Passee",{"Ordre Fabrication"},"LOG20_Charge Passee",JoinKind.LeftOuter),
#"LOG20_Charge Passee développé1" = Table.ExpandTableColumn(#"Requêtes fusionnées", "LOG20_Charge Passee", {"Date achevement"}, {"LOG20_Charge Passee.Date achevement"}),
#"Lignes triées" = Table.Sort(#"LOG20_Charge Passee développé1",{{"LOG20_Charge Passee.Date achevement", Order.Ascending}})
in
#"Lignes triées"Encore une fois, le fichier fonctionne parfaitement, je souhaite juste réduire le temps d'ouverture du fichier.
Baroute, je ne sais pas où trouver la mémoire alloué à PowerQuery.
Ni comment modifier le niveau de confidentialité.
Peux-tu m'éclérer ?
Bonjour à tous de nouveau !
Je ne peux pas partager le classeur même en l'allégeant.
Car vous n'auriez que les liaisons vers d'autre fichiers non accessible.
Il n'est pas interdit de "construire" les classeurs nécessaires à la requête....
Contraintes : Respect de la structure des classeurs et quelques données significatives
Hello,
C’est dans les options quand tu vas dans fichier puis options de requête puis chargement des données tu as deux blocs où il faut sélectionner la dernière option et dans Confidentialité tu as un bloc sur les niveaux de sécurité à ignorer (dernière option aussi). Et si tu fouilles tu vas retrouver une option où tu peux modifier la mémoire allouée. Augmente à 32000 ça devrait améliorer le temps d’exécution.
Pour le reste de tes requêtes je ne peux que te conseiller de regrouper les étapes de suppression de colonnes, enlever l’étape pour réordonner les colonnes, grouper les étapes de filtre et enlever l’étape de filtre « each true » qui ne sert à rien.
Après la volumétrie est ce qu’elle est donc forcément un peu de latence.
@+
Parfait Baroute ! ça fonctionne !!
j'ai d'autre question sur ce sujet. j'ouvre un tiquer adéquate ? ou je continu ici
Hey,
Si c’est un sujet en lien avec ce dernier, autant rester ici sinon si c’est une évolution oui il faut ouvrir un autre sujet
Ps
@+