Récupérer les données

Dépivoter un tableau croisé complexe

Le fichier source

Le tableau que nous allons intégrer (la feuille 1 du fichier Exemple Dépivoter.xlsx) donne le nombre trimestriel de commandes pour différents clients, de 2018-T1 à 2020-T2 :

Ce type de structure rend impossible toute analyse dans Power BI. Il est nécessaire de mettre ces données « à plat », et de se débarrasser des éléments superflus : totaux en ligne et en colonne, cellules fusionnées, et en-têtes de colonnes sur plusieurs lignes.

Notons aussi que ce tableau est évolutif : il est probable que le troisième et le quatrième trimestre s’ajouteront par la suite, et il est nécessaire d’anticiper leur apparition....

Objectif et intérêt de cette section

Le but des pages qui suivent est de proposer une découverte du langage qui se cache derrière Power Query, de mettre en perspective l’utilité d’apprendre ce langage, et de dérouler un nombre de cas concrets directement utiles.

Outre ces cas, vous constaterez que les manipulations que je vous propose par la suite (le web-scraping, l’agrégation, l’utilisation de paramètres) impliquent a minima de savoir lire ce code, et bien souvent de savoir le modifier. En revanche, l’objectif de cette section n’est pas d’acquérir une connaissance approfondie, permettant d’écrire intégralement le script, mais de savoir s’appuyer sur celui que génère automatiquement l’interface : l’utilisateur de Power Query qui saura utiliser intelligemment l’interface et le code maîtrisera un outil d’une redoutable efficacité.

Rappelons d’abord que Power Query est une UI (interface utilisateur) dont l’objectif est de générer simplement du code M afin de construire la requête qui sera ensuite - au moment où vous cliquez sur Fermer & appliquer - envoyée en partie ou en totalité à la source.

Comme toute UI, celle de Power Query se concentre sur les fonctionnalités les plus fréquentes, ainsi que sur les modalités les plus courantes de ces fonctionnalités. Power Query est un outil bien pensé, ces fonctionnalités courantes sauront satisfaire la plupart des besoins.

Mais une première remarque s’impose : selon les sources, 30 % à 50 % des possibilités du langage M ne sont pas proposées par l’interface de Power Query ! C’est une des raisons pour lesquelles connaître ce code peut être utile.

Voici une autre raison : de nombreux utilisateurs experts de Power Query ont maintenant suffisamment de recul pour pouvoir identifier des cas concrets où l’interface de Power Query n’est pas suffisante pour répondre au besoin. Ce chapitre vous propose une sélection de ces cas, entre autres :

Extraire un tableau

C’est l’exemple d’extraction le plus basique, et il sera donc traité très brièvement.

Si la page dont vous fournissez l’adresse présente explicitement un ou plusieurs tableaux, e connecteur web de Power BI va les identifier et vous n’aurez qu’à choisir dans la liste celui ou ceux qui vous intéressent.

Cette technique a déjà été décrite dans mon précédent ouvrage, j’en ferai donc juste un bref rappel :

Power BI analyse la page et vous propose l’ensemble des tableaux HTML (identifiés par le langage HTML) ou suggérés (par l’IA de Power BI).

Le reste de cette démarche consiste à opérer certaines transformations (lignes d’en-tête, dernière ligne, type des données) qui ne présentent aucune difficulté particulière.

Extraire une liste structurée implicite

Si le cas précédent illustre un cas simple, ce n’est pas le plus fréquent. Les données sont en effet souvent présentées sous forme de liste plutôt que de tableau : c’est le cas par exemple des résultats de recherche ou encore des listes de produits d’un catalogue....

Comprendre l’agrégation

Définition standard de l’agrégation

L’agrégation est une technique d’optimisation qui consiste à créer une nouvelle table (la table d’agrégat) en résumant une table source (la table détaillée ou table de détail).

Le grain, ou la granularité, est une indication du niveau de détail d’une table : la table source a un grain plus fin que la table agrégat. Par exemple, la première peut indiquer des transactions au niveau journalier pour un produit, et la table d’agrégat peut consolider les chiffres soit au niveau journalier pour une catégorie de produit, soit au niveau mensuel pour le produit, soit encore au niveau mensuel pour une catégorie de produit.

Il y a en effet de nombreuses façons d’agréger les données, et il est possible d’avoir plusieurs tables d’agrégats dans le modèle.

La table de détail possède un très grand nombre de lignes, alors que la table d’agrégat en possède beaucoup moins, selon un facteur qui peut varier considérablement (de 1 à 1000, 10 000, 100 000, etc.). Plus le nombre de lignes est réduit, plus l’accès aux données est rapide, et moins la table occupe de place dans le modèle.

C’est le besoin en reporting, ainsi que le public visé, qui déterminent le recours à un ou plusieurs agrégats. L’exemple typique veut que plus le destinataire occupe une position élevée (direction générale), plus la vision est globale (« gros » grain). Et en revanche, plus le destinataire est proche de l’opérationnel, plus le niveau est détaillé.

Définition étendue de l’agrégation

Je fais le choix, dans le cadre de cet ouvrage, d’étendre cette notion d’agrégat et d’y inclure deux autres cas de figure :

Pourquoi utiliser les paramètres de requête

L’utilisation la plus courante des paramètres de Power Query est la possibilité de changer rapidement de source de données : c’est très utile lorsqu’il s’agit de passer facilement d’un environnement de développement (test) à un environnement de production ; d’une base de données historisée à une base à l’instant t ; ou encore d’un fichier stocké en local à un fichier stocké sur le réseau. À condition, bien entendu, que les deux sources aient la même structure, ou du moins que les colonnes utilisées pour générer les visuels soient présentes et organisées de même manière dans les deux sources de données.

Mais il existe un deuxième type de paramètre de requête dont le but est de modifier, de manière simple, le filtre appliqué à une ou plusieurs colonnes (changer d’année, de produit, etc.). Je parlerai alors de paramètre-filtre.

L’effet de ce paramètre se manifeste au moment de l’envoi de la requête : un paramètre-filtre de requête est donc très différent d’un filtre de rapport (segment ou visuel), puisque le paramètre va réduire le volume de données ramené de la source au moment de l’actualisation du rapport.

Associé à un modèle de document (fichier .pbit), il permet de maîtriser la manière dont l’utilisateur interroge la source de données, tout en lui laissant une certaine dose de liberté quant au périmètre de la requête.

Mettre en place un paramètre

La démarche suit trois étapes :