Power Query dans Excel : importer et nettoyer

Le même outil que dans Power BI

Power Query est intégré à Excel pour Windows depuis plusieurs versions et à Microsoft 365 ; Excel pour Mac et Excel pour le web en proposent une partie. C’est le même moteur que dans Power BI Desktop, avec le même langage M : tout ce que vous apprenez ici se réutilise tel quel dans le cours BI2, et inversement. Seuls l’emplacement des commandes et les options de chargement diffèrent.

Pourquoi l’utiliser dans Excel plutôt que copier-coller ? Parce que le nettoyage devient une recette enregistrée : le mois prochain, vous remplacez le fichier source, cliquez sur Actualiser, et toutes les étapes sont rejouées. Plus de manipulations manuelles à refaire, plus d’oubli.

Importer un fichier

Dans l’onglet Données :

• Obtenir des données > À partir d’un fichier > À partir d’un texte/CSV (ou le bouton À partir d’un fichier texte/CSV) ;
• choisissez le fichier : un aperçu s’ouvre, avec l’origine du fichier (encodage, 65001 pour UTF-8), le délimiteur et la détection des types ;
• cliquez sur Transformer les données pour ouvrir l’éditeur, plutôt que Charger qui importerait sans nettoyer.

L’éditeur est identique à celui de Power BI : volet des requêtes, aperçu, étapes appliquées à droite, barre de formule (onglet Affichage), et les options Qualité, Distribution et Profil de la colonne pour diagnostiquer.

Nettoyer dans le bon ordre

Un fichier exporté d’un logiciel contient souvent des espaces, des majuscules incohérentes, des lignes vides, des doublons et des valeurs manquantes. Ordre recommandé :

1. Structure : vérifier les en-têtes promus, Accueil > Supprimer les lignes > Supprimer les lignes vides ;
2. Texte : sélectionnez les colonnes texte, Transformer > Format > Supprimer les espaces, puis Mettre en majuscules chaque mot ;
3. Doublons : sélectionnez la colonne clé (ou toutes les colonnes), Supprimer les lignes > Supprimer les doublons ;
4. Types : clic droit sur l’en-tête > Modifier le type > Utilisation des paramètres régionaux si le format diffère de celui du poste (dates jj/mm/aaaa, décimales) ;
5. Valeurs manquantes : Transformer > Remplacer les valeurs, en remplaçant null par 0 quand cela a un sens métier.

Normaliser le texte avant de supprimer les doublons est essentiel : « Paris » et « paris » ne sont pas des doublons pour Power Query tant que la casse diffère.

let
    Source = Csv.Document(File.Contents("C:\Formation\clients_brut.csv"), [Delimiter = ";", Encoding = 65001]),
    Entetes = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    SansVides = Table.SelectRows(Entetes, each [ID Client] <> "" and [ID Client] <> null),
    Texte = Table.TransformColumns(SansVides, {{"Nom", each Text.Proper(Text.Trim(_)), type text},
                                              {"Ville", each Text.Proper(Text.Trim(_)), type text}}),
    SansDoublons = Table.Distinct(Texte, {"ID Client"}),
    Types = Table.TransformColumnTypes(SansDoublons, {{"ID Client", Int64.Type},
             {"Date inscription", type date}, {"CA", type number}}, "fr-FR"),
    CAComplet = Table.ReplaceValue(Types, null, 0, Replacer.ReplaceValue, {"CA"})
in
    CAComplet

Vous n’avez pas à écrire ce code : l’interface le génère. Mais le lire dans Accueil > Éditeur avancé permet de vérifier l’ordre et de renommer les étapes de façon parlante.

Choisir où charger

Accueil > Fermer et charger crée un tableau structuré sur une nouvelle feuille. Fermer et charger dans… offre le choix :

Option	Quand l’utiliser
Tableau	le résultat doit être visible, filtré, utilisé dans des formules
Rapport de tableau croisé dynamique	on veut directement un TCD
Créer uniquement la connexion	requête intermédiaire, réutilisée par d’autres requêtes
Ajouter ces données au modèle de données	gros volumes, plusieurs tables reliées, mesures DAX (leçons 3 et 4)

Une feuille Excel est limitée à 1 048 576 lignes ; le modèle de données n’a pas cette limite par feuille, ce qui en fait la bonne destination pour des volumes importants.

Le volet Requêtes et connexions (Données > Requêtes et connexions) liste les requêtes, le nombre de lignes chargées et les éventuelles erreurs. Un clic droit > Charger dans… permet de changer la destination après coup.

Ne jamais modifier le tableau chargé à la main

Le tableau produit par une requête est réécrit à chaque actualisation : une correction tapée dedans sera perdue. Toute correction se fait dans la requête. Vous pouvez en revanche ajouter des colonnes de formules à droite du tableau chargé : Excel les conserve et les recopie sur les nouvelles lignes.