Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Power Query et M
  3. Exploiter la puissance de Power Query
Extrait - Power Query et M Extraire et préparer les données en vue de leur exploitation dans Excel ou Power BI
Extraits du livre
Power Query et M Extraire et préparer les données en vue de leur exploitation dans Excel ou Power BI
9 avis
Revenir à la page d'achat du livre

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.

En ce qui concerne les fichiers exemples que nous allons utiliser, le point de départ de ce nouveau chapitre est l’état final des fichiers correspondant du chapitre Nettoyer et transformer les données. Les fichiers sourceXX_final.pbix que vous trouvez dans le répertoire chapitre3 ont été copiés et renommés sourceXX_allerplusloin.pbix. Vous les trouverez dans le sous-répertoire...

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 :

images/04sob001.png

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.

 Lancez Power BI et connectez-vous à la source source16.xlsx.

 Cliquez sur Transformer les données pour ouvrir Power Query.

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 :

images/04sob010.png

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

 Cliquez sur Éditeur avancé dans le ruban Accueil.

 Ajoutez une ligne après la ligne 2 (touche Entrée) et saisissez le code suivant :

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.

Plus précisément...

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 :

images/04sob018.png

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 :

images/04sob019.png

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 :

images/04sob020.png

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 :

images/04sob039.png
images/04sob040.png

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.

images/04sob041.png

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.

Avec un nombre de termes de recherche limité

Le principe ici est de créer une colonne conditionnelle et d’y vérifier si le champ contient l’un des termes recherchés. Dans l’affirmative, la colonne conditionnelle peut contenir soit le terme recherché lui-même, soit un code (1 si le terme est trouvé...

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 (rapport de détail ou tableau de bord ?) qui détermine le recours à un ou plusieurs agrégats. Dans un tableau de bord, la vision est globale (« gros » grain). Et en revanche, dans un rapport, 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) à partir des montants de la table des lignes de facture (table détaillée). Ou encore, ajouter une colonne dans...