Name: Business Intelligence avec Excel Des données brutes à l'analyse stratégique (2e édition)
Rating: 5 (2 reviews)

Objectif

Ce premier 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, depuis des décennies, les utilisateurs d’Excel manipulent des données à la manière d’un ETL, cependant jusque-là ces tâches étaient réalisées soit :

manuellement, ce qui engendre à la fois une perte de temps et un risque d’erreur,
grâce aux formules d’Excel et/ou au code VBA. Excel est un outil puissant offrant une grande liberté à l’utilisateur. Toutefois, cette liberté...

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

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.

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

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 d’un 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...

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 d’un tableau ou d’une plage.

L’éditeur Power Query s’ouvre.

Extraction des années de naissance

Sélectionnez la colonne Date de naissance.

Dans le ruban, sélectionnez l’onglet Ajouter une colonne, au niveau du groupe Date et heure de début, cliquez sur le bouton Date, puis sur l’option Année puis Année.

Une nouvelle colonne nommée Année apparaît :

Retrouver l’âge d’une personne

Sélectionnez à nouveau la colonne Date de naissance.

Dans le ruban, sélectionnez l’onglet...

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 d’un 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 Chiffres d’affaires, il semblerait qu’il y ait une valeur vide et une valeur en double.

Passez le curseur sur la distribution, une nouvelle boîte de dialogue apparaît.

Cliquez sur le bouton images/icp77.png en bas à droite de cette nouvelle boîte de dialogue.

Un nouveau menu apparaît, il permet par exemple de supprimer les doublons ou de supprimer les éléments vides.

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 d’un 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 à...

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.