Où Power Query indique des erreurs au chargement

Pour établir la suite de commandes permettant le chargement des données, Power Query s’appuie sur un échantillon de lignes. Pour chaque colonne, les données vont être évaluées, et le type de la colonne sera déduit (c’est l’étape automatique « Type modifié »). Les erreurs les plus courantes viennent du fait que des données des différents types se trouvent dans la même colonne. C’est le cas que nous allons illustrer, mais la technique montrée ici marche quelque soit l’erreur.

En ce qui concerne la correction de l’erreur, l’idée ici est de fournir à la personne en charge des données – admin de la base, propriétaire du fichier Excel, … – les moyens d’effectuer la correction à la source. Notez que ces erreurs sont très rares, voire inexistante, si la source est une base de données. Elles sont beaucoup plus fréquentes pour des sources moins structurées (feuilles Excel, …).

Une autre précision importante : les données générant les erreurs peuvent se trouver dans l’échantillon, ou n’apparaître qu’au moment du chargement complet des données. Ça fait une petite différence sur les premiers moments du processus.

La première étape est de se connecter à la source (Excel ici), et de choisir l’option Transformer les données, recommandée avec les sources locales.  

1^er cas : les erreurs sont dans l’échantillon

Si dans l’échantillon les données de la colonne sont homogènes, Power Query affectera le plus souvent un type texte, nombre (entier ou décimal), ou date. En revanche, si les données de l’échantillon présentent des types différents ou incohérent (ex, du texte dans une colonne majoritairement numérique, ou une date erronée dans une colonne date), la colonne est typée ABC123 :

Vérifier le type automatique choisi par Power Query est toujours important, et nous allons donc modifier celui des trois dernières colonnes. C’est là que l’erreur va apparaître :

Notez que le 12 a été converti en texte, et que la date sans les barres obliques a été automatiquement corrigée. Il reste en revanche 4 erreurs.

L’objectif final est d’afficher ces 4 lignes dans un tableau dans le rapport, et d’indiquer clairement ce qui a causé l’erreur.

2^ème cas : les erreurs ne sont pas dans l’échantillon

Dans ce cas, les trois dernières colonnes vont être typées correctement, mais l’erreur apparaîtra lorsque nous cliquons sur Fermer & appliquer :

C’est le même message qui apparaîtra si vous cliquez directement sur Charger, plutôt que passer par l’étape Transformer les données.

Dans ces deux cas, cliquez sur Afficher les erreurs ne vous mènera pas très loin.

Comment éviter les messages d’erreurs au chargement

Retournons dans Power Query. La démarche que je vous propose ici a été détaillée par Reza Rad, de Radacad.

Cette méthode repose sur la création de 2 requêtes/tables : la première, celle où toutes les lignes sont « propres », la seconde où ne resteront que les lignes en erreur.

Je vais donc ici référencer deux fois ma table transactions2 (clic-droit sur la table puis Référence), renommer la première table transactions2 originale, la deuxième transactions2, la troisième transactions2 erreurs, désactiver le chargement de transactions2 originale (clic-droit sur la table puis décocher Activer le chargement) :

Pour transactions2, nous allons supprimer toutes les erreurs pour éviter d’avoir le message au moment du chargement. Pour cela, dans la mesure où nous ne savons pas à l’avance dans quelles colonnes se trouveront les erreurs, nous allons utiliser la fonction Supprimer les erreurs au niveau de la table elle-même – c’est le menu déroulant affichant le symbole d’une table tout à fait à gauche des colonnes :

Cette table va donc se charger sans message d’erreur, avec, dans notre exemple, 4 lignes en moins.

Comment capter les erreurs

Passons maintenant à transactions2 erreurs : l’idée ici c’est d’obtenir en définitive une table de 4 lignes, indiquant l’identifiant de la ligne (ici, le numéro client), le type de l’erreur et sa cause :

Pour ça, nous allons passer par une série d’étapes (ça peut sembler long et complexe, mais en réalité c’est très rapide) :

Dépivoter toutes les colonnes sauf la colonne identifiant (numéro client pour nous, mais vous pouvez aussi utiliser n’importe quelle autre colonne permettant d’identifier sans trop de difficulté la ligne) – clic-droit sur colonne identifiant, puis Dépivoter les autres colonnes

Conserver uniquement les lignes de la colonne Valeur en erreur – sélectionner cette colonne puis dans le ruban Accueil, Conserver les lignes et Conserver les erreurs

A ce stade là, le résultat n’est pas encore exploitable :

L’étape clé se met en place maintenant : nous allons créer une nouvelle colonne dans laquelle nous allons « tester » Valeur, pour forcer la génération de l’erreur. Nous allons donc utiliser la fonction Ajouter une colonne personnalisée (ruban Ajouter une colonne, puis Colonne personnalisée), puis saisir la formule try [Valeur] :

La fonction M try est utilisée pour évaluer une expression et déterminer si elle génère une erreur.

Le résultat est une colonne où nous avons cette fois-ci des enregistrements (Record) et où nous retrouvons (à droite du nom de la nouvelle colonne), le symbole permettant de déplier (ou de développer) l’enregistrement :

Il est nécessaire de déplier deux fois (cela est dû à la structure de la donnée en M). La première fois, ne garder que la colonne Error :

Puis déplier à nouveau la colonne Error, en sélectionnant cette fois-ci toutes les colonnes :

Pour obtenir :

Et pour terminer, il faut supprimer la colonne originale Valeur, qui est en erreur :

Vous pouvez maintenant charger les deux tables et créer votre rapport d’erreur au chargement :