Séparez les nombres et le texte dans une colonne à l’aide de Power Query
Il s’agit d’un cas typique de BI en libre-service avec des données Excel.
Il y a quelques jours, un client m’a posé la question suivante :
J’ai une feuille Excel avec des chiffres et du texte dans une colonne. Je souhaite importer cette feuille dans Power BI et effectuer une analyse sur les nombres de cette colonne.
Comment puis-je séparer les chiffres du texte dans cette colonne ?
N’oubliez pas que j’ai également besoin du texte dans cette colonne.
Je n’avais jamais été dans cette situation, alors j’ai commencé par utiliser la technique que je connaissais.
J’ai créé un Dummy Excel avec le même problème, qui ressemble à ceci :
Pour créer un PoC, j’ai d’abord chargé ces données dans une base de données SQL Server pour voir comment les résoudre là-bas.
Résoudre le problème en utilisant SQL
T-SQL possède deux fonctions utiles dans de tels scénarios :
- TRY_CONVERT()
- Celui-ci essaie de convertir une valeur en un type de données cible. En cas d’échec, il renvoie NULL.
- ISNUMÉRIQUE()
- Vérifie si une valeur est une valeur numérique. Si oui, il renvoie 1. Sinon, 0.
Sur la base de ces connaissances, j’ai écrit une requête pour séparer les valeurs en deux colonnes. Un avec les chiffres et un avec le texte :
SELECT [Values]
,TRY_CONVERT(decimal(18, 5), [Values]) AS [Number]
,IIF(ISNUMERIC([Values]) = 0, [Values], NULL) AS [Text]
FROM [dbo].[MixedValues];Le résultat est le tableau suivant :
Si vous regardez attentivement, vous voyez que la ligne 17 est reconnue comme un texte.
C’est parce que le numéro contient un espace.
J’y reviendrai plus tard.
Passer à Power Query – Essayer IsNaN()
Maintenant, j’ai chargé Excel dans Power Query.
J’ai défini la colonne comme Texte et j’ai commencé à travailler sur ce défi.
La première tentative utilise le Numéro.IsNaN() fonction.
Cette fonction renvoie vrai si la valeur est NaN. « NaN » est un espace réservé pour non applicable, par exemple, en raison d’une division par 0.
J’ai essayé cela pour déterminer si un texte est équivalent à NaN.
Voici le code M de la colonne calculée :
if Number.IsNaN([Value]) = true
then [Value]
else nullLe résultat m’a surpris :
Étrangement, le résultat est qu’il ne peut pas convertir un nombre en nombre.
Je suppose que cela se produit parce que le type de données de la colonne est du texte.
Ensuite, j’ai essayé de convertir la colonne en nombre et d’appliquer la fonction IsNaN() au résultat :
if Number.IsNaN(Number.From([Value])) = false
then Number.From([Value])
else nullDésormais, les nombres sont convertis en nombres, mais les valeurs de texte génèrent une erreur :
Maintenant, la logique fonctionne pour les nombres.
Mais la conversion échoue pour les lignes contenant du texte. Cela entraîne des lignes contenant des erreurs.
Essayer Value.Is() dans Power Query
Essayons une autre fonction : Valeur.Est()
Cette fonction vérifie si une valeur est compatible avec un type de données.
Cela devrait être équivalent à la fonction ISNUMERIC() présentée ci-dessus :
if Value.Is([Value], Number.Type) = true
then Number.From([Value])
else nullMalheureusement, cette fonction n’a pas non plus renvoyé le résultat attendu :
Lorsque j’ai essayé la même approche que ci-dessus, en convertissant d’abord la valeur en nombre, j’ai obtenu le même résultat qu’avant :
Par conséquent, je soupçonne que la fonction Value.Is() attend un type de données numérique, mais cela n’a aucun sens pour moi.
À ce stade, je n’avais pas le temps de faire des recherches plus approfondies, car je manquais de temps.
Il était temps de changer d’approche.
Concept de commutation
J’ai maintenant exploré comment détecter les erreurs dans Power Query.
Mon idée était la suivante : et si je pouvais détecter l’erreur de conversion et utiliser ces informations ?
J’ai trouvé cette page avec des informations utiles: Erreurs – PowerQuery M | Microsoft Apprendre
De là, j’en déduis cette expression :
try Number.From([Value]))Après avoir ajouté une colonne calculée avec cette expression, j’ai obtenu ce résultat :
J’étais optimiste, car je n’ai pas eu d’erreur.
Ensuite, il s’agissait d’élargir les enregistrements :
Je n’avais pas besoin des colonnes Erreur, seulement de la colonne Valeur.
Voici le résultat après expansion :
Notez que j’ai renommé les colonnes directement dans la fonction ExpandRecordColumn().
Sinon, j’aurais eu une colonne nommée [Value.1].
Ce résultat était le premier où je n’obtenais aucune erreur.
Maintenant, j’ai ajouté une colonne calculée pour vérifier si la nouvelle colonne est vide. Si oui, alors la colonne Valeur d’origine contenait un texte :
if [Numeric Value] = null then [Value] else nullIci, le résultat :
Après avoir défini les types de données corrects et supprimé la colonne Valeur d’origine, j’ai obtenu ce tableau :
Gérer le numéro avec des blancs
Mais nous avons toujours la ligne 17, qui contenait un numéro avec un blanc.
Comment ai-je géré cela ?
L’approche la plus simple consistait à supprimer tout espace vide de la colonne Valeur :
Mais j’ai dû ajouter cette étape avant de commencer les étapes de séparation des deux types de valeur :
Après avoir ajouté cette étape, la ligne 17 est reconnue comme un nombre et stockée correctement.
Voici les données après les avoir chargées dans Power BI :
Mais cela ne fonctionnait que si les valeurs du texte étaient des mots simples. Cela ne fonctionnait pas lorsque des phrases ou plusieurs mots y étaient stockés.
Conclusion
Ce fut une excursion fascinante sur la façon dont Power Query, ou langage M, fonctionne avec les types de données.
Je ne suis toujours pas sûr des causes des erreurs.
Mais j’ai appris à gérer les erreurs ou à utiliser l’appel try et à gérer la sortie.
Cela a été très utile.
Quoi qu’il en soit, comme vous le voyez avec la valeur originale de la ligne 17, la qualité des données est primordiale.
J’ai un autre client où des utilisateurs de différents pays travaillent sur le même fichier Excel avec leurs propres formats numériques.
C’est un cauchemar car Excel est très tolérant envers les types de données. Il accepte tout, même lorsque la colonne est formatée sous forme de nombre.
Dans cette situation, je dois forcer les utilisateurs à utiliser les options de formatage d’Excel pour garantir que les nombres sont systématiquement reconnus comme tels.
Sans cela, je n’ai aucune chance d’importer ces données dans Power BI sans beaucoup d’efforts pour nettoyer les chiffres.
Et soyez assuré que les utilisateurs trouvent toujours un moyen de gâcher les chiffres dans Excel.
Références
Les données sont créées avec des nombres et des mots aléatoires.
Voici la référence pour le M-Langage : Référence du langage de formule Power Query M – PowerQuery M | Microsoft Apprendre
