Power Pivot : construire un modèle de données dans Excel

Ce que vous saurez faire à la fin

1. Activer Power Pivot et Power Query dans Excel.
2. Importer plusieurs tables et créer des relations entre elles.
3. Construire des mesures DAX (CALCULATE, SUMX, FILTER, ALL).
4. Bâtir un cube de données et un tableau croisé dynamique multi-tables.
5. Optimiser les performances pour des modèles de plusieurs millions de lignes.

Durée : 6h pour les fondamentaux + pratique sur 1 mois. Pré-requis : Excel 2016 ou supérieur (Pro Plus, Microsoft 365 Apps for Business / E3 / E5), connaissance solide d’Excel et tableaux croisés dynamiques, fichiers Excel sources avec plusieurs tables (Ventes, Clients, Produits, Calendrier).

Étape 1 — Activer Power Pivot et Power Query

Excel > Fichier > Options > Compléments > en bas, Gérer « Compléments COM » > OK > cochez « Microsoft Power Pivot for Excel » > OK. Un nouvel onglet « Power Pivot » apparaît. Power Query est nativement intégré dans Données > Obtenir des données. Vérifiez la présence des deux avant de continuer.

Étape 2 — Comprendre l’architecture en étoile

Schéma type recommandé (dimensional model) :

 [DimClients]
 |
 |
[DimProduits]-+-[FactVentes]-[DimCalendrier]
 |
 |
 [DimMagasins]

Tables de dimensions (Dim) : descriptives, peu de lignes (clients, produits, etc.)
Table de faits (Fact) : transactions, beaucoup de lignes (ventes, paiements)
Liaison via clés étrangères (CodeClient, CodeProduit, etc.)

Étape 3 — Importer plusieurs tables avec Power Query

Données > Obtenir des données > choisissez la source : Excel, CSV, base SQL Server, dossier complet, web. Pour chaque table :

1. Charger dans l’éditeur Power Query
2. Renommer les colonnes en clair (espaces autorisés)
3. Définir le type de chaque colonne (texte, nombre, date)
4. Filtrer les lignes vides
5. Cliquer « Charger dans » > « Modèle de données » (pas dans la feuille)

Étape 4 — Créer une table Calendrier

La meilleure pratique : ajouter une table de dates explicite. Dans Power Pivot > Conception > Tables liées > Créer un calendrier :

Date début : 01/01/2020
Date fin : 31/12/2030
Colonnes calculées :
  Année = YEAR([Date])
  Mois numéro = MONTH([Date])
  Mois nom = FORMAT([Date], "mmmm")
  Trimestre = "T" & CEILING(MONTH([Date])/3, 1)
  Jour semaine = WEEKDAY([Date], 2)

Étape 5 — Créer les relations entre tables

Power Pivot > Vue diagramme. Glissez la clé d’une table vers la même clé dans une autre. Une ligne apparaît avec :

• Cardinalité : 1 vers plusieurs (1:n) ou plusieurs vers plusieurs (n:n, à éviter)
• Direction du filtre : unidirectionnelle (par défaut, recommandé) ou bidirectionnelle
• Active : oui (rouge si inactive)

Validez : un double-clic affiche les détails. Sans relations, vos calculs renvoient des chiffres absurdes.

Étape 6 — Vos premières mesures DAX

Onglet Power Pivot > Mesures > Nouvelle mesure
Nom : CA Total
Formule :
CA Total := SUM(FactVentes[MontantHT])

Nom : Quantité totale
Formule :
Quantité Totale := SUM(FactVentes[Quantité])

Nom : Prix moyen
Formule :
Prix Moyen := DIVIDE(SUM(FactVentes[MontantHT]), SUM(FactVentes[Quantité]), 0)

Format : nombre (FCFA, 0 décimales pour montants)

Étape 7 — Mesures avec CALCULATE et FILTER

CA 2025 :=
CALCULATE(
 [CA Total],
 DimCalendrier[Année] = 2025
)

CA Région Dakar :=
CALCULATE(
 [CA Total],
 DimMagasins[Région] = "Dakar"
)

CA Top 10 produits :=
CALCULATE(
 [CA Total],
 TOPN(10, ALL(DimProduits[NomProduit]), [CA Total], DESC)
)

CA Année courante :=
TOTALYTD([CA Total], DimCalendrier[Date])

CA Année précédente même période :=
CALCULATE([CA Total], SAMEPERIODLASTYEAR(DimCalendrier[Date]))

Croissance YoY % :=
DIVIDE([CA Total] - [CA Année précédente même période], [CA Année précédente même période], 0)

Étape 8 — Mesures itératives avec SUMX

SUMX itère sur chaque ligne d'une table et applique une formule.

Marge totale (HT) :=
SUMX(
 FactVentes,
 FactVentes[Quantité] * (FactVentes[PrixVenteHT] - RELATED(DimProduits[CoutAchat]))
)

Cas d'usage :
- Marge par ligne (sans colonne calculée préalable)
- Pondération (ex: panier moyen pondéré)
- Calculs row-by-row

Étape 9 — Construire un tableau croisé dynamique multi-tables

Insertion > TCD à partir du modèle de données. Faites glisser dans :

Filtres : DimCalendrier[Année]
Colonnes  : DimCalendrier[Mois nom]
Lignes : DimMagasins[Région]
Valeurs : [CA Total], [Marge totale (HT)], [Quantité totale]

Vous obtenez un TCD multi-tables impossible à faire avec Excel classique sans VLOOKUP infini.

Étape 10 — Construire des KPIs visuels

Power Pivot > Mesures > sélectionnez une mesure > « Créer un KPI ». Définissez :

• Cible : valeur absolue ou autre mesure (ex: budget mensuel)
• Seuils : 80 % rouge, 80-100 % orange, > 100 % vert
• Style d’icône : feux, flèches, jauges

Le TCD affiche désormais des icônes colorées à côté des valeurs. Idéal pour dashboards exécutifs.

Étape 11 — Construire un graphique croisé dynamique connecté

Insertion > Graphique croisé dynamique. Connectez aux mêmes données. Avantages : (a) interactivité avec segments / chronologie, (b) connexion entre plusieurs graphiques (un segment filtre tous les graphiques d’une feuille), (c) mise à jour en 1 clic via Données > Actualiser tout.

Étape 12 — Optimiser les performances

Pour modèles > 1 million de lignes :

• Réduisez le nombre de colonnes importées (gardez seulement l’utile)
• Utilisez les types appropriés (entier 32 bits < 2 milliards, sinon entier 64)
• Privilégiez les colonnes calculées DAX aux formules Power Query si répétées
• Évitez les relations bidirectionnelles sauf nécessité
• Utilisez VAR pour stocker les sous-calculs et éviter les recalculs
• Activez « Détecter les rapports automatiquement » si fichier > 100 Mo

Étape 13 — Bonnes pratiques DAX professionnelles

1. Préfixez les mesures par tablename : "Ventes[CA Total]" plutôt que "CA Total"
2. Utilisez DIVIDE() au lieu de "/" (gère la division par zéro)
3. CALCULATE() est votre meilleur ami pour appliquer des contextes de filtre
4. RELATED() pour récupérer une valeur depuis une table liée
5. ALL() ignore les filtres, ALLEXCEPT() ignore tous sauf...
6. Variables (VAR... RETURN) pour la lisibilité et les performances
7. Documentez chaque mesure avec un commentaire (-- mon commentaire)

Étape 14 — Migrer vers Power BI quand vous dépassez les limites Excel

Vous saurez qu’il est temps de migrer vers Power BI Desktop quand :

• Fichier Excel > 250 Mo (devient instable)
• Plus de 5 millions de lignes en table de faits
• Besoin de partage interactif avec 5+ utilisateurs
• Refresh automatique programmé nécessaire
• Mobile-friendly indispensable

Power BI Desktop est gratuit. Service Power BI : 10 $/user/mois. Le langage DAX est strictement le même : pas de réapprentissage.

Erreurs courantes en Power Pivot

• Importer dans une feuille au lieu du modèle : on perd toute la puissance.
• Pas de table Calendrier : impossible d’utiliser les fonctions Time Intelligence.
• Relations many-to-many partout : bug et perfs en ruine.
• Mesures avec /0 sans DIVIDE : erreur #DIV/0! qui casse tout le rapport.
• Trop de colonnes calculées vs mesures : alourdit le modèle inutilement.

Checklist Power Pivot pro

✓ Power Pivot et Power Query activés
✓ Schéma en étoile avec tables Dim et Fact
✓ Tables importées via Power Query (modèle de données)
✓ Table Calendrier créée et marquée comme telle
✓ Relations 1:n vérifiées
✓ Mesures de base (CA, Quantité, Marge)
✓ Mesures Time Intelligence (YTD, YoY)
✓ Mesures conditionnelles (CALCULATE + FILTER)
✓ KPIs visuels avec seuils définis
✓ TCD et graphiques croisés connectés
✓ Performances optimisées (variables, types, relations)
✓ Documentation des mesures et schéma
✓ Plan de migration Power BI si croissance

Voir aussi

• GROUPER.PAR et PIVOTER.PAR dans Excel : alternative aux tableaux croisés dynamiques
• Tableaux croisés dynamiques Excel : l’essentiel à maîtriser
• Power Pivot Excel : modèle de données pour PME

Etape 1 : activer Power Pivot dans Excel

Avant de modeliser quoi que ce soit, Power Pivot doit etre actif. Dans Excel 2016 a 2024 sur Windows, ouvrez Fichier > Options > Complements. En bas, dans la liste deroulante Gerer, choisissez Complements COM puis cliquez sur Atteindre. Cochez Microsoft Power Pivot for Excel et validez. Un nouvel onglet Power Pivot apparait dans le ruban.

Sur Mac, Power Pivot n’est pas disponible nativement : utilisez Power Query pour preparer les donnees, puis basculez sur Excel pour Windows ou sur Power BI Desktop pour la modelisation. Si l’onglet n’apparait toujours pas apres l’activation, verifiez votre licence Microsoft 365 : les editions Famille n’incluent pas Power Pivot, contrairement a Business Standard et Apps for Enterprise.

Etape 2 : importer les tables source dans le modele

Un modele Power Pivot tire sa puissance de la combinaison de plusieurs tables. Cliquez sur Power Pivot > Gerer pour ouvrir la fenetre dediee, puis sur Obtenir des donnees externes. Trois sources couvrent 90 % des cas : un fichier Excel local, un fichier CSV exporte d’un ERP comme Odoo Senegal, ou une base SQL Server hebergee chez Orange Business Services Dakar.

// Exemple : tables typiques d'une PME senegalaise
Ventes (date_vente, id_client, id_produit, montant_fcfa)
Clients (id_client, raison_sociale, ville, segment)
Produits (id_produit, libelle, categorie, prix_unitaire)
Calendrier (date, mois, trimestre, annee)

Apres l’import, chaque table apparait sous forme d’onglet en bas de la fenetre Power Pivot. Verifiez les types de donnees : un montant en FCFA doit etre en Nombre decimal, une date en Date, un identifiant client en Texte meme s’il ne contient que des chiffres (sinon les zeros initiaux disparaissent).

Etape 3 : creer les relations entre tables

Le modele relationnel repose sur des cles. Passez en Vue de diagramme via le bouton en haut a droite de la fenetre Power Pivot. Glissez la colonne id_client de Ventes vers id_client de Clients. Une ligne apparait avec un 1 du cote Clients et un asterisque du cote Ventes : c’est une relation un-a-plusieurs.

Repetez pour id_produit entre Ventes et Produits, et pour date_vente entre Ventes et Calendrier. Si Excel refuse une relation avec le message « valeurs en double dans la colonne de recherche », votre table de dimension contient des doublons : nettoyez-la avant tout. Une bonne table de dimension a une cle unique, sans NULL ni espace en fin de chaine.

Etape 4 : ecrire les premieres mesures DAX

Les mesures sont l’equivalent Power Pivot des formules Excel, mais elles s’executent au niveau du modele entier et non d’une cellule. Dans la vue de donnees, cliquez en bas de la table Ventes et tapez :

Total Ventes := SUM(Ventes[montant_fcfa])
Nb Clients := DISTINCTCOUNT(Ventes[id_client])
Panier Moyen := DIVIDE([Total Ventes], [Nb Clients])

L’operateur := definit une mesure nommee. DIVIDE est prefere a / car il gere proprement la division par zero. Une fois validees, ces mesures apparaissent dans la liste des champs des Tableaux croises dynamiques. Vous obtenez alors un panier moyen calcule sur 50 000 lignes en moins d’une seconde, la ou un SUMIFS classique ramerait.

Etape 5 : exploiter le modele dans un tableau croise

Revenez sur Excel via Power Pivot > Tableau croise dynamique. La liste des champs montre toutes vos tables. Glissez Calendrier[mois] en lignes, Produits[categorie] en colonnes, et la mesure Total Ventes en valeurs. Le tableau s’actualise instantanement, meme avec un million de lignes en source.

Astuce locale : creez une mesure Total Ventes EUR := DIVIDE([Total Ventes], 655.957) pour exposer les chiffres en euros aux partenaires francais ou belges, en utilisant la parite fixe FCFA-EUR. Vous pouvez ensuite afficher les deux mesures cote a cote dans le meme tableau croise, tres utile pour les reportings groupe.

Etape 6 : etendre avec des KPI et des hierarchies

Power Pivot permet de definir des KPI visuels (vert, jaune, rouge) sur n’importe quelle mesure. Clic droit sur Total Ventes dans la liste des champs, puis Creer un KPI. Definissez une cible absolue (ex : 50 millions FCFA) ou une mesure cible (objectif mensuel), et configurez les seuils. Le tableau croise affiche alors des icones de statut a cote des valeurs.

Les hierarchies regroupent plusieurs colonnes pour une exploration drill-down. Sur la table Calendrier, en vue diagramme, selectionnez annee, trimestre, mois avec Ctrl+clic, puis clic droit > Creer une hierarchie. L’utilisateur final pourra deplier annee > trimestre > mois dans un seul champ.

Etape 7 : maintenir et documenter le modele

Un modele Power Pivot vit avec l’entreprise. Activez l’actualisation automatique a l’ouverture du classeur via Donnees > Connexions > Proprietes. Pour les sources volumineuses (SQL Server, ERP), planifiez plutot un rafraichissement nocturne via Power Automate Desktop ou un script PowerShell Invoke-Item declenche par le Planificateur de taches Windows.

Documentez chaque mesure dans la zone Description (clic droit sur la mesure dans la liste des champs). Pour les equipes mixtes Dakar-Abidjan, partagez le modele via SharePoint Online ou OneDrive Business : Excel sait le rouvrir tout en preservant les relations et mesures DAX. Pour creuser ce sujet sur la modernisation des outils analytiques, voyez notre guide BI en PME et notre tutoriel Excel vers Power BI.

Etape 8 : pieges courants et bonnes pratiques

Trois erreurs reviennent chez les debutants. Premiere : melanger les colonnes calculees et les mesures. Une colonne calculee s’evalue ligne par ligne au chargement et alourdit le modele ; une mesure s’evalue dans le contexte du tableau croise. Privilegiez les mesures pour tout calcul agrege. Deuxieme : oublier la table Calendrier dediee. Sans elle, les fonctions DAX de time intelligence comme SAMEPERIODLASTYEAR ne fonctionnent pas.

Troisieme erreur : importer des donnees brutes non nettoyees. Faites passer chaque source par Power Query (onglet Donnees > Obtenir des donnees) avant chargement dans Power Pivot : suppression des doublons, conversion des types, normalisation des libelles villes (Dakar vs DAKAR vs dakar). Un modele propre se maintient seul ; un modele sale demande une intervention chaque fin de mois.