Exploiter la puissance de Power Query

Introduction

Le chapitre Acquérir les données de ce livre nous a donné une idée des vastes possibilités d’accès aux données à travers la bibliothèque des connecteurs : fichiers, bases de données, web, API, parmi d’autres. Le chapitre Nettoyer et transformer les données nous a ouvert les portes de l’interface graphique, et nous avons vu que le code M permet d’étendre encore les possibilités, d’accélérer les traitements, et de rendre la requête plus souple, capable de s’adapter à des modifications de la source.

Dans le présent chapitre, nous allons augmenter notre maîtrise de Power Query, travailler de manière plus organisée, rendre la requête plus dynamique encore, utiliser toute la puissance Power Query pour optimiser, tant du point de vue de la rapidité que du confort de travail, l’expérience de la création de rapport.

Organiser les requêtes et les étapes

Jusque là, nous ne nous sommes pas inquiétés de la façon dont sont présentées les requêtes ou du nom donné aux étapes.

Organiser et normaliser quelque peu ces éléments n’est pas strictement nécessaire, mais apporte un surcroît de clarté tant à la structure du processus de préparation de données, qu’à la lecture du code M, ce dernier point étant sans doute plus qu’accessoire.

Gérer les requêtes

Créer des répertoires pour regrouper les requêtes

Les requêtes issues directement de la source peuvent être les tables que vous importez dans Power BI, mais dans de nombreux cas, elles ne sont qu’une étape intermédiaire pour créer de nouvelles tables, par exemple par combinaison, fusion ou toute autre restructuration. 

Qui plus est, le volet Requêtes de Power Query contient aussi d’autres types d’éléments : des tables créées manuellement, des tables d’agrégation, des paramètres ou encore des fonctions (nous en verrons des exemples plus loin dans ce chapitre et dans le chapitre suivant).

En définitive, le volet Requêtes peut contenir beaucoup d’éléments temporaires (que je préfère appeler « techniques »), et des tables qui serviront effectivement pour le rapport.

Voyons à travers un exemple comment il est possible d’organiser ces deux types d’éléments pour plus de clarté.

Le fichier source11_allerplusloin.pbix contient quatre requêtes, et les trois premières tables sont combinées pour former la quatrième. C’est elle qui sera utilisée dans le rapport. 

Dans le précédent chapitre, nous avons vu qu’il est possible d’inclure une table dans l’actualisation du rapport, sans pour autant la charger. C’est le cas des tables France, Allemagne et Grande-Bretagne. Seule la table combinée FR-D-UK est chargée et utilisée dans le rapport :

Organiser les requêtes, c’est regrouper les trois premières dans un dossier, et mettre la quatrième dans un autre.

 Ouvrez le fichier source11_allerplusloin.pbix...

Récupérer le contexte de la requête

Le contexte d’une requête, c’est un ensemble d’informations qui ne sont pas utilisées dans le travail d’analyse, mais qui précisent le périmètre, les conditions d’extraction des données. Récupérer ce contexte peut revêtir une importance capitale pour analyser avec acuité les données affichées dans les rapports.

À travers deux exemples, nous allons voir comment le code M nous permet de récupérer ces informations.

Dans le premier exemple, nous allons récupérer la date d’extraction et le nom de la feuille Excel : en effet, cette information (le nom de la feuille, janvier) n’est pas présente dans la table :

L’ensemble des manipulations est effectué dans l’Éditeur avancé.

NomSource = Source{0}[Item], 

Cette ligne de code crée une variable NomSource qui stocke le nom du premier item de la source, à savoir le nom de la première feuille du fichier Excel.

Dynamiser la requête et travailler avec les paramètres

Travailler sur des périodes glissantes

Une période est dite glissante lorsqu’elle est calculée relativement à la date à laquelle est actualisé le rapport (elle prend donc comme point de référence « aujourd’hui ») : hier, cette semaine, le mois dernier, ou encore cette année.

Le principe est donc de filtrer une date (date de transaction, date de commande, etc.). L’interface est très complète de ce point de vue, et rend possible tous les scénarios de requête. 

Dans la mesure où je ne sais pas à quelle date vous allez lire ce chapitre, vous n’y trouverez pas de manipulations, mais une description pas à pas des différentes méthodes.

Je me sers pour illustrer ces techniques d’un fichier où figure le cours de bourse d’une action (RLFSW) entre le 15 juin 2020 et le 11 juin 2021. Et je précise que cette section est écrite le 13 juin 2021.

Après traitement, la table se présente ainsi :

Le filtre présent à la droite du nom de la colonne Date offre un éventail de possibilités important. Il suffit de choisir l’une d’entre elles pour filtrer la table :

Les filtres de dates sont nombreux, et celles qui sont encadrées ci-dessus sont plus spécifiquement associées à la notion de date relative

Pour chaque unité temporelle du deuxième cadre, les options sont l’unité précédente, l’unité actuelle et l’unité prochaine (ex., le mois dernier, ce mois, le mois prochain).

L’opérateur Dans le précédent… permet d’extraire un nombre d’unités quelconque. Par exemple, pour récupérer les données des dix derniers jours, le filtre se présente ainsi :

Les unités de temps sont les mêmes que dans le deuxième cadre (année, trimestre, mois, semaine, jour, heure, minute, seconde).

Il est toujours instructif de regarder le code M généré par l’interface. Dans ce cas :

= Table.SelectRows(#"Type modifié1",  
each Date.IsInPreviousNDays([Date], 10)) 

Nous verrons dans la section suivante la création...

Entrer des données

En dehors de la connexion à une source de données, il est toujours possible d’ajouter une table manuellement dans Power Query, soit par saisie, soit par copier-coller, soit encore à l’aide d’un code M.

Les cas d’utilisation de ce type de technique sont nombreux, citons par exemple :

la table de paramètres utilisée dans les rapports (à ne pas confondre avec les paramètres de requête),

la table du temps créée en code M (plutôt qu’en DAX),

une table de termes pour effectuer une analyse textuelle (nous développons ce cas plus loin dans ce chapitre).

Entrer des données par copier-coller

C’est la plus simple des techniques : pour peu que vous ayez un fichier Excel, un fichier TXT, un tableau dans un document Word ou PDF, vous pouvez copier-coller les données directement dans une table (en dehors du fichier Excel, il faut prévoir une ou deux étapes de transformations).

Où trouver la fonctionnalité ?

Vous la trouverez à trois endroits : dans Power BI, sur la page d’accueil Coller les données dans une table vide ou dans le ruban Accueil - Entrer des données, dans Power Query dans le ruban Accueil - Entrer des données :

Copier-coller à partir d’Excel

C’est le cas le plus simple, car les données collées sont directement structurées en colonnes. 

Testons cette technique :

 Ouvrez le fichier source03.xlsx.

 Copiez quelques lignes de la feuille Janvier, y compris les en-têtes.

 Dans la Page 1 de Power BI, cliquez sur Coller les données dans une table vide.

 Collez les lignes dans la fenêtre Créer un tableau.

 Nommez la table Table Excel et cliquez sur Charger.

Dans cet exemple, les données ont été correctement typées lors du processus de chargement

Copier-coller à partir d’un fichier texte

Les trois autres cas impliquent le plus souvent de transformer les données.

Continuez dans le même fichier Power BI :

 Ouvrez le fichier source18.txt.

Attention à ne pas confondre avec le fichier source18.xlsx que nous avons créé et utilisé dans une section précédente consacrée aux paramètres.

 Copiez quelques lignes, y compris...

Analyser le texte avec un produit cartésien

Je dois à Gil Raviv les exemples montrés dans ce chapitre.

L’objectif est très spécifique, mais très puissant : il s’agit d’identifier dans un champ texte la présence d’un ou plusieurs termes. Si le terme est trouvé, un code est ajouté dans une colonne, pour être en mesure d’identifier les lignes intéressantes.

Les scénarios d’utilisation incluent l’analyse de commentaires sur un site web ou l’identification d’éléments dans une liste (ce que nous allons faire).

Il faut ici distinguer deux cas de figure : le nombre de termes recherchés est petit et fixe (peu de termes, toujours les mêmes), ou au contraire important et dynamique (plus de 5 termes par exemple, et de nouveaux termes s’ajoutent régulièrement, pendant que d’autres disparaissent).

Dans le premier cas, une colonne conditionnelle suffit. Dans le second, le recours à une technique de croisement entre deux tables, appelée produit cartésien, permet un traitement rapide et efficace.

Pour les deux cas, nous travaillerons avec le fichier source14_allerplusloin.pbix, qui contient une liste de titres d’ouvrages. Nous y cherchons la présence des termes power bi, power query, et dax.

Agréger les tables