Colonne personnalisée ou colonne conditionnelle : comment choisir

Dans l’éditeur Power Query, deux commandes se ressemblent au point qu’on prend souvent la première venue : Ajouter une colonne conditionnelle et Colonne personnalisée. Les deux affichent une valeur qui dépend d’une condition, les deux produisent un texte, un nombre ou une date selon les lignes. La différence ne se voit pas tant que la logique reste simple : un seul critère, une seule colonne à tester. Elle devient un vrai problème le jour où deux colonnes doivent se combiner dans une même condition, ce que la boîte de dialogue ne sait tout simplement pas faire.

Ce n’est pas une question de goût. Les deux commandes ne génèrent pas le même code M, et la colonne conditionnelle a une limite dure qui oblige tôt ou tard à basculer vers la colonne personnalisée. Autant savoir laquelle choisir avant d’avoir construit six clauses qui ne suffiront pas.

La colonne conditionnelle : ce qu’elle génère

Depuis l’onglet Ajouter une colonne, la commande Colonne conditionnelle ouvre une boîte de dialogue à quatre champs par ligne : la colonne à tester, un opérateur, une valeur de comparaison, et la sortie si la condition est vraie. Un bouton Ajouter une clause permet d’empiler plusieurs règles, évaluées de haut en bas, la première qui correspond l’emporte, et une clause Sinon finale couvre tout le reste. Pour classer un délai de paiement en trois niveaux, la boîte de dialogue génère ceci :

Table.AddColumn(#"Type modifié", "Niveau de relance", each
    if [JoursRetard] > 30 then "Relance"
    else if [JoursRetard] > 0 then "Rappel"
    else "À jour"
)

L’opérateur proposé dans la liste déroulante n’est pas limité à l’égalité : on trouve aussi différent de, plus grand que, plus petit que, contient, commence par et termine par (chacun avec sa négation). De quoi couvrir la plupart des tests sur une seule colonne sans écrire une ligne de M.

Sa limite : une clause, une colonne, un opérateur, une valeur

Chaque clause de la boîte de dialogue teste exactement une colonne, avec un seul opérateur, contre une seule valeur. Il n’existe aucun moyen d’exprimer un ET ou un OU entre deux colonnes à l’intérieur d’une même clause. Ce n’est pas un oubli dans une version ancienne : la fenêtre n’a tout simplement pas ce champ.

Le cas qui la fait buter arrive vite : on veut une relance différente selon deux colonnes à la fois, par exemple un segment client et un nombre de jours de retard. Le cas Segment = "Grand compte" et JoursRetard > 30 doit produire « Relance directionnelle », les autres cas continuant de suivre les paliers vus plus haut. Aucune combinaison de clauses ne reproduit ce ET : chaque ligne de la boîte de dialogue ne peut regarder qu’une colonne, jamais deux en même temps.

La colonne personnalisée : le même besoin en M

La commande Colonne personnalisée, dans le même onglet, ouvre un éditeur vide où l’on écrit directement l’expression M. Le même besoin s’y exprime avec l’opérateur logique and, disponible nativement dans le langage :

Table.AddColumn(#"Colonne conditionnelle ajoutée", "Niveau de relance corrigé", each
    if [Segment] = "Grand compte" and [JoursRetard] > 30 then "Relance directionnelle"
    else if [JoursRetard] > 30 then "Relance standard"
    else if [JoursRetard] > 0 then "Rappel"
    else "À jour"
)

Les deux commandes appellent en réalité la même fonction, Table.AddColumn. Argument par argument :

• table : la table source, ici le résultat de l’étape précédente (#"Colonne conditionnelle ajoutée").
• newColumnName : le nom de la colonne créée, entre guillemets.
• columnGenerator : une fonction évaluée pour chaque ligne, introduite par each. C’est ici, et seulement ici, que la colonne personnalisée diffère vraiment : on peut y écrire n’importe quelle expression M valide, and, or, un appel à une fonction (Text.Contains, Date.From), ou une référence à une requête entière, alors que la boîte de dialogue ne sait générer qu’une chaîne de if à une seule colonne par clause.
• columnType : paramètre optionnel, le type à assigner à la colonne (type text, type number…). La boîte de dialogue ne le renseigne jamais ; l’étape Type modifié qui suit s’en charge séparément, ce qui fait deux étapes au lieu d’une si on l’ajoute à la main.

Rien n’empêche non plus de corriger à la main le code généré par la colonne conditionnelle : un clic sur l’engrenage de l’étape rouvre la boîte de dialogue, mais rien n’interdit d’éditer directement la formule dans la barre de formule si l’interface ne suit plus. Le morceau de code ci-dessus est exactement ce résultat : parti d’une colonne conditionnelle, complété à la main avec and.

Le piège commun aux deux : la valeur vide qui casse la ligne, pas la requête

Ce piège touche indifféremment les deux méthodes, puisqu’elles compilent toutes les deux vers un if. En M, les opérateurs de comparaison relationnels (>, <, >=, <=) renvoient null, et non false, dès que l’un des deux côtés vaut null : [JoursRetard] > 30 devient null si la cellule est vide, pas false. Un if qui reçoit null comme condition ne le traite pas comme faux : il lève l’erreur Expression.Error : We cannot convert the value null to type Logical.

Cette erreur ne fait pas planter la requête entière : elle apparaît uniquement sur les cellules concernées, affichées « Erreur » dans l’aperçu, et se propage seulement si une étape suivante essaie de lire cette valeur (un filtre, une agrégation, un changement de type). Le correctif est le même dans les deux cas, traiter le vide en premier :

if [JoursRetard] = null then "Non renseigné"
else if [JoursRetard] > 30 then "Relance"
else if [JoursRetard] > 0 then "Rappel"
else "À jour"

Ou, pour une seule clause à corriger plutôt qu’une chaîne entière, ajouter la garde directement dans le test : [JoursRetard] <> null and [JoursRetard] > 30. Une comparaison d’égalité (= ou <>) avec null renvoie bien true ou false, contrairement aux opérateurs d’ordre : c’est ce qui rend cette garde possible.

Comment choisir

Si le test porte sur une seule colonne, avec un opérateur simple et une sortie fixe, la colonne conditionnelle est plus rapide à construire et plus facile à relire par quelqu’un qui ne lit pas le M. Dès qu’il faut combiner deux colonnes dans une même condition, appeler une fonction, ou construire une sortie qui n’est pas une valeur littérale, la colonne personnalisée évite l’aller-retour : commencer par la boîte de dialogue pour découvrir sa limite deux minutes plus tard fait perdre plus de temps que ça n’en fait gagner.

Disponibilité

Les deux commandes existent depuis les premières versions de Power Query, y compris le module complémentaire pour Excel 2010 et 2013, et nativement depuis Excel 2016. La fonction Table.AddColumn et les opérateurs and/or font partie du langage M lui-même : rien dans cet article n’exige Microsoft 365.

Ce que ça change

Une fois qu’on sait où se situe la limite, le choix se fait avant d’ouvrir la boîte de dialogue plutôt qu’après avoir buté dessus. La documentation complète des éléments utilisés : Table.AddColumn, Ajouter une colonne conditionnelle et les opérateurs du langage M.