Comment utiliser Power Query et Power Pivot dans Excel comme un pro

Si vous voulez être un véritable utilisateur avancé des tableurs, vous devez apprendre à utiliser les fonctionnalités Power Query et Power Pivot dans Excel. Bien que vous puissiez accomplir beaucoup de choses avec Excel seul, l’utilisation de ces fonctionnalités “Power” intégrées fera de vous un utilisateur professionnel avancé d’Excel. Dans ce guide, vous apprendrez à utiliser Power Query pour importer potentiellement des centaines de fichiers différents contenant des millions de lignes et à utiliser Power Pivot pour générer des analyses complexes sur les modèles de données massifs que vous importez.

Table des matières

• 1. Importation de données à partir de plusieurs fichiers
• 2. Filtrage des fichiers et des lignes importés
• 3. Chargement des données filtrées dans un modèle de données
• 4. Analyse des données avec un tableau croisé dynamique
• 5. Présentation de vos données avec PowerChart
• Questions fréquentes

Vous souhaitez protéger votre travail des regards indiscrets ? Apprenez à ajouter un mot de passe à votre classeur Excel.

1. Importation de données à partir de plusieurs fichiers

En utilisant Power Query, vous pouvez importer des données à partir de différents fichiers dans un seul dossier. Soyez prudent, cependant, car vous devez vous assurer que toutes les données suivent le même format. Gardez le même nombre de colonnes et les mêmes noms d’en-tête de colonne et assurez-vous que le type de données pour les valeurs dans chaque colonne est cohérent.

Pour ce guide, nous utilisons cinq fichiers qui contiennent environ 100 millions de lignes de données provenant de Kaggle. Téléchargez-les si vous souhaitez suivre ou utilisez vos propres données. Cela dit, voici les étapes pour importer les données :

1. Cliquez sur l’onglet “Données” dans le ruban.
2. Sélectionnez “Obtenir des données -> À partir du fichier -> À partir du dossier”, puis parcourez votre dossier et sélectionnez-le.

1. Vous devriez voir une nouvelle fenêtre apparaître. Dans notre cas, nous avons cinq fichiers de test qui sont nommés selon leur taille : “adult10m” a 10 millions de lignes de données, “adult1m” a 1 million de lignes, “adult100k” a 100 000 lignes, et ainsi de suite. Cliquez sur “Transformer les données” pour ouvrir l’éditeur Power Query.

2. Filtrage des fichiers et des lignes importés

Dans l’éditeur Power Query, vous pouvez appliquer des filtres pour importer uniquement les fichiers qui correspondent aux critères que vous avez définis, ce qui est très utile pour éliminer les sources de données que vous ne souhaitez pas.

1. Nous avons quatre fichiers sans extension de fichier, mais un fichier avec l’extension .DATA. Retirons celui qui est différent en cliquant sur la flèche à côté de “Extension”.

1. Nous pouvons voir une liste de toutes les extensions de fichiers de nos fichiers. Dans cet exemple, il s’agit simplement de (vide) et .data. Décochez les extensions que vous ne souhaitez pas.

1. Chaque filtre que nous avons appliqué apparaîtra sous la section “Étapes appliquées”.
2. Nous pouvons également filtrer par nom de fichier en cliquant sur la flèche à côté de “Nom”.

1. Supposons que nous voulions filtrer tous les fichiers contenant “k”, comme “adult100k”, car nous ne voulons pas travailler avec de petits fichiers contenant moins d’un million de lignes. Nous pouvons appliquer un filtre de texte. Il existe de nombreuses options, mais pour nos besoins, le filtre “Ne contient pas” est le bon.

1. Tapez le texte que vous ne souhaitez pas voir dans le nom du fichier.

1. Faites défiler un peu vers la droite jusqu’à ce que vous puissiez voir les colonnes “Date modifiée” et “Date créée”. Filtrons pour sélectionner uniquement les fichiers créés dans une certaine plage de temps. Cliquez sur la flèche à côté de “Date créée”.

1. Choisissez “Filtres de date/heure -> Entre.”

1. Tapez les valeurs de temps pour les deux conditions et confirmez en appuyant sur “OK.”

1. Nous avons terminé de filtrer nos données au niveau des fichiers.
2. Regroupons nos fichiers restants afin que nous puissions filtrer les données elles-mêmes. Cliquez sur les doubles flèches à côté de l’en-tête “Contenu”.

1. Maintenant que vous pouvez voir toutes les valeurs pour les lignes individuelles, chaque ligne a une colonne supplémentaire, “Source.Name”, qui indique le fichier d’où elle provient.

1. Pour améliorer davantage nos données : puisque nos fichiers CSV n’avaient pas d’en-têtes de colonne, vous pouvez cliquer avec le bouton droit sur certains des en-têtes de colonne et cliquer sur “Renommer” pour leur donner un nom approprié.

1. Les filtres fonctionnent ici aussi. Nous pouvons appliquer un filtre de plage numérique. Par exemple, cliquez sur la flèche à côté de l’en-tête de colonne “Âge”, puis choisissez “Filtres numériques -> Supérieur à.”

1. Entrez un nombre tel que 30, puis cliquez sur “OK”. Toutes les lignes avec des âges inférieurs au nombre spécifié seront exclues.

Astuce : lorsque vous travaillez avec des tableurs dans Microsoft Excel, il peut être utile de savoir comment déplacer une colonne.

3. Chargement des données filtrées dans un modèle de données

Vous pouvez continuer à nettoyer et à filtrer les données, mais passons à la suite et chargeons-les dans un modèle de données pour commencer à les analyser.

1. Terminez avec Power Query en cliquant sur “Fermer et charger -> Fermer et charger vers.”
2. Dans la fenêtre contextuelle qui s’ouvre, sélectionnez “Créer uniquement une connexion” et cochez “Ajouter ces données au modèle de données.”

1. Attendez que les données soient chargées. Cela peut prendre quelques minutes si vous avez une grande quantité de données.

Utilisation du modèle de données Power Pivot dans Excel

1. Une fois le chargement des données terminé, nous pouvons apporter des modifications (comme ajouter de nouvelles colonnes) au modèle de données en cliquant sur “Données -> Outils de données -> Gérer le modèle de données.”

1. Vous devriez voir la fenêtre Power Pivot. Ajoutons une colonne avec une formule d’expressions d’analyse de données (DAX). Les formules DAX sont très similaires aux formules Excel que vous connaissez probablement déjà. Faites défiler vers la droite jusqu’à ce que vous voyiez la dernière colonne de vos données, puis cliquez sur “Ajouter une colonne.”
2. Écrivons une formule pour cette colonne qui applique un calcul simple à chaque ligne. Comparé aux formules Excel, la syntaxe DAX est légèrement différente. Par exemple, nous pouvons ajouter une colonne appelée “Description” et entrer la formule =CONCATENATE([Column9],[Column10]). Notez l’utilisation des crochets ([ ]) et l’accès via les noms d’en-tête de colonne.

1. Si vous le souhaitez, vous pouvez également effectuer des modifications supplémentaires comme renommer ou supprimer des colonnes en cliquant avec le bouton droit sur l’en-tête de colonne.

4. Analyse des données avec un tableau croisé dynamique

Après avoir terminé les modifications du modèle de données, il est temps d’analyser les données dans une feuille de calcul Excel à l’aide d’un tableau croisé dynamique.

1. Cliquez sur “Accueil -> Tableau croisé dynamique” dans le ruban de la fenêtre Power Pivot pour ouvrir la fenêtre “Créer un tableau croisé dynamique”.

1. Sélectionnez “Nouvelle feuille de calcul” et appuyez sur “OK.”

1. Le panneau des champs du tableau croisé dynamique s’ouvrira dans une nouvelle feuille sur la fenêtre principale d’Excel. Dans la boîte sous la barre de recherche, vous devriez voir vos sources de données répertoriées, “kaggle_adult_csv” dans cet exemple. Cliquez dessus pour l’étendre et afficher toutes ses colonnes.

1. Faisons une analyse où nous comptons le nombre de chaque “Description” dans nos données. Nous pouvons sélectionner les colonnes avec lesquelles nous voulons travailler en cliquant sur la case à cocher à côté de leurs noms. Sélectionnez “Description” et remarquez comment le focus se déplace automatiquement vers la zone “Lignes” ci-dessous. C’est Power Pivot qui démontre son intelligence – il peut deviner avec précision la zone appropriée pour les sélections. Puisque nous commençons à partir d’un tableau croisé dynamique vide, il est logique que notre première sélection soit des lignes.

1. Faites glisser la colonne “Description” cochée dans l’espace vide de “Valeurs”. Remarquez que Power Pivot suppose à nouveau correctement que nous voulons un “Nombre de Description” en raison du type de données étant du texte. De plus, notre tableau a été mis à jour avec une colonne fournissant les comptes de chaque occurrence de chaque valeur “Description”.

1. Nous pouvons ajouter une autre dimension à notre comptage. Par exemple, faites glisser la colonne “Âge” dans la zone “Colonnes” vide pour ajouter plus de détails au tableau en comptant le nombre de descriptions par âge.

1. Vous pouvez également ajouter une capacité de sélection de filtre au tableau en faisant glisser la colonne “WorkClass” dans la zone “Filtres”. Remarquez la nouvelle cellule interactive qui apparaît au-dessus de notre tableau.

1. Cliquez sur la flèche à côté de “Tous”, puis sélectionnez “Never-worked”. Cela changera le tableau pour inclure uniquement les personnes ayant un WorkClass de “Never-worked” dans les calculs. Vous pouvez changer le filtre à tout moment pour voir le tableau sous un autre angle.

Vous avez beaucoup de doublons dans votre classeur Excel ? Apprenez à trouver et à supprimer les doublons et à nettoyer vos tableurs.

5. Présentation de vos données avec PowerChart

Vous pouvez créer d’autres feuilles de calcul pour analyser vos données de différentes manières. Cette fois, créons un résultat plus visuellement impressionnant en utilisant PowerChart.

1. Retournez à la fenêtre “Power Pivot pour Excel”. Si elle est fermée, vous pouvez l’ouvrir depuis la fenêtre Excel via “Données -> Outils de données -> Gérer le modèle de données.”
2. Cliquez sur “Tableau croisé dynamique -> Graphique croisé dynamique -> Nouvelle feuille de calcul.”

1. Dans le panneau à droite, cliquez sur la source de données (“kaggle_adult_csv”) pour l’étendre et afficher toutes ses colonnes.
2. Faites glisser et déposez les colonnes de la source de données dans l’une des quatre zones ci-dessous pour construire le graphique. Déplacez la colonne “OccupationType” dans la zone “Axe (Catégories)”.

1. Faites glisser “OccupationType” à nouveau, et cette fois déposez-le dans la zone “Valeurs”. Vous remarquerez immédiatement qu’un graphique à barres est généré automatiquement. Il montre le nombre de chaque type d’occupation.

1. Vous pouvez également faire glisser la colonne “Ethnicity” dans la zone “Légende (Séries)”. Vous verrez instantanément un graphique plus granulaire qui compare combien de chaque ethnie composent chaque occupation.

1. Ensuite, nous pouvons appliquer des filtres à notre légende pour simplifier notre graphique et ne regarder que certaines valeurs. Déplacez votre souris sur “Ethnicity” afin qu’une flèche apparaisse sur le côté droit. Cliquez sur la flèche.

1. Cliquez sur les cases à cocher à côté des valeurs que vous souhaitez supprimer, ne laissant que deux.

1. Essayons quelque chose de nouveau. Sous la section “Valeurs”, cliquez sur “Nombre d’OccupationType”, puis cliquez sur “Supprimer le champ”. Faites glisser et déposez la colonne “Âge” pour la remplacer.

1. Vous remarquerez que la valeur est automatiquement définie comme “Somme d’âge”, mais ce n’est pas très utile dans le monde réel. Changeons cela. Cliquez dessus et sélectionnez “Paramètres du champ de valeur”.

1. Puisque l’âge est un nombre, il existe de nombreux calculs que nous pouvons appliquer à cette section. Essayez de sélectionner “Moyenne” par exemple.

1. La visualisation sera mise à jour pour montrer les âges moyens pour chaque occupation par ethnies spécifiques (que nous avons filtrées).

Vous souhaitez revoir les fondamentaux d’Excel ? Nous avons un guide complet sur tous les raccourcis clavier pour Excel.

Questions fréquentes

Quelles sont les origines de Power Pivot ?

Microsoft a introduit Power Pivot comme un module complémentaire distinct fourni par SQL Server 2008 R2 de Microsoft, qui a été publié en 2010. À l’époque, il était nommé “PowerPivot” sans espace. En 2013, il a été renommé “Power Pivot”. Il n’est devenu une fonctionnalité intégrée d’Excel qu’à partir de 2016.

Qu’est-ce que les expressions d’analyse de données et comment diffèrent-elles des formules Excel ?

Les expressions d’analyse de données sont un langage de formule utilisé par Power Pivot dans Excel et par Power BI. En essence, c’est ainsi que vous écrivez des formules pour des calculs dans les applications mentionnées : les déclarations, la syntaxe et les fonctions que vous utilisez.

L’avantage principal de DAX par rapport aux formules Excel est qu’il est conçu pour fonctionner sur des données agrégées. Les formules Excel standard peuvent effectuer des calculs ligne par ligne (comme calculer la somme de plusieurs valeurs dans une ligne), mais les formules DAX peuvent fonctionner colonne par colonne (comme compter le nombre d’une valeur spécifique dans une colonne), ou même sur une table entière.

Comment puis-je corriger l’erreur “Cette table a été créée par une requête. Pour modifier cette table, modifiez plutôt la requête” ?

Cette erreur dans Excel Power Pivot se produit lorsque vous essayez de modifier un modèle de données dans Power Pivot alors que ce modèle de données a été initialement chargé via Power Query. Vous ne pouvez pas faire cela, donc vous devez le modifier via la méthode d’origine de chargement : Power Query.

Sur le côté droit d’Excel, cliquez sur l’icône “Requêtes et connexions”, puis trouvez et double-cliquez sur votre source de données de requête pour ouvrir la fenêtre où vous pouvez modifier la table.

Crédit image : Pexels. Toutes les captures d’écran par Brandon Li.