Contact: 514-605-7112 / info@lecfomasque.com
Contact: 514-605-7112 / info@lecfomasque.com

Power Query: Améliorer la performance de vos requêtes

Publié le : 17 décembre 2015

Cet article fait suite à l’article Power Pivot: Améliorer la performance des modèles de données. Les problèmes de performance que vous éprouvez avec les add-ins Power BI dans Excel peuvent être rattachés à Power Pivot, mais ils peuvent également être causés par Power Query. Cet article vise donc à mettre en lumière quelques pistes de solution pour améliorer la performance de vos requêtes dans Power Query. Mais avant toute chose, sachez que les dernières versions de Power Query offrent une performance de loin supérieure aux versions précédentes. Assurez-vous donc d’utiliser la plus récente version de Power Query.

 

Spécifications et limites de Power Query

Avant toute chose, il serait sans doute judicieux que vous consultiez l’article suivant pour connaître les spécifications et les limites de Power Query: Power Query Specifications and Limits.

 

Optimiser l’importation de données dans Power Query

Il faut comprendre que lorsque vous vous connectez à une source de données à partir de Power Query et que vous effectuez ensuite des transformations sur cette source de données, Excel importe d’abord l’ensemble des données de la source de données et effectue ensuite les transformations et ce, même si vous n’avez besoin que de quelques colonnes ou lignes spécifiques. Si vous utilisez Power Query pour supprimer des lignes et/ou des colonnes, vous n’allégez pas l’action de départ, qui est d’importer l’ensemble des données de la source de données.

Encore une fois, si vous pouviez vous connecter à une source de données qui ne comprend que les données dont vous avez besoin, une vue par exemple, ce serait votre meilleure option.

Sinon, selon la source de vos données, vous pourrez utiliser quelques techniques pour éviter d’importer l’ensemble des données.

 

Si votre source de données est un serveur SQL, vous pourrez alors rédiger une requête SQL en utilisant l’espace prévu à cet effet dans Power Query.

Power Query requête SQL

Évidemment, vous pouvez également utiliser le code M et le modifier en conséquence. Toutefois, il y a de fortes chances pour que vos requêtes sur un serveur SQL profitent du Query Folding (expliqué plus bas dans cet article). Dans ce cas, vous ne gagnerez pas nécessairement en performance lorsque vous modifierez le code M.

Voici un exemple de modification de code M.

D’abord, voici une requête régulière où on a créé une connexion àla table FactInternetSales de la BD AdvendureWorksDW2014 sur le serveur Sophie et où on a ensuite filtré la table sur 3 clés de produits manuellement. Donc ici, on a d’abord importé l’ensemble des données de la table et ensuite apposé un filtre.

3productkeysFilter

 

Ensuite, voici une requête où on a modifié le code pour aller directement chercher les lignes de la table qui correspondent aux 3 clés de produits, sans avoir eu à importer d’abord l’ensemble des données de la table.

3productkeysComdeM

 

Si votre source de données est un cube tabulaire ou multidimensionnel sur SSAS, vous pourrez alors choisir vos colonnes (mais pas vos lignes).

Power Query on SSAS cube

 

Si votre source de données est un lien OData feed, le OData REST API permet d’effectuer des filtres avant l’importation des données, à partir du lien URL. L’article When Using Power Query to Retrieve Dynamics CRM vous présente un exemple concret. Au final, vous devez rédiger un URL qui ressemble à ceci:

https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunitySet()?$select=c5_Date,column2,column3,column4,column5,column5,column6,column7,column8,column9,column10,&$filter=(c5_Date gt DateTime’2014-01-01T00:00:00′)&$orderby=c5_Date desc

Vous pouvez également télécharger le OData query designer pour vous aider à rédiger ce type d’URL.

ODataQueryDesigner

 

Ne pas dupliquer les connexions

En vous référant à l’article Power Query : Faire pointer vos requêtes vers une nouvelle source de données, vous verrez qu’il existe plusieurs façons de centraliser les informations de connexion. Ceci est pratique puisque si la source change d’endroit, vous n’aurez à modifier les informations de connexion qu’à un seul emplacement dans votre fichier. Mais c’est aussi intéressant parce que ça évite de dupliquer les connexions et donc, de ralentir inutilement le déroulement de vos requêtes.

 

Optimiser la performance des requêtes

Il existe quelques façons d’optimiser la performance de vos requêtes dans Power Query. En voici quelques-unes:

 

1.Tirer profit du Query Folding

Le Query Folding est un phénomène très peu documenté, mais voici ce que nous en savons jusqu’à maintenant.

  • Query Folding veut dire que le serveur source exécute lui-même des transformations demandées par PowerQuery, ce qui devrait en améliorer grandement la performance.
  • Sources qui permettent le Query Folding :
    1. Sources sur des serveurs de bases de données (SQL Server, Oracle, …);
    2. AzureMarketPlace (basé sur SQL Server);
    3. OData sources;
    4. ActiveDirectory (je ne sais pas ce que cela implique…);
    5. Exchange (je ne sais pas c’est quoi…);
    6. Liste de fichiers ou des répertoires (est-ce vraiment utile?…)
    7. AUCUNE LISTE OFFICIELLE N’EXISTE!
  • Le Query Folding se produit selon les circonstances, les sources et les transformations impliquées. L’usager n’a pas vraiment de contrôle…
  • Transformations supportées par Query Folding :
    1. Filtrage de lignes et de colonnes
    2. Opérations de type JOIN
    3. Opérations de type AGGREGATE ou GROUP BY
    4. Transformations de type Pivot ou Unpivot
    5. Calculs numériques
    6. Changement de types des colonnes
    7. Transformation simples comme UPPER
    8. AUCUNE LISTE OFFICIELLE n’existe!
  • Ce qui empêche le Query Folding :
    1. Le Query Folding s’arrête, le cas échéant, dès qu’une condition adverse est rencontrée, comme :
      1. Utilisation de commandes ou de filtres qui n’ont pas d’équivalents dans le système source (par exemple, Remove last 6 rows, filter on ThisMonth, …)
      2. Fonction créée par l’usager – peut être incompatible avec Query Folding
      3. Utilisation de la fonction BUFFER de PowerQuery (cela force le chargement de la source dans PowerQuery, donc pas de possibilité de Query folding)
      4. Certains paramètres de Privacy Level sont incompatibles avec Query folding
      5. Filtre afin d’enlever les lignes avec erreurs
      6. Utilisation de ses propres requêtes SQL personnalisées

Donc, on peut, dans PowerQuery, mettre les transformations qui ont le plus de chances d’utiliser le Query folding en premier, afin de maximiser le gain de performance de Query folding. Le Query folding s’arrête dès qu’une transformation ne peut être interprétée par Query folding, on va donc garder ces transformations ne supportant pas Query Folding pour la fin…

Il est toutefois inutile de faire ces entourloupes si la source ne supporte pas Query Folding (flat files, CSV, texte, Excel, Access, …).

Pour monitorer vos requêtes et comprendre si elles bénéficient ou non du Query Folding, vous pouvez utiliser l’outil SQL Server Profiler.

 

2.Utiliser les options Fast Data Load et Fast Combine

Vous pouvez modifier les paramètres des options définies par défaut dans Power Query, en vous rendant dans la section Query Options.

D’abord, sous Data Load, vous pouvez utiliser les options de téléchargement par défaut ou préciser ce que vous préférez. Vous avez notamment l’option de cocher la case Fast Data Load. On explique que cette option permet de charger les requêtes plus rapidement, mais que, ce faisant, il se pourrait qu’Excel soit hors d’usage pour toute la période de téléchargement/rafraîchissement.

Fast Data Load

 

Une autre option disponible est le Fast Combine. Lorsque vous cochez cette case, vous améliorez la performance de vos requêtes, mais celles-ci ignorent alors le niveau de confidentialité que vous avez attribué à vos sources de données. Donc, si vous travaillez avec des données sensibles, cette option n’est pas intéressante pour vous. Autrement, faites-en le test!

Fast Combine Privacy Level

 

3.Utiliser la fonction Buffer

Il existe certaines fonctions dans le langage M, comme List.Buffer, Table.Buffer et Binary.Buffer qui permettent d’améliorer la performance d’une requête, en enregistrant en mémoire les résultats d’une opération (et donc, en évitant de la répéter à chaque mise à jour). Chris Webb en fait une bonne démonstration dans son article Improving Power Query Calculation Performance With List.Buffer(). Toutefois, cet article indique qu’en utilisant la requête ci-dessous, on échappe au Query Folding, qui serait sans doute plus performant. Il faut se rappeler, par contre, que le Query Folding ne s’effectue pas sur toutes les sources de données. Donc, si votre source de données ne supporte pas le Query Folding, la fonction Buffer pourrait vous être utile.

Buffer

 

CFO-Masque_Formations-en-ligne_FB

Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l’ensemble des formations.

 

Découvrez quelles formations vous conviennent

 

smarchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse S. Minne ,

    Bonjour Sophie,

    Est ce que le fait d’appliquer un filtre sur un critère provenant d’un tableau du classeur interrompt le Query Folding aussi ?

    Merci

  • réponse S. Minne ,

    Chose faite…Merci 😉

    • réponse URCA ,

      Bonjour, Je suis débutante en Power BI et je voulais savoir comment puis je supprimer des lignes avec des critères bien déterminées sous power query , j’ai essayé ceci mais ça n’a pas marché :

      # »Lignes filtrées » = Table.SelectRows(# »Type modifié », each ([ARKTSOC] « 999 »)),
      # »DEleteit » = Tables.RemoveRows(# »Lignes filtrées »)
      in
      # »Lignes filtrées »

      et ceci :
      = Table.RemoveRows(ARTICLE, each ([ARKTSOC] # »Code-Soc »))
      Merci pour votre aide et
      Cordialement

      • réponse URCA ,

        Oui , effectivement c’est un problèmes de doublons et ce que je veux faire c’est supprimer les lignes en double que je connais dèja par une requête , j’ai essayé de le faire sur la table mais les doubles restent toujours

        • réponse Sophie Marchand ,

          Bonjour,

          Il y a une option dans Power Query qui s’appelle justement Supprimer les doublons… = Table.Distinct(Table, Colonne).

          Au plaisir,

          Sophie

        • réponse URCA ,

          Table.RemoveRows(ARTICLE , [code_soc] # »Soc-Param »)
          mais ça ne marche pas

          Laisser un commentaire