Optimisation d'une requête générée par l'éditeur Power Query

Le 06/05/2022 à 01:20

Bonjour,

J'utilise l'éditeur Power Query pour lier deux tables (onglets) à partir d'un champ identique (clé). Tout fonctionne. Il m' arrive d'ajouter ou de supprimer des colonnes dans l'onglet qui contient les données liées via l'éditeur avancé. J'ai remarqué aussi que certains champs de type date dans une table source n'avaient pas ce même type dans la table résultante mais plutôt un type entier. Si dans PowerQuery je sélectionne la colonne et modifie le type en date, tout redevient normal.
J'ai regardé la requête générée automatique et à ma grande surprise, toutes les modifications manuelles que j'ai effectuées y sont consignées. Ayant constaté que cette requête était un peu lente (10 secondes pour s'exécuter) je me demande donc si ce n'est pas la présence de toutes ces instructions de modifications qui en sont la cause.
Pourriez-vous me donner votre avis sur ce problème notamment si c'est possible de le réécrire manuellement en enlevant ces instructions de modifications et l'imposer à l'éditeur Power Query?
Merci d'avance également pour toute proposition d'optimisation de cette requête. Je ne pourrai malheureusement pas mettre à disposition mon fichier car il s'agit d'un fichier d'entreprise.

let
    Source = Table.NestedJoin(TAB_PE,{"ID_SUJET"},TAB_CONTRIBUTEUR,{"ID SUJET"},"TAB_CONTRIBUTEUR",JoinKind.Inner),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Date de Réception", type date}, {"Date limite interne de Réponse", type datetime}}),
    #"TAB_CONTRIBUTEUR développé" = Table.ExpandTableColumn(#"Type modifié", "TAB_CONTRIBUTEUR", {"Thèmes ", "Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "Direction associée", "ID SUJET"}, {"TAB_CONTRIBUTEUR.Thèmes ", "TAB_CONTRIBUTEUR.Sous-thèmes", "TAB_CONTRIBUTEUR.Contributeurs principaux", "TAB_CONTRIBUTEUR.Contributeurs auxiliaires", "TAB_CONTRIBUTEUR.Chefs de services en CC", "TAB_CONTRIBUTEUR.Direction associée", "TAB_CONTRIBUTEUR.ID SUJET"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"TAB_CONTRIBUTEUR développé",{"Clôture", "Délai de réponse", "Destinataire demande", "Outcome", "Accès réponse #(lf)à la PE", "Accès Outcome #(lf)de la PE", "Commentaires", "TAB_CONTRIBUTEUR.Thèmes ", "TAB_CONTRIBUTEUR.Direction associée"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Date de relance", type date}, {"Date de Réponse des experts", type date}, {"Validation hiérarchique", type date}, {"Date de Réception", type date}, {"Date limite interne de Réponse", type date}}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Type modifié1",{"Date précédente#(lf)limite interne"}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Colonnes supprimées1",{{"Date limite externe de Réponse", type date}, {"Date de relance", type date}, {"Date de Réponse des experts", type date}, {"Validation hiérarchique", type date}, {"Date Envoi PE", type date}}),
    #"Colonnes supprimées2" = Table.RemoveColumns(#"Type modifié2",{"Heure DL#(lf)Externe", "Date de relance"})
in
    #"Colonnes supprimées2"
Le 06/05/2022 à 08:37

Bonjour à tous,

Vous devriez joindre un fichier anonymisé et représentatif !

J'ai regardé la requête générée automatique et à ma grande surprise, toutes les modifications manuelles que j'ai effectuées y sont consignées

C'est le principe même de Power Query. Enregistrer les différentes manipulations pour les "rejouer" ensuite.

Ayant constaté que cette requête était un peu lente (10 secondes pour s'exécuter) je me demande donc si ce n'est pas la présence de toutes ces instructions de modifications qui en sont la cause.

La vélocité d'une requête va dépendre, outre sa construction, de la volumétrie des données et des qualités de votre ordinateur.

Dans votre cas, plusieurs étapes triplées, alourdissent le traitement.

Le 07/05/2022 à 00:39

Bonsoir JFL,
merci pour votre réponse.
Effectivement un fichier exemple aurait pu aider mais l'anonymisation n'est pas simple dans la mesure ou je devrais même enlever les codes d'entreprise avant de poster le fichier dans le cadre du respect déontologique.
La solution fonctionne mais je me projette juste sur la maintenance pour la personne qui reprendra après moi. Si les modifications de quelques champs peuvent rendre le code SQL incompréhensible comme ça, je me demande si une macro qui fusionne deux onglets sur la base d'une colonne identique ne sera pas plus efficace à maintenir et même en termes de performance.

Le 07/05/2022 à 10:20
Bonjour à tous,

Il est toujours préférable de joindre un fichier représentatif. Cela évite bien des difficultés. Et créer un double, avec juste quelques données représentatives n'est pas chronophage.

Pas certain qu'un code VBA soit d'une maintenance plus aisée relativement à Power Query dont nombre de traitements s'effectuent via le ruban.
Le 07/05/2022 à 16:43

Bonjour JFL,

Vous avez raison, l'anonymisation n'est pas forcement chronophage et un fichier exemple est toujours mieux afin de mieux percevoir un problème particulier.
Après réflexion , oui vous avez également raison du choix de PowerQuery par rapport à un code VBA.
Je pouvais faire un fichier anonymisé mais dans la réalité mon vrai problème ne porte pas sur le contenu du fichier car il est des plus classiques. C'est la structure de la requête Power Query qui me pose des souci notamment le fait que cet outil conserve les traces des modifications afin de la rejouer à chaque fois, ce qui n'est pas des plus optimales. Je pensais qu'on pouvait simplement transformer la requête générée tout en gardant les mots clés Let et In mais en l'écrivant proprement comme sin on fait du SQL : Select table1.champ1, table2.champ3 inner joint T1.champ4=T2.champ4.....
Donc seuls les champs qui sont dans le Select sont affichés. Pas besoin donc de préciser à chaque fois que le champ x de la table y a été supprimé, modifié, etc comme fait Power Query. Voilà ce qui me dérange mais peut être que j'en demande trop et que Power Query n'est pas fait pour cela.
En conclusion : même en fournissant un fichier exemple cela ne change pas mon problème si on ne peut pas modifier manuellement la requête.
Encore merci

Le 07/05/2022 à 17:42

Bonjour

Quand on construit une requête via les menus on peut modifier le code dans la barre de formule pour optimiser. Exemples :

  • j'y reprends toujours les noms automatique des unpivot,
  • souvent y supprime la dernière colonne d'un SplitColumn,
  • y renomme les champs des Expand

Également dans les dialogues des étapes nommer correctement les colonnes ajoutées, les champs de regroupement ou, comme dans le cas de votre Expand, revenir dans la boite de dialogue et décocher les colonnes inutiles que vous supprimez dans l'étape d'après voire n étapes.

Ces 2 méthodes évitent des étapes inutiles ensuite.

Les typages de données successifs sont souvent inutiles.

On peut également en fin de requête réanalyser la méthode employée et parfois combiner 2 étapes ou en supprimer de peu utiles.

Rien n'empêche de modifier le code M dans l'éditeur...

Ce qui est fait via l'interface est comme avec l'enregistreur de macros VBA : comme un caméscope tout est filmé et le film rejoué à l'identique si on ne le coupe pas... POwerQuery est plus souple puisque toute correction modifie le code M.

PowerQuery peut utiliser une requête SQL si la source est une base de données (Acces, Oracle...)

Le 07/05/2022 à 20:18

Bonjour 78Chris

Merci beaucoup pour votre réponse très instructive qui va dans le sens de ce que je voulais faire mais la réalité est que (de mon point de vue) la requête Power Query est d'une lecture très difficile. Afin de vous rendre compte j'ai finalement fait une copie de mon fichier en supprimant toutes les données sauf 2 ligne fictives.

En joignant les deux tables par la clé ID-SUJET via PowerQuery j'obtiens dans le 3ème onglet RECAP_PE les champs suivants :

Numéro,Date de Réception,Date limite interne de Réponse,Date limite externe de Réponse,Origine de la Demande,Objet,Date de Réponse des experts,Validation hiérarchique,ID_SUJET,Date Envoi PE,TAB_CONTRIBUTEUR.Sous-thèmes,TAB_CONTRIBUTEUR.Contributeurs principaux,TAB_CONTRIBUTEUR.Contributeurs auxiliaires,TAB_CONTRIBUTEUR.Chefs de services en CC,TAB_CONTRIBUTEUR.ID SUJET

C'est ce que j'obtiens effectivement comme résultat mais puisque j'avais effectuer quelques modifications la requête à mon avis est devenu illisible. C'est le code que j'ai publié ci-dessous et que vous pourrez également le retrouver dans le fichier joint.

Question : Est-ce qu'on peut vraiment enlever manuellement les lignes Type modifié, Colonne modifiée, colonne supprimée, etc. qui apparaissent dans le code afin de le rende lisible et surtout pouvoir ajouter un champ sais que le code soit à nouveau pollué?

16tableausuivianonyme.xlsm (44.27 Ko)
Le 07/05/2022 à 20:56

Bonsoir à tous,

Une proposition ?

let
    Source = Table.NestedJoin(TAB_PE,{"ID_SUJET"},TAB_CONTRIBUTEUR,{"ID SUJET"},"TAB_CONTRIBUTEUR",JoinKind.Inner),
    tblCONTRIBUTEUR_dével = Table.ExpandTableColumn(Source, "TAB_CONTRIBUTEUR", {"Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "ID SUJET"}, {"TAB_CONTRIBUTEUR.Sous-thèmes", "TAB_CONTRIBUTEUR.Contributeurs principaux", "TAB_CONTRIBUTEUR.Contributeurs auxiliaires", "TAB_CONTRIBUTEUR.Chefs de services en CC", "TAB_CONTRIBUTEUR.ID SUJET"}),
    TypeDate = Table.TransformColumnTypes(tblCONTRIBUTEUR_dével,{{"Date de Réception", type date}, {"Date limite interne de Réponse", type date}, {"Date précédente#(lf)limite interne", type date}, {"Date limite externe de Réponse", type date}, {"Date de Réponse des experts", type date}, {"Validation hiérarchique", type date}, {"Clôture", type date}, {"Outcome", type date}}),
    ColSupp = Table.RemoveColumns(TypeDate,{"Clôture", "Délai de réponse", "Destinataire demande", "Outcome", "Accès réponse #(lf)à la PE", "Accès Outcome #(lf)de la PE", "Commentaires", "Date de relance", "Heure DL#(lf)Externe", "Date précédente#(lf)limite interne"})
in
    ColSupp
Le 07/05/2022 à 23:00

re à tous

Puisque les tables sont typées avant fusion je m'assurerais du typage de ces tables pour n'avoir que 3 étapes

let
    Source = Table.NestedJoin(TAB_PE,{"ID_SUJET"},TAB_CONTRIBUTEUR,{"ID SUJET"},"TAB_CONTRIBUTEUR",JoinKind.Inner),
    #"TAB_CONTRIBUTEUR développé" = Table.ExpandTableColumn(Source, "TAB_CONTRIBUTEUR", {"Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "ID SUJET"}, {"Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "ID SUJET"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"TAB_CONTRIBUTEUR développé",{"Clôture", "Délai de réponse", "Destinataire demande", "Outcome", "Accès réponse #(lf)à la PE", "Accès Outcome #(lf)de la PE", "Commentaires",  "Date précédente#(lf)limite interne", "Date de relance", "Heure DL#(lf)Externe"})
in
    #"Colonnes supprimées"

où je ne ne récupérerais pas les colonnes inutiles lors de l'Expand comme déjà suggéré.

ou bien je supprimerai le typage initial des 2 tables pour ne le faire qu'après nettoyage de la fusion

let
    Source = Table.NestedJoin(TAB_PE,{"ID_SUJET"},TAB_CONTRIBUTEUR,{"ID SUJET"},"TAB_CONTRIBUTEUR",JoinKind.Inner),
    #"TAB_CONTRIBUTEUR développé" = Table.ExpandTableColumn(Source, "TAB_CONTRIBUTEUR", {"Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "ID SUJET"}, {"Sous-thèmes", "Contributeurs principaux", "Contributeurs auxiliaires", "Chefs de services en CC", "ID SUJET"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"TAB_CONTRIBUTEUR développé",{"Clôture", "Délai de réponse", "Destinataire demande", "Outcome", "Accès réponse #(lf)à la PE", "Accès Outcome #(lf)de la PE", "Commentaires",  "Date précédente#(lf)limite interne", "Date de relance", "Heure DL#(lf)Externe"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Date de Réception", type datetime}, {"Date limite interne de Réponse", type datetime}, {"Date limite externe de Réponse", type datetime}, {"Date de Réponse des experts", type datetime}, {"Validation hiérarchique", type datetime}, {"Date Envoi PE", type datetime}, {"Numéro", type text}, {"Objet", type text}, {"ID_SUJET", type text}, {"Accès réponse exterieur", type text}, {"Sous-thèmes", type text}, {"Contributeurs principaux", type text}, {"Contributeurs auxiliaires", type text}, {"Chefs de services en CC", type text}, {"ID SUJET", type text}})
in
    #"Type modifié"

A noter que pour ma part, je ne récupère quasi jamais le nom de la table source en préfixe des colonnes issues de la fusion

Le 08/05/2022 à 20:57

Bonsoir JFL & 78Cris,

Merci beaucoup pour vos réponses. Si je modifie le typage datetime en date dans la proposition de 78Cris, la réponse de JFL et la deuxième réponse de 78Cris donnent le même résultat à l'exception du typage du champ Date Envoi.
La première réponse de 78Cris ne me convient pas car il n'ya pas de typage, ce qui met mes dates en type entier numérique.
Donc dans les 2 cas le résultat est instantané contrairement au presque 10 secondes de la requête initiale générée par PowerQuery.
J'ai donc l'embarras du choix même si j'avoue ne pas bien avoir compris l'idée de suppression de typage initial puis de le remettre après la fusion (rires)
Encore merci à tous les deux

Le 08/05/2022 à 22:45

Bonsoir à tous,

cheickna a écrit :

...... j'avoue ne pas bien avoir compris l'idée de suppression de typage initial puis de le remettre après la fusion

Les adeptes de Power Query désactivent l'option de typage automatique, préférant utiliser la fonctionnalité au moment choisi.

Le 08/05/2022 à 23:01

Merci JFL, je comprends mieux

Rechercher des sujets similaires à "optimisation requete generee editeur power query"