Comment Créer des colonnes personnalisées dans Power Query dans Excel - Guide Pratique

Apprenez à créer des colonnes personnalisées dans Power Query pour transformer et enrichir vos données avec des champs calculés, des manipulations de texte et de la logique conditionnelle. Cette compétence avancée permet aux professionnels des données de construire des modèles complexes sans quitter Excel, en combinant plusieurs colonnes en insights significatifs.

Pourquoi c'est important

Les colonnes personnalisées automatisent la transformation des données et réduisent le travail manuel des formules, ce qui permet une analytique plus rapide et une logique métier complexe directement dans votre pipeline de données. Cette compétence est essentielle pour l'analyse de données moderne.

Prérequis

•Compréhension de base de Power Query et de l'interface de l'éditeur de requêtes
•Familiarité avec les types de données (texte, nombres, dates) dans Excel
•Connaissance de la syntaxe de base du langage M ou volonté d'apprendre les concepts de formules

Instructions étape par étape

Ouvrir l'éditeur Power Query

Dans Excel, cliquez sur Données > Récupérer et transformer les données > Obtenir les données > D'autres sources > Lancer l'éditeur Power Query, ou cliquez avec le bouton droit sur une table et sélectionnez Modifier la requête.

Accéder à Ajouter une colonne personnalisée

Dans le ruban de l'éditeur Power Query, cliquez sur Ajouter une colonne > Colonne personnalisée (ou Appeler une colonne personnalisée dans les versions antérieures) situé au milieu du ruban.

Nommer votre colonne personnalisée

Dans la boîte de dialogue Colonne personnalisée, entrez un nom significatif dans le champ 'Nom de la nouvelle colonne' (ex. 'Nom complet', 'Revenu net') qui décrit vos données calculées.

Entrer le code du langage de formule M

Dans la zone de texte 'Formule de colonne personnalisée', écrivez votre expression M en utilisant les références de colonne entre crochets comme [Prénom] & " " & [Nom] pour la concaténation, ou des opérations mathématiques comme [Prix] * [Quantité].

Appliquer et vérifier les résultats

Cliquez sur OK pour créer la colonne, puis examinez les résultats dans la grille d'aperçu de la requête pour vous assurer que les calculs sont corrects avant de cliquer sur Fermer et charger.

Méthodes alternatives

Utiliser les colonnes conditionnelles

Au lieu d'écrire du code M, utilisez Ajouter une colonne > Colonne conditionnelle pour la logique si-alors-sinon avec une interface visuelle. C'est plus intuitif pour les débutants mais moins flexible pour les calculs complexes.

Dupliquer et modifier une colonne

Cliquez avec le bouton droit sur une colonne existante, sélectionnez Dupliquer, puis transformez-la en utilisant des fonctions intégrées comme Remplacer les valeurs ou Fusionner les colonnes.

Utiliser Appeler une fonction personnalisée

Créez des fonctions personnalisées réutilisables en tant que requêtes distinctes, puis appelez-les à partir de votre requête principale. Cette approche est idéale pour les transformations répétées.

Astuces et conseils

✓Utilisez la syntaxe [NomColonne] pour référencer les colonnes existantes; Power Query suggérera automatiquement les noms de colonnes correspondants au fur et à mesure que vous tapez.
✓Testez vos formules M sur un petit ensemble de données d'abord pour détecter les erreurs de syntaxe avant d'appliquer à de grandes tables.
✓Combinez plusieurs fonctions comme Text.Upper([Nom]) & " (" & Text.From([ID]) & ")" pour une manipulation avancée du texte.
✓Gardez la logique des colonnes personnalisées simple et lisible; les calculs complexes peuvent être divisés sur plusieurs colonnes personnalisées pour un débogage plus facile.

Astuces avancées

★Utilisez la syntaxe try-catch (try [Expression] otherwise "Error") pour gérer les valeurs nulles et éviter les défaillances de requête sur les cas limites.
★Exploitez DateTime.FromText() et Date.FromText() pour les conversions de dates avec un formatage automatique pour éviter les erreurs d'analyse courantes.
★Créez une requête de référence avec des fonctions M fréquemment utilisées comme bibliothèque, puis référencez-la lors de la création de colonnes personnalisées pour la cohérence.
★Surveillez les performances des requêtes en comparant les comptages de lignes avant/après; les colonnes personnalisées trop complexes peuvent ralentir les grands ensembles de données.

Résolution de problèmes

La colonne personnalisée affiche 'Erreur' ou des valeurs '#Erreur' dans les résultats

Vérifiez votre syntaxe M pour les crochets et guillemets correspondants. Survolez la cellule d'erreur pour voir le message d'erreur détaillé, puis utilisez try-otherwise pour gérer les valeurs nulles ou les incompatibilités de type de données.

La requête s'exécute très lentement après l'ajout d'une colonne personnalisée

Simplifiez la logique des colonnes personnalisées ou divisez les transformations complexes en plusieurs étapes. Envisagez d'appliquer des filtres avant l'étape de colonne personnalisée pour réduire le nombre de lignes traitées.

La référence de colonne [NomColonne] retourne null même si les données existent

Vérifiez l'orthographe exacte du nom de colonne et l'espacement (Power Query est insensible à la casse mais sensible aux espaces). Utilisez l'autocomplétion de la barre de formule pour assurer une syntaxe de référence correcte.

Les calculs de date ou de nombre produisent des résultats inattendus

Vérifiez les types de données des colonnes sources dans Power Query—les nombres/dates au format texte doivent d'abord être convertis en utilisant Value.FromText() ou Date.FromText() avant les opérations arithmétiques.

Les modifications apportées à la formule de colonne personnalisée ne sont pas mises à jour dans Excel

Assurez-vous de cliquer sur Fermer et charger (et non Fermer et charger vers) pour actualiser la table dans Excel, ou actualisez manuellement la requête avec Données > Actualiser tout.

Formules Excel associées

Text.Upper Text.Lower Text.Concat Date.Year Number.Round List.Contains Table.AddColumn try-otherwise

Questions fréquentes

Quelle est la différence entre Colonne personnalisée et Colonne conditionnelle?

Colonne personnalisée vous permet d'écrire des formules M pour toute transformation (mathématiques, texte, dates, logique complexe). Colonne conditionnelle fournit un constructeur si-alors-sinon visuel pour la logique de décision simple sans codage. Utilisez Colonne personnalisée pour la flexibilité et Colonne conditionnelle pour la simplicité.

Puis-je référencer plusieurs colonnes dans une seule colonne personnalisée?

Oui, vous pouvez combiner des colonnes illimitées en utilisant l'opérateur & pour le texte, + pour les nombres, ou des fonctions imbriquées. Par exemple: [Prénom] & " " & [Nom] combine deux colonnes, ou [Prix] * [Quantité] effectue une multiplication entre colonnes.

Comment gérer les erreurs dans les colonnes personnalisées pour les données nulles ou manquantes?

Utilisez la syntaxe try-otherwise: try [Colonne] otherwise "N/A" ou try [Colonne1] / [Colonne2] otherwise 0. Cela empêche les défaillances de requête et affiche une valeur par défaut lorsque les calculs rencontrent des erreurs ou des données manquantes.

Les colonnes personnalisées peuvent-elles référencer des cellules d'autres requêtes ou feuilles Excel?

Les colonnes personnalisées ne peuvent référencer que les colonnes de la même requête. Pour utiliser des données externes, fusionnez les requêtes en utilisant Fusionner les requêtes dans l'éditeur Power Query, puis référencez les colonnes fusionnées dans votre formule personnalisée.

Quelles fonctions du langage M sont les plus utiles pour les colonnes personnalisées?

Les fonctions courantes incluent Text.Upper/Lower (conversion de casse), Text.Length (compte des caractères), Date.Year/Month/Day (extraction de date), Number.Round (arrondi), et List.Contains (vérification des valeurs). La documentation de référence de formule M de Microsoft fournit la bibliothèque de fonctions complète.