Tutoriel Power Query
Qu'est-ce que Power Query et pourquoi cela change tout
Power Query est le moteur ETL (Extract, Transform, Load) intégré d'Excel. En termes simples : c'est un outil pour importer des données depuis presque n'importe quelle source, les nettoyer et les remodeler automatiquement, puis les charger dans votre feuille de calcul — chaque étape étant enregistrée et reproductible. Si vous avez déjà passé un vendredi après-midi à nettoyer manuellement un rapport qui arrive chaque semaine dans le même format désordonné, Power Query est la solution que vous attendiez.
Contrairement aux macros, Power Query ne nécessite aucune programmation. Vous construisez les étapes de transformation via une interface visuelle, et Power Query les enregistre en langage M en arrière-plan. Lorsque le fichier de la semaine suivante arrive, un seul clic réapplique toutes vos étapes de nettoyage.
Pas à pas : automatiser le nettoyage d'un rapport de ventes hebdomadaire
Scénario : chaque lundi, vous recevez sales_AAAAMMJJ.csv avec des formats de date incohérents, des catégories de produits fusionnées (Catégorie-Sous-catégorie dans une seule colonne), des lignes avec des montants de ventes manquants et des lignes de résumé supplémentaires en bas. Créez une requête Power Query qui nettoie tout automatiquement.
Étape 1 — Importer les données brutes
- Données > Obtenir des données > À partir d'un fichier > À partir d'un fichier texte/CSV.
- Sélectionnez votre fichier CSV de ventes. Le Navigateur prévisualise les données. Remarquez comment Power Query a déjà détecté les délimiteurs et les types de données.
- Cliquez sur Transformer les données (pas « Charger »). Cela ouvre l'éditeur Power Query — tout le nettoyage se fait ici.
Étape 2 — Promouvoir les en-têtes et supprimer les lignes inutiles
- Si la première ligne contient les en-têtes : Accueil > Utiliser la première ligne comme en-têtes. Faites toujours cela en premier — les en-têtes permettent de référencer les colonnes par leur nom dans les étapes suivantes.
- Supprimez les lignes de résumé en bas. Filtrez la colonne de date : cliquez sur la liste déroulante, décochez les lignes contenant du texte comme « Total » ou des valeurs vides. Ou utilisez Accueil > Supprimer des lignes > Supprimer les lignes du bas si vous connaissez le nombre de lignes supplémentaires.
- Supprimez les lignes complètement vides : Accueil > Supprimer des lignes > Supprimer les lignes vides.
Étape 3 — Diviser la colonne Catégorie de produit
- Votre colonne produit contient « Électronique-Accessoires » — catégorie trait d'union sous-catégorie. Vous avez besoin de deux colonnes.
- Sélectionnez la colonne Produit. Transformer > Diviser la colonne > Par délimiteur.
- Délimiteur : Personnalisé, saisissez
-. Diviser à : Délimiteur le plus à gauche (important : certaines sous-catégories contiennent des traits d'union, par ex. « Audio-Visuel »). - Cliquez sur OK. Vous avez maintenant Produit.1 (catégorie) et Produit.2 (sous-catégorie). Renommez-les : clic droit sur les en-têtes > Renommer.
Étape 4 — Corriger le format de date et gérer les valeurs manquantes
- Sélectionnez la colonne Date. Transformer > Type de données > Date. Si certaines dates ne se convertissent pas (affichant Erreur), cliquez sur la liste déroulante de la colonne > Remplacer les erreurs > saisissez la date du jour comme valeur de secours, ou filtrez pour examiner ces lignes.
- Pour la colonne Montant des ventes : sélectionnez-la, Transformer > Remplacer les valeurs. Valeur à rechercher :
null, Remplacer par :0. Cela remplace les ventes manquantes par zéro plutôt que de laisser des blancs. - Supprimez les lignes où le Montant des ventes est 0 si elles représentent des entrées sans signification (facultatif) : filtrez Montant des ventes > Filtres numériques > Supérieur à > 0.
Étape 5 — Charger et configurer l'actualisation automatique
- Accueil > Fermer et charger dans. Choisissez « Tableau » et « Nouvelle feuille de calcul ». Cliquez sur OK.
- Vos données nettoyées apparaissent dans Excel. Maintenant, pour automatiser : Données > Requêtes et connexions (volet à droite).
- Clic droit sur votre requête > Propriétés. Cochez Actualiser les données à l'ouverture du fichier. Vous pouvez également définir Actualiser toutes les X minutes pour les tableaux de bord en direct.
- La semaine prochaine : enregistrez le nouveau CSV avec le même nom au même emplacement, ouvrez ce classeur et cliquez sur Données > Actualiser tout. Toutes les étapes de nettoyage sont rejouées automatiquement.
Techniques clés
- Dépivoter pour des données prêtes à l'analyse. Si vos données ont des mois en colonnes séparées (Jan, Fév, Mar), sélectionnez les colonnes descriptives et Transformer > Dépivoter les autres colonnes. Les tableaux larges deviennent des tableaux hauts adaptés aux tableaux croisés dynamiques.
- Fusionner des requêtes au lieu de RECHERCHEV. Accueil > Fusionner des requêtes joint deux tables sur des colonnes correspondantes — la version Power Query de RECHERCHEV, mais capable de gérer des millions de lignes et plusieurs types de jointure (gauche, droite, externe complète, interne, anti).
- Regrouper par pour des résumés. Transformer > Regrouper par pour agréger les données (SOMME, NB, MOYENNE) par catégorie — comme un tableau croisé dynamique qui s'exécute avant que les données n'arrivent dans votre feuille de calcul.
- Renommer les étapes pour plus de clarté. « Type modifié », « Colonnes supprimées » et « Lignes filtrées » deviennent incompréhensibles après 20 étapes. Clic droit sur les étapes > Renommer pour décrire ce qu'elles font : « Supprimer les lignes vides » ou « Diviser le nom complet ».
Erreurs courantes
- Charger des millions de lignes inutilement. Filtrez les lignes avant de charger. Pendant le développement, utilisez Accueil > Conserver les lignes > Conserver les premières lignes et supprimez le filtre lorsque vous êtes prêt pour les données complètes.
- Ne pas corriger les types de données explicitement. Power Query devine les types mais peut se tromper. Sélectionnez chaque colonne et utilisez Accueil > Type de données pour définir correctement : Texte pour les identifiants, Décimal pour les montants, Date pour les dates. Les types incorrects sont la source n°1 des erreurs Power Query.
- Oublier que Power Query est sensible à la casse. Contrairement aux formules Excel, le langage M et les filtres de texte sont sensibles à la casse. « ABC » ne correspond pas à « abc » dans les filtres ou les fusions, sauf si vous appliquez d'abord une transformation majuscule/minuscule.
- Trop imbriquer les transformations en une seule étape. Utilisez des étapes séparées pour chaque transformation logique. Les étapes indépendantes sont plus faciles à déboguer, à réorganiser et à expliquer aux collègues.
Astuces avancées
- Combiner automatiquement les fichiers d'un dossier. Obtenir des données > À partir d'un fichier > À partir d'un dossier, puis cliquez sur Combiner > Combiner et transformer. Power Query applique vos transformations à chaque fichier du dossier. Déposez un nouveau fichier et actualisez — la fusion des rapports hebdomadaires est automatisée.
- Paramètres pour des requêtes dynamiques. Accueil > Gérer les paramètres vous permet de créer des valeurs nommées (chemin de fichier, plage de dates, seuil) que les utilisateurs peuvent modifier sans éditer la requête. Référencez les paramètres dans les étapes de filtre pour des rapports en libre-service.
- Gestion des erreurs avec Try Otherwise. Encapsulez les transformations avec
try ... otherwise ...:try Date.FromText([Colonne]) otherwise null. Empêche une cellule erronée de faire échouer toute la requête.