Power Query et Power Pivot dans Excel : importer, nettoyer et modéliser vos données pas à pas

Power Query dans Excel est l’outil qui importe, nettoie et transforme automatiquement vos données, quelle que soit leur source : classeurs, fichiers CSV, dossiers entiers, pages web ou bases SQL. Associé à Power Pivot, qui relie plusieurs tables et calcule des indicateurs avec le langage DAX, il transforme Excel en véritable outil de Business Intelligence.

Ce guide s’adresse aux utilisateurs qui passent trop de temps à copier-coller des exports, à enchaîner les RECHERCHEV ou à refaire chaque mois le même nettoyage. Vous verrez comment importer et préparer vos données, construire un modèle relié et créer vos premières mesures, avec les menus réels et les erreurs à éviter.

Qu’est-ce que Power Query et Power Pivot ?

Power Query est le moteur d’import et de transformation de données intégré à Excel. Chaque action réalisée dans son éditeur (filtrer, fractionner, changer un type) est enregistrée comme une étape. Quand la source change, un clic sur Actualiser rejoue toutes les étapes et produit un résultat propre, sans aucune manipulation manuelle.

Power Pivot est le moteur de modélisation d’Excel. Il stocke les données dans un modèle de données compressé, relie les tables entre elles par des relations et permet de créer des mesures en DAX, le même langage que Power BI.

Outil	Rôle	Question à laquelle il répond
Power Query	Préparer	« Comment obtenir des données propres, automatiquement ? »
Modèle de données	Stocker et relier	« Comment relier ventes, clients et produits sans RECHERCHEV ? »
Power Pivot et DAX	Calculer	« Comment obtenir la marge, le cumul annuel ou l’évolution N-1 ? »
TCD et graphiques	Restituer	« Comment présenter et filtrer les résultats ? »

Pourquoi utiliser Power Query dans Excel ?

Les bénéfices sont immédiats dès qu’une tâche se répète :

• Consolider automatiquement les fichiers mensuels déposés dans un dossier.
• Nettoyer un export de logiciel métier : colonnes inutiles, espaces parasites, dates au mauvais format, lignes de total à supprimer.
• Croiser plusieurs sources : un export CSV de ventes, une table clients dans un classeur, une grille tarifaire.
• Dépasser la limite d’une feuille : le modèle de données accepte des volumes bien supérieurs au million de lignes d’une feuille Excel.
• Fiabiliser les reportings : les étapes étant enregistrées, le traitement est identique à chaque actualisation.

Power Query Excel : importer et nettoyer les données pas à pas

Exemple fil rouge : un dossier qui reçoit chaque mois un export CSV des ventes, une table Clients et une table Produits dans un classeur de référence.

Étape 1 — Importer des données

Onglet Données > Obtenir des données. Les sources les plus courantes :

• À partir d’un fichier > À partir d’un classeur : une feuille ou un tableau d’un autre fichier Excel ;
• À partir d’un fichier > À partir d’un fichier texte/CSV : exports de logiciels ;
• À partir d’un fichier > À partir d’un dossier : tous les fichiers d’un dossier, combinés en une seule table ;
• À partir d’une base de données > À partir d’une base de données SQL Server : connexion directe à une base ;
• À partir d’autres sources > À partir du web : un tableau publié sur une page web.

Pour le dossier d’exports, choisissez À partir d’un dossier, sélectionnez-le, puis Combiner > Combiner et transformer les données. Power Query empile tous les fichiers et ajoute une colonne indiquant le nom du fichier source.

Étape 2 — Se repérer dans l’éditeur Power Query

L’éditeur s’ouvre dans une fenêtre dédiée. À gauche, la liste des requêtes ; au centre, l’aperçu des données ; à droite, les étapes appliquées. Chaque clic ajoute une étape, que vous pouvez renommer, supprimer ou déplacer.

Activez Affichage > Barre de formule : elle montre le code M généré pour l’étape sélectionnée. Par exemple, un filtre qui retire les montants vides produit :

= Table.SelectRows(#"Type modifié", each [Montant] <> null)

Cliquer sur une étape antérieure permet de voir les données à ce moment du traitement : c’est l’outil de diagnostic le plus utile.

Étape 3 — Définir les bons types de données

Le type est la première chose à vérifier. L’icône à gauche de chaque en-tête indique le type : texte, nombre décimal, nombre entier, date. Un montant détecté comme texte ne s’additionnera pas ; une date mal interprétée faussera les regroupements.

Pour une date au format français importée depuis un CSV, utilisez clic droit sur l’en-tête > Modifier le type > Utilisation des paramètres régionaux, puis choisissez Date et Français (France). Cette précaution évite les inversions jour/mois.

Étape 4 — Nettoyer les colonnes

Les transformations les plus courantes, toutes accessibles par les onglets Accueil et Transformer :

• Supprimer les colonnes inutiles (clic droit > Supprimer les autres colonnes pour ne garder que la sélection) ;
• Supprimer les doublons (Accueil > Réduire les lignes > Supprimer les lignes) ;
• Remplacer les valeurs, y compris les null par 0 ou par « Non renseigné » ;
• Format > Supprimer les espaces et Nettoyer pour les textes ;
• Fractionner la colonne par délimiteur ou nombre de caractères (par exemple « Dupont, Marie ») ;
• Fusionner les colonnes pour reconstituer un identifiant ;
• Supprimer le tableau croisé dynamique des autres colonnes : transforme un tableau avec douze colonnes de mois en deux colonnes Mois et Montant, format indispensable pour l’analyse.

Étape 5 — Regrouper, fusionner et ajouter des requêtes

Regrouper par (onglet Transformer) agrège les données : total des ventes par client et par mois, par exemple. Fusionner des requêtes joint deux tables sur une colonne commune, comme une RECHERCHEV, mais sur toutes les lignes à la fois et sans formule. Ajouter des requêtes empile deux tables de même structure.

Pour rendre le fichier portable, créez un paramètre (Accueil > Gérer les paramètres) contenant le chemin du dossier source, et utilisez-le dans l’étape Source. Changer d’emplacement ne demande alors qu’une modification.

Étape 6 — Charger et actualiser

Accueil > Fermer et charger dans… propose plusieurs destinations :

• Tableau dans une feuille : pour consulter ou utiliser les données avec des formules ;
• Rapport de tableau croisé dynamique : directement une synthèse ;
• Uniquement créer la connexion, en cochant Ajouter ces données au modèle de données : le choix recommandé pour les tables destinées à Power Pivot.

Le mois suivant, déposez le nouveau fichier dans le dossier, puis Données > Actualiser tout (Ctrl+Alt+F5). Toutes les étapes sont rejouées.

Power Pivot : relier les tables et créer des mesures DAX

Une fois les tables chargées dans le modèle, Power Pivot prend le relais.

Étape 7 — Activer et ouvrir Power Pivot

Si l’onglet n’est pas visible : Fichier > Options > Compléments, choisissez Compléments COM dans la liste Gérer, cliquez sur Atteindre et cochez Microsoft Power Pivot for Excel. Ensuite, Power Pivot > Gérer (ou Données > Gérer le modèle de données) ouvre la fenêtre du modèle. Rappel : Power Pivot n’existe que dans Excel pour Windows.

Étape 8 — Créer les relations

Passez en Vue de diagramme (onglet Accueil de la fenêtre Power Pivot). Chaque table apparaît sous forme de boîte. Faites glisser la colonne IdClient de la table Ventes vers la colonne IdClient de la table Clients : la relation est créée.

La structure recommandée est le schéma en étoile : une table de faits au centre (les ventes, une ligne par transaction) et des tables de dimensions autour (clients, produits, dates), chacune avec un identifiant unique. Dans la même vue, vous pouvez créer des hiérarchies (Année > Trimestre > Mois, Catégorie > Produit) pour faciliter la navigation dans les TCD.

Étape 9 — Ajouter une table de dates

Les calculs temporels exigent une table de dates continue. Dans Power Pivot : Conception > Table de dates > Nouvelle. Reliez sa colonne Date à la colonne date de la table des ventes, et vérifiez qu’elle est bien marquée comme table de dates.

Étape 10 — Écrire vos premières mesures DAX

Une mesure est un calcul qui s’évalue selon le contexte du TCD : chaque cellule affiche la mesure pour sa région, son mois, son produit. Créez-la dans la zone de calcul sous une table dans Power Pivot, ou depuis Excel avec Power Pivot > Mesures > Nouvelle mesure :

CA Total := SUM ( Ventes[Montant] )

Marge := SUMX ( Ventes ; Ventes[Quantité] * ( Ventes[Prix unitaire] - Ventes[Coût unitaire] ) )

Taux de marge := DIVIDE ( [Marge] ; [CA Total] )

CA N-1 := CALCULATE ( [CA Total] ; SAMEPERIODLASTYEAR ( Calendrier[Date] ) )

Les noms de fonctions DAX restent en anglais, même dans Excel en français. Le séparateur d’arguments suit en revanche vos paramètres régionaux : point-virgule en français, virgule en anglais.

Étape 11 — Exploiter le modèle dans un TCD

Insertion > Tableau croisé dynamique > À partir du modèle de données. Le volet des champs affiche toutes vos tables : glissez la Région de la table Clients en lignes, l’Année de la table Calendrier en colonnes et la mesure CA Total en valeurs. Les relations font le travail que faisaient auparavant des dizaines de RECHERCHEV. Si vous débutez avec cet outil, notre guide du tableau croisé dynamique Excel en présente toutes les bases.

Colonne calculée ou mesure DAX : que choisir ?

C’est la question que tout le monde se pose en découvrant Power Pivot.

Critère	Colonne calculée	Mesure
Calculée	Ligne par ligne, à l’actualisation	À la volée, selon les filtres du TCD
Stockage	Occupe de la mémoire dans le modèle	Aucun stockage
Utilisation dans un TCD	En lignes, colonnes, filtres ou segments	Uniquement en valeurs
Exemple typique	Tranche d’âge, catégorie de client, marge unitaire	Total, moyenne, taux, cumul, évolution N-1
Règle pratique	Pour classer ou filtrer	Pour agréger

Notre conseil : dès qu’il s’agit d’un résultat chiffré à afficher dans les valeurs d’un TCD, créez une mesure. Réservez les colonnes calculées aux attributs qui servent à découper l’analyse.

Astuces avancées pour un modèle robuste

• Organisez les requêtes en groupes (clic droit > Déplacer vers le groupe) : sources, tables intermédiaires, tables finales.
• Désactivez le chargement des requêtes intermédiaires : clic droit > décocher Activer le chargement. Elles restent utilisables par les autres requêtes sans alourdir le fichier.
• Créez des fonctions personnalisées pour appliquer le même traitement à plusieurs sources.
• Gérez les erreurs : Accueil > Conserver les lignes > Conserver les erreurs, pour isoler et comprendre les lignes problématiques avant de les corriger.
• Masquez les colonnes techniques dans Power Pivot (identifiants, colonnes de tri) pour simplifier la liste des champs.
• Définissez des indicateurs de performance clés (KPI) sur vos mesures, avec un objectif et des seuils, pour afficher des icônes d’état dans les TCD.
• Convertissez un TCD en formules (Outils OLAP > Convertir en formules) : chaque cellule devient une fonction cube, par exemple VALEURCUBE, que vous pouvez placer librement dans une mise en page de tableau de bord.

Ces briques sont la base d’un reporting automatisé : notre méthode pour construire un tableau de bord Excel les assemble de bout en bout.

Les erreurs fréquentes avec Power Query et Power Pivot

Erreur	Conséquence	Solution
Types de données non vérifiés	Sommes nulles, dates inversées	Contrôler le type de chaque colonne, utiliser les paramètres régionaux
Chemin de fichier en dur	Requête cassée après déplacement du dossier	Utiliser un paramètre pour le chemin source
Charger toutes les requêtes en feuille	Classeur lourd et lent	Uniquement créer la connexion et ajouter au modèle
Identifiants en double dans une dimension	Relation impossible à créer	Supprimer les doublons dans Power Query
Pas de table de dates	Fonctions temporelles DAX fausses	Créer et relier une table de dates continue
Colonnes calculées pour tout	Modèle volumineux	Préférer les mesures pour les agrégations
Modifier la source sans actualiser	Résultats obsolètes	Actualiser tout (Ctrl+Alt+F5) ou à l’ouverture

Power Query dans Excel ou dans Power BI ?

Power Query existe à la fois dans Excel et dans Power BI Desktop, avec la même interface et le même langage M. De même, le moteur de Power Pivot est celui de Power BI.

Critère	Excel (Power Query + Power Pivot)	Power BI
Préparation des données	Power Query	Power Query, avec plus de connecteurs
Modélisation et DAX	Power Pivot (Windows uniquement)	Intégrée, plus complète
Visualisations	TCD, graphiques, segments	Visuels interactifs nombreux et personnalisables
Partage	Fichier Excel	Service en ligne, actualisation planifiée
Idéal pour	Analyses et reportings au sein d’Excel	Rapports partagés à grande échelle

Commencer par Excel est un excellent choix : tout ce que vous apprenez se transpose. Pour la suite, consultez nos guides Power Query dans Power BI et DAX dans Power BI.

Comment apprendre Power Query et Power Pivot ?

Ces outils s’apprennent mieux sur un cas réel, dans cet ordre :

1. Power Query, les bases : importer un classeur et un CSV, définir les types, nettoyer, charger.
2. Power Query, l’automatisation : dossier, paramètres, fusion et ajout de requêtes, gestion des erreurs.
3. Power Pivot : modèle de données, relations, table de dates, colonnes calculées.
4. DAX : premières mesures, puis CALCULATE et les fonctions temporelles.
5. Restitution : TCD sur le modèle, graphiques croisés dynamiques, segments, tableau de bord.

Pour être accompagné sur l’ensemble de ce parcours, la formation Power Query et Power Pivot dans Excel d’EspritAcadémique le couvre en trois heures, sans prérequis. Elle aborde :

• l’import de données depuis Excel, le web, SQL Server, un dossier ou un CSV ;
• les étapes appliquées, les paramètres, les fonctions et la gestion des erreurs ;
• le nettoyage : types, filtres, doublons, valeurs null, fractionnement et regroupement ;
• le modèle Power Pivot : relations, table de dates, hiérarchies, perspectives et KPI ;
• les colonnes calculées et les mesures, des plus simples aux plus avancées ;
• la construction et le partage d’un tableau de bord complet.

Si vous reprenez Excel depuis le début, notre feuille de route pour apprendre Excel situe ces outils dans la progression globale.

En résumé

Power Query automatise l’import et le nettoyage des données, Power Pivot les relie dans un modèle et le DAX calcule des indicateurs qui s’adaptent à chaque filtre. Ensemble, ils remplacent les copier-coller mensuels et les RECHERCHEV en cascade par un flux fiable qui s’actualise d’un clic. Commencez par un import que vous refaites chaque mois : c’est le meilleur terrain d’apprentissage.