Capter et afficher les erreurs lors du chargement de données

Proposé par André Meyer-Roussilhon

20 avril 2021

Data club

chargement de données | gestion d'erreurs | M

Restez à l'écoute, abonnez-vous à la newsletter (2 fois / mois)

15 + 11 =

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.  

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

Les champs générant des possibles erreurs sont affichés en rouge dans l’image ci-dessus

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 :

Pour le rapport, une carte comptant le nombre d’identifiants, et un simple tableau

Articles associés :

Le point sur mon implication dans Power BI

Le point sur mon implication dans Power BI

Depuis juillet 2109, date à laquelle mon premier livre sur Power BI est sorti, il s’est passé beaucoup de choses, et plus encore au cours des derniers mois. Dans ce billet, je veux vous proposer un tour d’horizon des ressources que j’ai publiées sur le thème de Power BI, et de ce que je prévois pour la suite.

Utilisez M : des exemples simples et concrets

Ce billet fait suite à celui consacré à la découverte de l’environnement M. Dans celui-ci , nous voyons les premiers exemples utiles d’utilisation de ce code, pour nettoyer une source de données

Une pincée de M

Le but des billets que je vais consacrer au langage M, qui se cache derrière Power Query, est d’en proposer une découverte, de mettre en perspective l’utilité d’apprendre ce langage, et de dérouler un nombre de cas concrets directement utiles