Retour

Optimisez vos rapports Power BI avec Power Query : Guide complet pour réduire les temps de chargement et améliorer vos analyses

Temps de lecture : 9 minutes

Ce tutoriel a pour objectif de vous guider pas à pas dans l’utilisation de Power Query au sein de Power BI. Vous découvrirez les étapes essentielles pour accéder à cet outil, les raisons pour lesquelles il est indispensable dans le mode import, ainsi que les bonnes pratiques et fonctionnalités pour optimiser le traitement des données. À travers des exemples concrets, nous aborderons également les techniques pour simplifier et améliorer vos processus d’analyse, tout en évitant les pièges qui pourraient impacter la performance de vos rapports.

Accès sur Power BI

Power BI Desktop

Cliquez sur « transformer les données » pour accéder à l’éditeur power Query

Power BI service

Power Query est accessible sur Power BI service via la création et l’utilisation des dataflow gen1 ou gen2 (nécessite une capacité fabric)

Quand et pourquoi utilisez Power Query ?

Rappel sur les différents modes de connexion

Rappel : Power BI dispose deux modes de connexion aux données :

  • Le mode direct query
  • Le mode import

Power Query n’est efficace que si les données sont importées.
En mode import, Power BI copie les tables sélectionnées par l’utilisateur et les gardent en mémoire ce qui permet par la suite d’y effectuer diverses transformations sans impacter la table d’origine.

Pour quelle raison utiliser Power Query

En mode import, les données sont copiées et par conséquent la taille de la table copiées est la même que celle de la table d’origine.
Plus la table d’origine est lourde, plus la table copiée le sera également, et donc l’utilisateur se retrouve rapidement face à une des limites suivantes :

  • Limite de la taille du fichier .pbix dépasse 1GB, donc l’utilisateur doit passer de la licence gratuite ou Pro à Premium et/ou une capacité fabric sinon le rapport ne s’actualisera pas.
  • Chargement des données trop long et donc le risque de déclencher un time-out (temps limite de chargement des données) définis par la source de données est plus facile à déclencher, et par conséquent le rapport power BI ne se met pas à jour.  

Pour éviter d’atteindre une de ces limites, il est nécessaire de limiter le nombre de données importer, c’est le rôle principal de Power Query.

Via power Query, l’utilisateur peut :

  • Supprimer les colonnes et/ou données non indispensable à la création de l’étude power BI,
  • Modifier le format des colonnes et/ou données pour améliorer la visibilité des résultats du rapport,
  • Créer et Ajouter d’autres colonnes personnalisées,
  • Combiner les données de différentes tables provenant de différentes sources en une seule

Par la suite voyons chacun des différents cas et leur impact sur le temps de chargement des données dans le rapport power BI

Les fonctionnalités et méthodes pour réduire le temps de chargement des données

Supprimer les colonnes et/ou données non indispensable à la création de l’étude power BI

Dans des sources de données tel que CRM ou Business Central, les tables peuvent contenir entre 50 et 500 colonnes.
Dans l’exemple suivant :

La table opportunities(1) a de base (cliquez sur l’étape « Navigation » (2) pour accéder à la table d’origine) 406 colonnes (3).
Dans un rapport power BI, quand un utilisateur souhaite faire une étude, il utilise maximum 20 colonnes, donc il faut supprimer les autres.

Pour cela , l’utilisateur peut « choisir les colonnes » à garder :

L’utilisateur peut décocher (sélectionner toutes les colonnes) puis sélectionner que celles qui l’intéresse :

Dans notre nouvelle table opportunité (1), la table contient à présent 20 colonnes (3), les 386 autres colonnes ont été supprimés.
Remarque : Il est possible de rajouter des colonnes si l’utilisateur en a omis, ceci est possible en cliquant sur le rouage à côté de l’étape « Autres colonnes supprimées » (2)

La suppression de plusieurs colonnes réduit la taille de la table copiée ce qui permet un chargement plus rapide.

Un autre moyen de réduire le temps de chargement des données est de filtrer sur les données considérées comme importante.
Sur n’importe quel colonne, il y a une petite flèche, qui une fois déplier permet de sélectionner la (ou les) donnée(s) à garder, toutes lignes contenant des données non voulu seront supprimer.

Ici, dans notre colonne « statuscode », la colonne contient différent entier représentant un statut reconnaissable par l’utilisateur. En désélectionnant, l’un de ces entiers, il supprime de la table opportunité les lignes dont le statut n’est pas de ceux qu’il souhaite garder pour son analyse.

Exemple de cas d’utilisation: si une compagnie dispose de plusieurs magasins, l’utilisateur voudra sans doute connaître les données du magasin qu’il gère mais pas celles des autres.

Cependant, cette méthode sur le long terme, a moins d’impact que la suppression de colonne sur le temps de chargement

Créer et/ou ajouter des colonnes personnalisées

Sur power Query, l’utilisateur peut créer des colonnes personnalisées. Pour cela, la connaissance du langage M est nécessaire.

L’utilisateur cliquera sur « Colonne Personnalisée » (1) et une fenêtre s’ouvrira avec 2 champs modifiable :

  • le nom de la nouvelle colonne,
  • son expression en langage M à fournir.

L’image suivante montre une expression en langage M qui renvoie une colonne contenant les valeurs « en cours » et « finit » avec comme condition que si la valeur du « statuscode » dans le ligne en cours soit supérieur ou égale à 2 alors la ligne est considéré comme « en cours » sinon « finit »

Ce qui nous donne au final la nouvelle colonne « Personnalisé » qui se trouve à l’extrême droite de la table.

La création de colonnes calculé sur power Query a pour effet d’ajouter une colonne, et donc cette nouvelle colonne doit être crée et ajouté à chaque actualisation des données de la table d’origine et par conséquent cela a pour effet d’augmenter le temps de chargement des données.

Pour gagner du temps sur le chargement des données, il est préférable pour l’utilisateur de créer les colonnes calculées en langage DAX, qui s’applique après le chargement des données plutôt qu’en langage M (langage utilisé par power query).

La création de colonnes calculés est possible sous power Query mais a utilisé avec précaution vu son impact sur le temps de chargement des données.

Combiner les données de différentes tables provenant de différentes sources en une seule.

Power BI peut incorporer en un seul fichier, les données de différentes tables provenant de différentes sources.

Si les tables récupérer possèdent des caractéristiques communes comme le même nombre de colonnes et le même type de données,

L’utilisateur peut:

  • Fusionner les requêtes  (combiner deux tables en une seule, mais il faut que les deux tables disposent d’au moins une colonne avec des données commune pour être efficace).
  • Ajouter les requêtes (coller une table à la suite d’une autre, mais il faut que les 2 tables contiennent le même type de données et le même nombre de colonne).

Les deux fonctions sont accessible à l’extrême droite du volet accueil , Partie « combiner »

Fusionner les requêtes

La fusion de requêtes permet d’ajouter les colonnes d’une table à une autre en utilisant une ou plusieurs colonnes de chaque table comme référence.

Dans l’exemple suivant, l’utilisateur souhaitent ajouter les données de compte clients (accounts (2)) à celle des opportunités (opportunities(1)).

Les colonnes utilisées comme référence sont « parentaccountid_value » (3) et « accountid »(4). Les valeurs étant les même dans les deux colonnes, chaque ligne de la table des opportunités se verra attribuer la ligne correspondant au compte client

Les données clients sont ensuite ajoutés sous forme d’une colonne placé à l’extrême droite de la table des opportunités.

Attention : en cas non correspondance entre les colonnes de références, power Query appliquera à chaque ligne de la première table (dans l’exemple la table des oportunité ) tous les comptes clients i.e. que power query dupliquera chaque ligne de la première table autant de fois que le nombre de lignes de la deuxième table. Il y a un fort risque d’augmentation de la taille de la table à charger plus tard.

Ajouter les requêtes

L’ajout de requêtes ajoutent les données d’une table à la suite d’une autre.

Dans l’exemple suivant, l’utilisateur ajoute les données de la table opportunité de son 2ème crm (« opportunities crm 2 »)  à la table oppportunité  (« opportunities »)

Comme les deux tables ont la même architecture de colonnes, les données de (« opportunities crm 2 ») ont été ajouté à la suite de celles de la table opportunité (« opportunities »)

Attention cependant, les deux tables doivent avoir exactement le même nombre et les même noms de colonnes, sinon Power Query créera une(ou plusieurs) nouvelle(s) colonne(s) avec la moitié des lignes vides et l’autre moitié rempli de valeur de la table ajouté.

Ce qui agrandit la table et donc augmente plus sa taille.

Conclusion sur la combinaison de données

Ces méthodes sont pratiques mais reste à utiliser avec précaution car elles peuvent au moins doubler la taille d’un jeu de données et par conséquent au moins doubler le temps de chargement. Une solution DAX est de préférence à utiliser avant d’avoir recours à l’une de ces méthodes.

Laisser un commentaire

Il n'y a pas de commentaires pour le moment. Soyez le premier à participer !