juin 3, 2022Lire l'article
Tout fonctionnait vendredi. Lundi, l'actualisation échoue avec un message du type « Expression.Error : la colonne "Montant" de la table est introuvable ». Entre-temps, quelqu'un a renommé « Montant » en « Montant HT » dans le fichier source. Une seule cellule modifiée, et tout le rapport est à terre.
La réparation prend deux minutes : il faut retrouver l'étape qui cite l'ancien nom et la corriger. Mais si cela vous arrive régulièrement, le vrai sujet est ailleurs : vos requêtes sont fragiles par construction, et il existe des façons simples de les rendre tolérantes aux changements des colonnes dans la source.
Dans la grande majorité des cas, l'étape fautive s'appelle Type modifié. C'est la piste à suivre en premier.
Votre situation est un peu différente ? Si vous empilez les fichiers d'un dossier et qu'une colonne se retrouve à moitié vide sans aucun message, le problème vient de fichiers sources qui n'ont pas les mêmes intitulés. Nous le traitons dans un article dédié : [LIEN INTERNE : Comment combiner des fichiers qui n'ont pas les mêmes noms de colonne avec Power Query ?].
Le scénario est identique avec Power Query dans Excel, l'outil étant commun aux deux logiciels de Microsoft. La différence tient à l'interface : dans le classeur Excel, tout passe par l'onglet Données. Le bouton Requêtes et connexions ouvre le volet de droite, et un clic droit sur une requête en échec permet de la modifier dans le même éditeur. Que vous choisissiez de charger les données vers Excel dans un tableau ou seulement dans le modèle de données, les requêtes restent les mêmes.
Le symptôme, lui, se voit ailleurs : vous cliquez sur Actualiser tout, un tableau Excel reste figé sur les anciennes valeurs, et le tableau croisé dynamique bâti dessus (ou le modèle Power Pivot qu'il interroge) ne bouge plus. Le TCD semble vivant, mais ses chiffres datent de la dernière actualisation réussie. Avec Microsoft 365, une infobulle rouge sur la requête, dans le volet, signale le blocage. Pensez à la regarder avant de faire confiance aux données dans Excel, surtout si vous utilisez Power Query.
Beaucoup d'utilisateurs Excel ont adopté Power Query précisément pour automatiser ce qu'ils faisaient à la main : importer plusieurs tables, regrouper les feuilles en une seule table, puis remplacer les RECHERCHEV en cascade et les macros VBA qui recopient les données d'une feuille de calcul vers une nouvelle feuille chaque mois. Le gain d'automatisation est réel, mais la contrepartie est là : une chaîne automatisée qui dépend des noms de colonne doit être protégée, exactement comme dans Power BI.
Infographie des équivalences Excel, extrait du livre Microsoft Power BI en images, p. 37.
Chaque clic dans PowerQuery écrit une ligne de code, et cette ligne cite les colonnes par leur nom :
= Table.TransformColumnTypes(Source, {{"Date", type date}, {"Montant", type number}, {"Client", type text}})Cette étape, l'outil l'ajoute tout seul dès l'import, sans vous demander votre avis. Elle fixe le type de données de chaque colonne, et elle cite donc toutes les colonnes du fichier source. Il suffit qu'une colonne change de nom pour que tout s'arrête : le moteur cherche une colonne contenant les montants sous un nom qui n'existe plus.
Les nouvelles colonnes posent le problème inverse, plus sournois : aucun message, mais la colonne ajoutée dans la source n'apparaît jamais, parce qu'une étape Supprimer d'autres colonnes a figé la liste de celles que vous gardez. C'est le revers de la médaille de transformer des données par clics : l'outil enregistre automatiquement les noms, pas l'intention.
Supprimez l'étape Type modifié créée à l'import, et recréez-la à la fin, uniquement sur les colonnes que vous utilisez réellement. Moins une étape cite de colonnes, moins elle a de raisons de casser. Profitez-en pour supprimer les lignes inutiles (totaux, lignes vides) et les colonnes superflues avant de typer : les transformations qui restent portent sur une table plus légère.
Vous pouvez aussi désactiver cette détection automatique une fois pour toutes, dans Fichier > Options > Chargement des données > Détection du type (dans Excel : Données > Obtenir des données > Options de requête).
« Premiers gestes sur Power Query », extrait du même livre, p. 118.
En vidéo : Comment supprimer les étapes inutiles dans Power Query ?
[LIEN YOUTUBE À AJOUTER]
L'onglet Accueil propose deux boutons qui semblent identiques et ne le sont pas :
Aucun n'est meilleur que l'autre. Le second protège le reste du traitement contre les surprises ; le premier laisse entrer les nouveautés. L'important est de choisir en connaissance de cause, dès le début.
Plusieurs fonctions acceptent un argument supplémentaire, MissingField.Ignore, qui signifie : « si la colonne n'existe pas, continue sans planter ». Il suffit de l'ajouter dans la barre de formule :
= Table.RenameColumns(Source, {{"Montant HT", "Montant"}}, MissingField.Ignore)
= Table.SelectColumns(Source, {"Date", "Montant", "Client"}, MissingField.Ignore)
= Table.RemoveColumns(Source, {"Commentaire"}, MissingField.Ignore)Variante utile pour Table.SelectColumns : MissingField.UseNull crée la colonne manquante, remplie de valeurs vides. Les étapes suivantes continuent de fonctionner, et vous pouvez même ajouter une colonne calculée par-dessus sans risque.
Attention, c'est une arme à double tranchant. Avec MissingField.Ignore, votre traitement ne casse plus, mais il ne vous prévient plus non plus. Si « Montant » disparaît, le rapport s'actualise sans broncher et affiche des chiffres faux. Réservez cette option aux colonnes secondaires, ou accompagnez-la du contrôle décrit plus bas.
« Les erreurs à éviter sur Power Query », extrait du même livre, p. 119.
Gérer plusieurs variantes d'un même intitulé. Quand les variantes se multiplient (« Montant », « Montant HT », « Mt HT »), listez-les dans une table de correspondance à deux colonnes, ancien nom et nouveau nom, et laissez le moteur la parcourir :
= Table.RenameColumns(Source, Table.ToRows(Correspondance), MissingField.Ignore)Cette table de correspondance peut vivre dans une feuille du classeur, ce qui évite de fusionner deux tables ou de rouvrir le code : une nouvelle variante se règle en ajoutant une ligne.
Renommer par position. Si la première colonne est toujours la date, quel que soit son intitulé :
= Table.RenameColumns(Source, {{Table.ColumnNames(Source){0}, "Date"}})Typer sans citer de noms. Pour passer en nombre toutes les colonnes sauf quelques-unes :
= Table.TransformColumnTypes(Source, List.Transform(
List.Difference(Table.ColumnNames(Source), {"Date", "Client"}),
each {_, type number}))Ces trois techniques s'écrivent dans l'éditeur avancé. Elles servent autant pour analyser des données issues d'un fichier CSV que pour manipuler une table SQL Server dont le schéma évolue : dans les deux cas, le code ne cite plus de noms figés.
En vidéo : Comment créer une nouvelle requête dans Power Query et y injecter du code M ?
Un code tolérant traite le symptôme. La cause, c'est une source de données instable. Trois mesures simples :
Manquantes = List.Difference({"Date", "Montant", "Client"}, Table.ColumnNames(Source)),
Controle = if List.IsEmpty(Manquantes) then Source
else error "Colonnes manquantes dans le fichier source : " & Text.Combine(Manquantes, ", ")Le jour où « Montant » est renommé, l'actualisation s'arrête avec un message que n'importe qui peut comprendre et transmettre. Cette inspection systématique évite le plus fastidieux : découvrir le décalage dans un TCD trois semaines plus tard.
Pourquoi le message n'apparaît-il qu'à l'actualisation ? Power Query travaille sur un aperçu mis en cache. Tant que vous n'actualisez pas, il vous montre l'ancienne version des données.
L'ordre des colonnes a changé dans la source, est-ce grave ? Non, tant que vos étapes citent les colonnes par leur nom. C'est seulement si vous avez utilisé le renommage par position qu'il faut y prêter attention.
Faut-il tout rendre dynamique ? Non. Un code trop tolérant devient illisible et masque les anomalies. Protégez les points qui cassent réellement, et laissez le reste simple.
Le problème existe-t-il aussi côté DAX ? Oui, sous une autre forme : une mesure DAX qui référence une colonne supprimée passe en erreur dans le modèle. Mais la cause première reste la même, et se règle au même endroit, à l'import.
