Récupérer les données

Rappels sur l’accès aux données

Dans cette brève introduction, nous allons revoir les notions basiques et quelques évidences de la phase de requête. Pour plus de détails sur ces différents points, je vous renvoie à mon précédent ouvrage, Power BI Desktop, De l’analyse de données au reporting, paru aux éditions ENI en juillet 2019.

Je vous recommande toutefois de prendre le temps de lire les quelques lignes qui suivent, afin de poser les bases pour la suite du chapitre.

La phase de requêtes comprend trois aspects, la (ou les) connexion(s), le choix des données, et l’enrichissement de la requête envoyée à la source, auxquels nous pouvons aussi décider d’ajouter la phase de mise en place du modèle de données (mise en place des relations, disposition graphique des tables, organisation des champs dans des tables et des dossiers, etc.).

Dans le détail, ces différents points proposent de nombreuses options et subtilités. Il n’est pas dans mon propos de les développer ici, mais simplement d’en faire un rappel rapide (pour le détail, voir le chapitre Mettre en place le modèle de données).

Avant toute chose, rappelons qu’une partie de la phase de requête a lieu avant que celle-ci ne soit envoyée à la source - autrement dit, qu’une partie des actions d’enrichissement de la requête a pour objet d’optimiser celle-ci, et de déporter autant que faire se peut le travail sur la source, pour éviter à Power BI de le faire en local (poste utilisateur).

Toute une partie de la requête est donc prise en charge par la machine où se trouve la source de données : nous parlons alors de « pliage » de requête pour désigner la part de la requête « traduite dans la langue » de la source.
Une partie de la requête ne peut être pliée, le travail est alors effectué en local (c’est également le cas pour la requête sur des fichiers locaux).
Il n’est pas toujours évident de savoir jusqu’où la requête est « pliée ».

Pour ce qui concerne la connexion :

La connexion « classique »...

Précisions sur la version du logiciel utilisée et les options activées

Une fois Power BI Desktop installé, un cycle de mises à jour fréquentes est enclenché. Ces mises à jour sont installées automatiquement, ou à la demande, ou encore pilotées par l’équipe informatique de manière centralisée, en fonction du type d’installation.

Cet ouvrage a été commencé avec la version de Mars 2020 et terminé avec la version de Mai 2020.

Par ailleurs, l’évolution de l’outil repose sur la mise à disposition de fonctionnalités qui ont d’abord un statut dit de préversion, avant d’obtenir celui de General Availability (c’est-à-dire de statut validé).

L’activation d’une fonctionnalité en préversion relève de votre choix, et se configure dans le menu déroulant Fichiers, puis Options et paramètres et enfin Options. Dans la rubrique Fonctionnalités en préversion, cochez ou décochez selon votre souhait.

Dans le cadre de la rédaction de cet ouvrage, et sur la base de la version de mars 2020, voici les options choisies :

Cas complexes d’import de fichier

Power BI fonctionne avant tout sur un modèle de données tabulaire structuré, à savoir, et pour simplifier, des tableaux composés de colonnes bien séparées avec en-têtes sur la ligne supérieure, et aucun champ récapitulatif (sous-total, total) - ou encore les colonnes d’une table. C’est le cas pour un fichier Excel simple ou pour une base de données.

Mais Power BI est aussi capable d’interroger des structures plus complexes, des données généralement moins « préparées » et moins propices à une intégration dans le modèle tabulaire.

Il n’est ainsi pas rare d’avoir à intégrer dans Power BI un tableau croisé (le fameux TCD) dont les en-têtes de colonnes ou de lignes présentent une structure complexe, par exemple des champs fusionnés, des totaux ou des en-têtes sur plusieurs lignes.

Un autre cas de figure intéressant est celui d’une structure empilée, où les données se présentent sur une seule colonne (ça peut être le cas dans des fichiers TXT ou CSV, ou encore sur des données récupérées sur internet).

Dépivoter un tableau croisé complexe

Le fichier source

Il est absolument essentiel et incontournable, lors de l’utilisation de Power Query, de connaître ses données, leur structure et leur contenu (entre autres). C’est pourquoi je présente en préambule à ce chapitre (et à d’autres) 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....

Éléments fondamentaux du langage M

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 :

la création d’une fonction personnalisée pour générer un calcul,
la création...

Importer des données web : le web-scraping

L’extraction de données issues du Web a pris énormément d’ampleur, et Power BI accompagne ce mouvement, en proposant quatre possibilités pour récupérer l’information, de la plus simple à celle qui va vous amener à travailler le code de la requête, en passant par l’aide qu’apporte l’intelligence artificielle de Power BI.

L’ensemble des techniques exposées dans cette section sont disponibles dans le fichier chap1-5_web_extractions.pbix que vous trouverez dans Fichiers exemples > chapitre 1.

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 :

Ouvrez Power BI et, dans l’onglet Accueil - groupe Données, cliquez sur le bouton Obtenir les données puis choisissez Web.

Saisissez ou collez l’adresse https://fr.wikipedia.org/wiki/Population_mondiale dans la fenêtre, puis validez.

Utilisez l’accès anonyme et cliquez sur Se connecter.

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).

Sélectionnez, par exemple, Évolution depuis 1950 et cliquez sur Transformer les données.

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....

Agréger les tables

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 :

Le premier consiste, lors de l’actualisation du rapport, à précalculer un indicateur (montant, quantité, etc.) et à le stocker dans une table autre que la table de détail. Par exemple, calculer le montant de la facture dans la table Facture (table agrégée)...

Utiliser les paramètres pour dynamiser la requête

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.

Par utilisateur, j’entends la personne qui manipule le fichier ; ça peut donc bien entendu être le créateur du rapport lui-même. Le paramètre est une technique permettant d’agir sur la requête sans avoir à chaque fois à ouvrir l’éditeur Power Query.

Mettre en place un paramètre

La démarche suit trois étapes :

Création du paramètre.
Modification du code M de la requête ou utilisation de l’interface pour ajouter le paramètre à la requête....

Comprendre l’actualisation incrémentielle

Lors de l’actualisation d’un rapport, l’intégralité des données présentes dans le rapport (E) est effacée et remplacée par l’intégralité des données présentes dans la source, c’est-à-dire l’ensemble E (dont certains enregistrements ont pu être modifiés) plus les nouveaux enregistrements (apparus depuis la précédente actualisation).

C’est ce remplacement complet du jeu de données qui peut, à mesure qu’augmente le nombre d’enregistrements dans la source, allonger la durée d’actualisation du rapport.

Il est possible de mettre en place des stratégies d’actualisation pour agir sur ce point : découper une grande table en plusieurs sections et n’actualiser que la dernière section, en prenant en compte la possibilité que les anciens enregistrements puissent être modifiés. Imaginez par exemple une très grande table dans laquelle sont stockés les passages en caisse pour un hypermarché. Les transactions, une fois enregistrées, ne changent pas, à moins qu’un client ne ramène un produit. Si la politique de retour est limitée à 7 jours, il est possible, moyennant quelques contorsions de la requête, de ne récupérer qu’une partie des enregistrements (après le 7 de chaque mois, uniquement ceux du mois en cours ; avant le 7, ceux du mois en cours et ceux du mois précédent).

Le principe ici est de découper la requête en fonction de la période, et de n’inclure dans l’actualisation que la dernière et éventuellement, l’avant-dernière section de la table :