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
💥 Les 22 & 23 novembre : Accès 100% GRATUIT
à la Bibliothèque Numérique ENI. Je m'inscris !
  1. Livres et vidéos
  2. Business Intelligence avec Excel et Copilot
  3. La préparation des données avec Power Query
Extrait - Business Intelligence avec Excel et Copilot Des données brutes à l'analyse stratégique (3e édition)
Extraits du livre
Business Intelligence avec Excel et Copilot Des données brutes à l'analyse stratégique (3e édition) Revenir à la page d'achat du livre

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.

images/01SOB01.png

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 :

images/repise_chap_2_1.png

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.

images/reprise_chap_2_2.png

Une nouvelle fenêtre s’ouvre, il s’agit de l’éditeur Power Query.

images/reprise_chap_2_3.png

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 :

images/reprise_chap_2_4.png

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.

images/01SOB06.png

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.

images/SOBP20.png

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.

images/01SOB08.png

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.

images/2022_SOB01_6.png

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.

images/2022_SOB01_07.png

La boîte de dialogue Importer des données apparaît à l’écran.

images/01SOB31.png

 Sélectionnez le fichier Excel souhaité puis cliquez sur le bouton Importer.

Une boîte de dialogue appelée Navigateur apparaît.

images/01SOB32.png

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.

images/01SOB33.png

Les données sont alors importées sous forme de table dans le fichier Excel actif.

images/01SOB34.png

À droite de l’écran, le volet Requêtes et connexions indique qu’une requête a été mise en place.

images/2022_SOB01_8.png

 Pour obtenir d’autres méthodes d’affichage des données, cliquez sur la flèche à droite du bouton Charger puis sur Charger dans.

images/01SOB36.png

La boîte de dialogue Importation de données apparaît à l’écran.

images/01SOB37.png

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 :

images/2022_SOB01_9.png

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.

images/2022_SOB01_10.png

La boîte de dialogue Importer des données apparaît à l’écran.

images/01SOB40.png

 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.

images/01SOB41.png

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

images/2022_SOB01_11.png

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.

images/2022_SOB01_12.png

La boîte de dialogue Parcourir apparaît à l’écran.

 Sélectionnez le dossier rapport mensuel.

images/2022_SOB01_13.png

 Cliquez sur le bouton Ouvrir.

Une nouvelle boîte de dialogue s’affiche à l’écran :

images/2022_SOB01_14.png

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.

images/2022_SOB01_15.png

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.

images/2022_SOB01_16.png

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.

images/2022_SOB01_17.png

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.

images/2022_SOB01_18.png

 Cliquez sur le bouton OK.

Les données sont combinées et directement chargées sous forme de tableau classique dans Excel.

images/SOBP42b.png

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 :

images/01SOB51.png

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.

images/N01SOB01.png

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.

images/2022_SOB01_20.png

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.

images/01SOB54.png

 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.

images/N01SOB02.png

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.

images/01SOB57.png

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 :

images/01SOB64.png

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 :

images/01SOB65.png

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.

images/2022_SOB01_27.png

La boîte de dialogue Créer un tableau apparaît à l’écran :

images/2022_SOB01_28.png

 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 :

images/2022_SOB01_29.png

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.

images/2022_SOB01_30.png

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.

images/2022_SOB01_44.png
  • Dans l’onglet Ajouter une colonne du ruban, au niveau du groupe Date et heure de début.

images/01SOB115.png

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.

images/01SOB116.png

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 :

images/2022_SOB01_45.png

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.

images/2022_SOB01_46.png

 À présent, sélectionnez par exemple la colonne Chiffres d’affaires.

Le résultat est le suivant :

images/2022_SOB01_47.png

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.

images/01SOB123.png

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 :

images/01SOB124.png

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.

images/Chap1p80.PNG

 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. 

images/01SOB191.png

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 :

images/01SOB192.png

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 :

images/2022_SOB01_79.png

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