Editions ENI Livres | Vidéos | e-Formations
La préparation des données avec Power Query
Objectif
Ce deuxième chapitre permet tout d’abord de vous familiariser et d’apporter des connaissances concrètes en matière de manipulation de données avec Power Query.
Tout au long de ce chapitre, des exemples d’application simples seront proposés afin d’illustrer les notions abordées.
Enfin, un cas pratique de mise en application inspiré d’une expérience professionnelle réelle, sera proposé.
Les fichiers nécessaires à la réalisation des exemples et du cas pratique sont disponibles en téléchargement.
Power Query, un outil pour nettoyer et manipuler les données
Power Query est un outil de Microsoft spécialisé dans la manipulation et la transformation de données.
Pour l’analyste, cette étape est probablement la plus importante car sans données structurées, rien n’est possible.
Cet outil est disponible dans Excel à partir sa version 2010 mais aussi dans Power BI Desktop, Microsoft Flow, Common Data Service et Analysis Service.
Lorsque Microsoft a commencé à intégrer des outils de business intelligence dans Excel, Power Query a été quelque peu négligé par les utilisateurs, ces derniers préférant la plupart du temps les possibilités de calcul spectaculaires du langage DAX ou bien les nouvelles fonctionnalités en matière de graphique.
C’est à mon avis une erreur, Power Query est probablement l’outil le plus utile depuis l’arrivée des tableaux croisés dynamiques en 1993.
Power Query est ce que l’on appelle un ETL (Extract, Transform, Load).
-
Extraction : se connecter à une ou plusieurs sources pour en extraire les données souhaitées.
-
Transformer : nettoyer, mettre en forme, agréger les données.
-
Load : importer les données, transformées ou non dans une location spécifique afin de les analyser.
De fait...
Première prise en main
L’objectif de cette section est de réaliser un premier tour d’horizon du fonctionnement de Power Query à travers un exemple simple. L’ensemble des fonctionnalités sera étudié plus en détail dans les sections suivantes.
Présentation des données
Un refuge pour animaux dispose d’un petit tableau de données comportant le nom, l’espèce, la date d’accueil et l’âge de plusieurs animaux.
Les données se présentent sous cette forme :
Vous retrouverez les données de cet exemple dans le fichier 1_animaux.xlsx.
Importation des données dans Power Query
Ouvrez le fichier 1_animaux.xlsx.
Sélectionnez une cellule contenue dans le tableau de données, par exemple la cellule A2.
Dans l’onglet Données du ruban, dans le groupe Récupérer et transformer des données, cliquez sur le bouton À partir de Tableau ou d’une Plage.
Une nouvelle fenêtre s’ouvre, il s’agit de l’éditeur Power Query.
Présentation de l’interface
Premier aperçu de l’éditeur Power Query
L’éditeur de requête se présente de la manière suivante :
Le ruban
En haut de l’écran se trouve un ruban ressemblant à celui qui se trouve dans Excel. Il est composé de cinq onglets :
Accueil
Cet onglet contient les outils de transformation les plus courants.
Il est possible entre autres de supprimer des colonnes, supprimer des lignes, trier, grouper, combiner les données, séparer des colonnes.
Transformer
Cet onglet comprend des outils qui permettent de modifier les colonnes existantes.
Ainsi, il est possible notamment de scinder ou grouper des colonnes, transposer du texte, d’utiliser des fonctions de calcul ou de manipulation des dates et heures.
Ajouter une colonne
Dans cet onglet se trouvent des outils permettant d’ajouter différents types de colonnes : colonnes conditionnelles, duplication, date et heure, etc.
Certains outils sont communs aux onglets Transformer et Ajouter une colonne comme par exemple les fonctions de calcul ou de manipulation des dates ou heures.
La barre de formule
La barre de formule contient du code M.
Si la barre de formule n’est pas affichée...
La connexion aux données
Power Query permet de connecter Excel à un large éventail de sources afin de filtrer, ajouter, transformer les données brutes.
Dans Excel 2019, 2021 et Excel Microsoft 365, l’outil d’importation des données Obtenir des données se trouve dans l’onglet Données du ruban au niveau du groupe Récupérer et transformer des données.
Dans cette section, nous allons voir comment se connecter grâce à Power Query à quelques-unes des sources de données les plus courantes.
Connexion à un fichier Excel
Créez un nouveau classeur Excel.
Dans l’onglet Données du ruban, dans le groupe Récupérer et transformer des données, cliquez sur le bouton Obtenir des données - À partir d’un fichier - À partir d’un classeur Excel.
La boîte de dialogue Importer des données apparaît à l’écran.
Sélectionnez le fichier Excel souhaité puis cliquez sur le bouton Importer.
Une boîte de dialogue appelée Navigateur apparaît.
Dans la version 2019 d’Excel, le bouton Transformer les données se nomme Modifier.
Elle permet de sélectionner un ou plusieurs éléments, feuilles, tables ou zones nommées contenus dans le fichier Excel.
Cliquez sur un des éléments du fichier. Un aperçu des données apparaît sur le côté droit de la boîte de dialogue.
Il est possible d’importer des tables et des zones nommées dans l’éditeur Power Query ; cependant, il n’est pas possible d’importer des graphiques, des tableaux croisés dynamique, des objets ou du code VBA.
À partir de la boîte de dialogue Navigateur, deux options sont possibles :
Charger directement les données sans effectuer de modifications
Cliquez sur le bouton Charger pour charger les données directement sous forme de table de données.
Les données sont alors importées sous forme de table dans le fichier Excel actif.
À droite de l’écran, le volet Requêtes et connexions indique qu’une requête a été mise en place.
Pour obtenir d’autres méthodes d’affichage des données, cliquez sur la flèche à droite du bouton Charger puis sur Charger dans.
La boîte de dialogue Importation de données apparaît à l’écran.
Réaliser des transformations de données
À partir de la boîte de dialogue Navigateur, cliquez sur le bouton Transformer les données (ou Modifier). L’éditeur Power Query s’ouvre :
Il sera alors possible d’effectuer des modifications sur la table avant de l’importer dans Excel.
Connexion à une base de données relationnelle : exemple avec Access
Dans l’onglet Données du ruban, dans le groupe Récupérer et transformer des données, cliquez sur le bouton Obtenir des données - À partir d’une base de données - À partir d’une base de données Microsoft Access.
La boîte de dialogue Importer des données apparaît à l’écran.
Sélectionnez le fichier Access souhaité puis cliquez sur le bouton Importer.
La base de données base_données_commercial.accdb est disponible dans les fichiers en téléchargement.
La boîte de dialogue Navigateur apparaît.
Elle permet de sélectionner une ou plusieurs des tables contenues dans la base de données Access.
En sélectionnant une des tables, un aperçu des données apparaît sur le côté droit de la boîte de dialogue.
Comme pour l’exemple précédent, il est possible de charger directement la ou les tables sélectionnées en cliquant sur le bouton Charger ou bien de modifier les données avant importation en cliquant sur le bouton Transformer les données.
Connexion à un dossier
Power Query permet d’importer une liste entière de fichiers contenus dans un dossier.
Cela s’avère particulièrement utile lorsqu’il s’agit de synthétiser en un document unique un ensemble de rapports divisés en plusieurs fichiers.
Attention cependant, si vous utilisez une Mise sous forme de tableau, les différentes tables doivent être nommées de manière identique.
Exemple : le service commercial remplit un tableau mensuel retraçant les ventes d’une entreprise.
En tant que chargé du reporting, vous souhaitez obtenir une table de données qui reprendra l’ensemble des ventes pour un trimestre.
Les données mensuelles sont organisées de la manière suivante (exemple pour le mois de janvier) :
Vous retrouverez les fichiers rapport_janvier.xlsx, rapport_fevrier.xlsx, rapport_mars.xlsx dans le dossier rapport mensuel disponible en téléchargement.
Importer les fichiers contenus dans le dossier
Téléchargez le dossier rapport mensuel sur votre ordinateur et ouvrez un document Excel vierge.
Dans l’onglet Données du ruban, dans le groupe Récupérer et transformer des données, cliquez sur le bouton Obtenir des données, sélectionnez À partir d’un fichier - À partir d’un dossier.
La boîte de dialogue Parcourir apparaît à l’écran.
Sélectionnez le dossier rapport mensuel.
Cliquez sur le bouton Ouvrir.
Une nouvelle boîte de dialogue s’affiche à l’écran :
Elle contient des informations sur l’ensemble des fichiers contenus dans le dossier.
Combiner les fichiers
Pour combiner les trois fichiers Excel contenus dans le dossier, cliquez sur le bouton Combiner.
Trois options sont possibles :
Combiner et transformer les données
Cliquez sur l’option Combiner et transformer les données.
La boîte de dialogue Combiner les fichiers s’affiche à l’écran.
Sélectionnez la table nommée donnees.
Un aperçu de la table apparaît à l’écran. Il est possible d’obtenir un aperçu des tables des autres fichiers à partir de la liste déroulante Exemple de fichier.
Cliquez sur le bouton OK.
L’éditeur Power Query s’ouvre, il contient les données de l’ensemble des trois fichiers Excel présents dans le dossier.
La colonne Source.Name a été automatiquement ajoutée par Power Query. Elle contient pour chacune des lignes, le nom du fichier d’origine des données.
Il est possible, à ce niveau, de transformer les données avant importation dans Excel.
Combiner et charger
Cliquez sur l’option Combiner et charger.
La boîte de dialogue Combiner les fichiers s’affiche à l’écran.
Cliquez sur le bouton OK.
Les données sont combinées et directement chargées sous forme de tableau classique dans Excel.
Combiner et charger dans
Sélectionnez l’option Combiner et charger dans.
La boîte de dialogue Combiner les fichiers s’affiche à l’écran.
Sélectionnez la table donnees.
Cliquez sur le bouton OK.
La boîte de dialogue Importation de données apparaît à l’écran :
Elle permet de charger les données dans Excel sous forme de tableau, de rapport de tableau croisé dynamique, de graphique croisé dynamique, de ne créer qu’une connexion aux données ou d’ajouter les données au modèle de données d’Excel.
Connexion à une table de données venant du Web
Une des utilisations les plus intéressantes de Power Query est de pouvoir se connecter à des données venant d’Internet.
Exemple : le site de la banque de France contient les taux de change quotidiens de l’euro par rapport à la plupart des devises mondiales.
Adresse de la page web : https://www.banque-france.fr/fr/statistiques/taux-et-cours/taux-de-change-parites-quotidiennes-2024-09-24
Le but de cet exemple est d’utiliser Power Query pour importer la table de données comportant les différents taux de change directement du site de la banque de France. Ainsi, une fois la connexion établie, il suffira de rafraîchir les données dans Excel pour obtenir les derniers taux en vigueur.
Créez un nouveau classeur Excel.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur À partir du web.
La boîte de dialogue À partir du web apparaît à l’écran.
Dans la zone de saisie URL, collez l’adresse de la page web.
Cliquez sur le bouton OK.
Power Query va rechercher les tables de données existantes sur la page web.
Une fois la recherche effectuée, la boîte de dialogue Navigateur apparaît à l’écran.
Au niveau des Options d’affichage, Power Query a reconnu une table HTML nommée Table 1.
Cliquez sur la table Table 1. Un aperçu des données est présenté sur la partie droite de la boîte de dialogue.
Comme précédemment, deux options sont possibles :
-
soit charger directement les données en cliquant sur le bouton Charger ;
-
soit Transformer les données grâce à l’éditeur de requête de Power Query.
La mise à jour des données
Une fois la connexion aux données effectuée, que se passe-t-il si le fichier source est modifié ?
Exemple : l’onglet source du fichier 3_personnel.xlsx contient un petit tableau retraçant le nom, l’âge et la ville de plusieurs personnes.
Vous retrouverez le fichier 3_personnel.xlsx dans les liens de téléchargement.
Ouvrez le fichier 3_personnel.xlsx.
Première étape : importation les données dans Power Query
Sélectionnez une cellule contenue dans le tableau de données...
Manipulation de données de base
Afin de se familiariser avec les différentes commandes de base de l’éditeur de requête de Power Query, nous allons faire quelques manipulations concrètes à l’aide d’un tableau de données contenu dans un fichier Excel.
Vous retrouverez les données de cet exemple dans le fichier 4_manipulation_base.xlsx.
Un formateur recense les participants inscrits à ses cours dans un tableau.
Les données se présentent de la manière suivante :
Le but de cet exercice est de transformer la structure de ce tableau afin de créer une table de données structurée, facilement analysable par un tableau croisé dynamique :
-
au niveau des en-têtes, uniquement et seulement les titres de colonnes,
-
pas de lignes, de colonnes et/ou de cellules vides.
En outre, dans un objectif de clarté et éventuellement pour faciliter d’éventuelles futures recherches, nous allons scinder la colonne Nom élève en deux colonnes afin d’y faire figurer distinctement le nom et le prénom des étudiants.
Le résultat final sera le suivant :
Importation des données dans Power Query
Ouvrez le fichier 4_manipulation_base.xlsx
Sélectionnez une cellule contenue dans le tableau de données. Par exemple la cellule B3.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur le bouton À partir de Tableau ou d’une Plage.
La boîte de dialogue Créer un tableau apparaît à l’écran :
Vérifiez que les données sélectionnées par Excel correspondent bien à la plage de cellules souhaitée.
Décochez la case Mon tableau comporte des en-têtes. En effet, la première ligne du tableau sélectionnée ne correspond pas aux en-têtes de colonnes.
Cliquez sur le bouton OK.
L’éditeur Power Query s’ouvre :
C’est à partir d’ici que nous allons pouvoir transformer les données.
Nommer le tableau
Dans le volet Paramètres d’une requête au niveau de la zone Nom, nommez le tableau Cours_design.
La requête sera par la suite identifiée par ce nom.
Supprimer...
Les outils de dates et heures
Présentation des outils
Power Query possède plusieurs outils intéressants pour travailler avec des dates et heures.
Ces outils se trouvent :
-
Dans l’onglet Transformer du ruban, au niveau du groupe Colonne Date et heure.
-
Dans l’onglet Ajouter une colonne du ruban, au niveau du groupe Date et heure de début.
La plupart du temps les opérations sur des dates et heures sont réalisées à partir du menu Ajouter une colonne pour créer de nouveaux champs plutôt que de transformer une colonne individuelle.
Application
Nous disposons d’un petit tableau Excel comportant les dates de naissance de plusieurs personnes.
Vous retrouverez les données de cet exemple dans le fichier 6_date.xlsx.
Le but de cet exercice est d’utiliser Power Query pour extraire l’année de naissance de la colonne Date de naissance puis de calculer l’âge de chacune des personnes.
Importation des données dans Power Query
Ouvrez le fichier 6_date.xlsx
Sélectionnez une cellule contenue dans le tableau de données, par exemple la cellule A2.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur le bouton À partir de Tableau ou d’une Plage.
L’éditeur Power Query s’ouvre.
Extraction des années de naissance...
Le profilage des données
Le profilage des données est un outil de Power Query qui permet à l’analyste d’obtenir un aperçu rapide des données.
Nous allons partir de ces données et les importer dans Power Query :
Vous retrouverez les données de cet exemple dans le fichier profilage.xlsx.
Ouvrez le fichier profilage.xlsx.
Sélectionnez une cellule contenue dans le tableau de données.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur le bouton À partir de Tableau ou d’une Plage.
Au niveau du ruban de Power Query, sélectionnez l’onglet Affichage.
Dans le groupe Aperçu des données, cochez les cases Qualité de la colonne, Distribution des colonnes et Profil de colonne.
À présent, sélectionnez par exemple la colonne Chiffres d’affaires.
Le résultat est le suivant :
Juste en dessous de l’intitulé de colonne, nous avons le nombre d’erreurs, le nombre de valeurs vides et la distribution des valeurs.
Plus bas, sur le côté gauche se trouvent différentes Statistiques de colonnes : Nombre, Erreur, Vide, Min, Max, Moyenne etc.
Et enfin sur le côté droit, une autre Distribution de valeurs, plus étoffée.
D’après ces informations, pour la colonne...
Manipulations avancées
Les colonnes conditionnelles
Les colonnes conditionnelles permettent de définir de nouveaux champs en fonction de règles et de conditions logiques, de la même façon que les fonctions Si et SI.MULTIPLE d’Excel.
L’outil Colonne conditionnelle se trouve dans l’onglet Ajouter une colonne au niveau du groupe Général.
Application : catégoriser les animaux en fonction de leur poids
Un vétérinaire a créé un tableau Excel contenant diverses informations sur les animaux de compagnie de ses clients.
Les données se présentent de la manière suivante :
Vous retrouverez les données de cet exemple dans le fichier 7_veterinaire.xlsx.
Dans cet exemple, le but est de créer une nouvelle colonne nommée Classe qui dépendra des conditions suivantes :
-
Si le poids de l’animal est supérieur à 10 kg, alors la classe est lourd.
-
Si le poids de l’animal est supérieur ou égal à 5 kg et inférieur ou égal à 10 kg, alors la classe est moyen
-
Si le poids est inférieur à 5 kg, alors la classe est léger.
Importation des données dans Power Query
Ouvrez le fichier 7_veterinaire.xlsx
Sélectionnez une cellule contenue dans le tableau de données, par exemple la cellule A2.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur le bouton À partir de Tableau ou d’une Plage.
L’éditeur de requête apparaît à l’écran.
Mise en place de la colonne conditionnelle
Dans l’onglet Ajouter une colonne, au niveau du groupe Général, cliquez sur le bouton Colonne conditionnelle.
La boîte de dialogue Ajouter une colonne conditionnelle apparaît à l’écran.
Dans la zone Nouveau nom de Colonne, saisissez Classe.
Première condition
Dans la liste déroulante Nom de la colonne, choisissez la colonne Poids (kg).
Au niveau de l’Opérateur, choisissez est supérieur à.
Au niveau de la Valeur, saisissez 10.
Au niveau de la Sortie, saisissez lourd.
Cliquez sur le bouton Ajouter une clause.
Deuxième condition
Dans la liste déroulante...
Cas pratique : le budget municipal
Présentation du cas
Vous êtes responsable du budget de la direction culturelle d’une mairie. À la fin de chaque année, les directeurs des différents services vous font parvenir leurs propositions budgétaires prévisionnelles pour l’année à venir.
Votre tâche est de collecter les données de chacun des services afin de présenter un budget prévisionnel synthétique de la direction culturelle aux élus de la ville.
Votre direction est composée de trois services : la culture, le sport et une biblio-médiathèque.
Bien entendu, chacun des tableaux que vous avez reçus est présenté d’une manière différente, mais cette année, grâce à Power Query, votre travail va être simplifié.
Le but de ce cas pratique est de connecter Power Query a chacun des trois tableaux, de les modifier et de créer une présentation synthétique grâce à un tableau croisé dynamique.
Pour cela, nous allons manipuler les données des trois tableaux afin de pouvoir les regrouper dans une table structurée.
Cette table se présentera de la manière suivante :
Le rapport souhaité devra présenter le montant alloué par service et par intitulé de compte.
Préparation des données
Tableau n°1 : le budget prévisionnel du service culturel
Le budget prévisionnel du service culturel se trouve dans le fichier 12_BP_Culture.xlsx.
Les données se présentent de la manière suivante :
Transformations à effectuer
Dans la colonne intitulé, se trouvent à la fois l’intitulé du compte et le numéro du compte. Pour obtenir la structure désirée à partir de ce tableau il va falloir fractionner la colonne. Le séparateur utilisé est le double point.
Connecter la table à Power Query
Ouvrez un classeur Excel vierge.
Dans l’onglet Données - groupe Récupérer et transformer des données, cliquez sur le bouton Obtenir des données - À partir d’un fichier - À partir d’un classeur Excel.
La boîte de dialogue Importer des données apparaît...