Power Query transforme vos montants : 6 contrôles avant de changer les séparateurs

Un montant importé comme 1 234,56, 1,234.56 ou 1.234,56 ne doit pas être « réparé » par une suite de remplacements au hasard. Dans Power Query, commencez par conserver la valeur brute en texte, identifiez la convention du fichier source, puis convertissez la colonne avec une culture explicite. Enfin, contrôlez les erreurs, les extrêmes et les totaux avant de charger le résultat.

Le point important est simple : la virgule et le point n’ont pas une signification universelle. La chaîne 1,234 peut représenter mille deux cent trente-quatre dans un export américain, ou un nombre décimal proche de un dans un fichier français. Sans connaître la convention de la source, Power Query — comme un humain — ne peut pas lever cette ambiguïté de façon fiable.

Pourquoi un montant peut changer sans produire d’erreur

Power Query attribue un type à chaque colonne. Pour les sources non structurées comme les fichiers CSV ou texte, il peut détecter automatiquement les types en observant les valeurs ; Microsoft indique que cette détection est activée par défaut et peut ajouter une étape Type modifié. La culture du document sert ensuite à interpréter les textes lors de leur conversion en nombres, dates ou autres types. Microsoft Learn — Types de données dans Power Query

Une mauvaise interprétation ne provoque pas toujours une cellule en erreur. Elle peut produire un nombre valide mais faux. Par exemple, selon la convention retenue, le caractère , est lu comme séparateur décimal ou comme séparateur de milliers. C’est le cas le plus dangereux : l’actualisation termine, le tableau paraît propre, mais les montants sont décalés.

Microsoft distingue aussi le nombre décimal (type number) du nombre décimal fixe (Currency.Type). Le premier utilise une représentation à virgule flottante ; le second conserve quatre décimales fixes et peut être pertinent lorsque la précision attendue le justifie. Le choix du type vient après l’interprétation correcte du texte, et non à sa place. Microsoft Learn — Types numériques Power Query

La fiche LOCALE 6 CONTRÔLES

Avant de modifier une requête, relevez ces six éléments. Cette fiche est conçue pour être copiée dans le journal de la requête ou dans une procédure d’actualisation.

1. Revenir au texte brut

Dans les étapes appliquées, repérez la première conversion de type. Si une étape Type modifié arrive juste après la source, inspectez la colonne avant cette étape. Dupliquez éventuellement la requête à des fins de diagnostic ou ajoutez temporairement une colonne de contrôle ; ne remplacez pas immédiatement les caractères.

Cherchez aussi les espaces ordinaires, espaces insécables, symboles monétaires, parenthèses négatives et suffixes. 1 234,56 € n’est pas seulement un nombre avec une virgule : c’est un texte qui contient plusieurs conventions. Le nettoyage doit être documenté et testé sur les cas réellement attendus.

2. Identifier la convention de la source

La langue d’Excel sur votre poste n’est pas une preuve de la convention utilisée par l’export. Un logiciel hébergé aux États-Unis peut produire un CSV en anglais pour une équipe française ; un fournisseur allemand peut envoyer 1.234,56 ; un export paramétrable peut changer selon le profil utilisateur.

La meilleure preuve est un contrat de source : nom du système, option d’export, séparateur de colonnes, séparateur décimal, séparateur de milliers, encodage et exemples attendus. À défaut, demandez au propriétaire du fichier une valeur dont le montant exact est connu.

3. Ne pas confondre séparateur de colonne et séparateur décimal

Dans un CSV français, le point-virgule est souvent utilisé comme délimiteur de colonnes afin de laisser la virgule aux décimales. Mais ce n’est pas une règle absolue. Le connecteur doit d’abord séparer correctement les colonnes ; la culture intervient ensuite pour convertir le texte du champ Montant.

Si une ligne entière se retrouve dans une seule colonne, ou si 1,234.56 est coupé en deux champs, le problème se situe au niveau du délimiteur du fichier, pas du type numérique. Corrigez les étapes dans l’ordre : structure, nettoyage, puis conversion.

4. Convertir avec une culture explicite

Dans l’interface Power Query, sélectionnez la colonne, puis Modifier le type > Utiliser les paramètres régionaux. Choisissez le type numérique attendu et la culture correspondant au format de la source. Cette sélection crée une étape reproductible, indépendante du poste qui actualise le fichier.

En langage M, Table.TransformColumnTypes accepte un troisième argument de culture. Microsoft documente par exemple une culture fr-FR ou en-US. Microsoft Learn — Table.TransformColumnTypes

let
    Source = Csv.Document(File.Contents(CheminFichier), [Delimiter=";", Encoding=65001]),
    EnTetes = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    MontantConverti = Table.TransformColumnTypes(
        EnTetes,
        {{"Montant", type number}},
        "fr-FR"
    )
in
    MontantConverti

Pour traiter une valeur isolée, Number.FromText accepte lui aussi une culture optionnelle. Microsoft Learn — Number.FromText

Number.FromText("1234,56", "fr-FR")

Ces exemples montrent le mécanisme ; adaptez le délimiteur, l’encodage, le nom de colonne et le type à votre source. Si les fichiers d’un même dossier mélangent plusieurs conventions, ne forcez pas une culture unique : classez les fichiers par convention ou ajoutez une règle explicite fondée sur une métadonnée fiable.

Simulation fictive : quatre chaînes, trois décisions

Le tableau suivant est une simulation fictive du modèle, sans données d’entreprise réelles. Il sert à montrer pourquoi on ne peut pas déduire la convention à partir d’un seul caractère.

Les trois premières lignes convergent vers le même montant parce que la convention est connue. La quatrième reste ambiguë : en supprimer la virgule peut transformer 1,234 en 1234, tandis que la conserver comme décimale conduit à 1,234. Une règle automatique fondée uniquement sur la longueur des décimales échouera tôt ou tard.

Les contrôles à placer juste après la conversion

Une colonne sans erreur n’est pas une colonne validée. Ajoutez une petite recette de contrôle, idéalement dans une requête séparée qui référence la table préparée.

1. Nombre de lignes : la conversion ne doit ni supprimer ni dupliquer des enregistrements.
2. Nombre d’erreurs et de valeurs nulles : isolez-les au lieu de les remplacer silencieusement par zéro.
3. Minimum et maximum : un prix unitaire de 250 000 ou un montant négatif inattendu mérite une vérification.
4. Somme de contrôle : rapprochez le total d’un export source ou d’un sous-total officiellement fourni.
5. Échantillon ciblé : vérifiez au moins une petite valeur, une valeur supérieure à 1 000, une valeur négative et une valeur avec décimales.
6. Nouvelle période : rejouez la requête sur un fichier suivant avant de considérer la règle comme stable.

N’inventez pas un seuil universel. Les bornes utiles dépendent du métier : une quantité, un taux, un prix unitaire et un chiffre d’affaires n’ont pas les mêmes valeurs plausibles.

Cas limites à traiter séparément

Plusieurs cultures dans la même colonne

C’est le cas le plus délicat. La bonne solution n’est pas de tester successivement plusieurs cultures jusqu’à ce qu’une conversion réussisse : certaines chaînes seront valides dans plusieurs cultures avec des résultats différents. Il faut une colonne fiable — pays, système source, devise, identifiant de fichier — pour choisir la règle, ou faire corriger l’export en amont.

Symboles monétaires et codes de devise

1 234,56 €, €1.234,56 et USD 1,234.56 combinent format et devise. Retirer le symbole n’autorise pas à mélanger les devises. Conservez le code de devise dans une colonne distincte et ne produisez pas un total commun sans règle de conversion documentée.

Parenthèses, signes et valeurs comptables

Un export comptable peut représenter un montant négatif par (1 234,56). Vérifiez cette convention avant nettoyage. Un remplacement qui enlève seulement les parenthèses rendrait le montant positif.

Espaces invisibles

L’espace de milliers peut être ordinaire, insécable ou fine insécable. Deux valeurs qui paraissent identiques à l’écran peuvent contenir des caractères différents. Profilage, longueur du texte et remplacement ciblé aident à les distinguer ; conservez un échantillon brut pour prouver la transformation.

Pourcentages

12,5 % peut être attendu comme 0,125 dans le modèle numérique. Décidez si la source fournit un taux ou une valeur déjà divisée par cent, puis contrôlez un cas connu. Changer simplement le format d’affichage ne corrige pas une mauvaise échelle.

Exercice corrigé

Situation fictive. Une équipe reçoit deux fichiers. Le fichier A, exporté d’un logiciel français, contient 2 500,00 et 12,50. Le fichier B, exporté d’un portail américain, contient 2,500.00 et 12.50. Les deux sont ajoutés dans une même requête, puis la colonne Montant est convertie avec fr-FR. Que faut-il changer ?

Correction. Il ne faut pas convertir la colonne après l’ajout avec une seule culture. Ajoutez d’abord une colonne CultureSource ou conservez l’origine du fichier. Convertissez chaque jeu avec sa culture (fr-FR pour A, en-US pour B), contrôlez les erreurs et les totaux séparément, puis ajoutez les tables déjà normalisées. Si l’origine n’est pas fiable, arrêtez la recette et faites corriger le contrat de source.

Le test utile n’est pas seulement « la requête s’actualise ». Vérifiez que 2 500,00 et 2,500.00 deviennent tous deux 2500, puis que les deux valeurs 12,50 et 12.50 deviennent 12,5, sans suppression de ligne.

Quand une formation Power Query est-elle adaptée ?

Ce diagnostic relève du parcours Fondations si vous devez apprendre à profiler une colonne, choisir un type, utiliser une culture et construire des contrôles simples. Le parcours Avancé devient pertinent si plusieurs sources, dossiers, paramètres ou fonctions doivent appliquer des règles différentes et rester maintenables par une autre personne.

La formation Excel avec Power Query de StraFormation propose actuellement un parcours Fondations de 14 heures et un parcours Avancé de 21 heures. La page indique des modalités possibles à Strasbourg, dans les locaux selon étude, ou à distance ; elle précise aussi que les connecteurs et fonctions diffèrent entre Excel Windows, Mac et Web. Le programme doit donc être confirmé selon votre environnement, sans garantie d’automatisation pour une source instable.

Pour préparer une demande utile, transmettez : votre version et plateforme Excel, le système source, deux ou trois exemples anonymisés, la convention attendue, le résultat faux observé, le contrôle de référence, la fréquence d’actualisation, le nombre de participants et votre échéance. Ne transmettez pas de données personnelles ou confidentielles non autorisées.

Si votre problème est plutôt qu’un fichier récent n’entre pas dans une consolidation, utilisez d’abord la procédure voisine : Power Query : un nouveau fichier manque au reporting — 6 contrôles avant de modifier la requête.

Checklist avant de charger les montants

• La valeur brute a été conservée et échantillonnée.
• La culture vient du contrat de source, pas d’une supposition.
• Le délimiteur du fichier est correct avant le typage.
• La conversion utilise une culture explicite.
• Les erreurs et valeurs nulles restent visibles.
• Minimum, maximum, somme et cas ciblés sont rapprochés.
• Les devises, pourcentages et signes sont traités séparément.
• Une nouvelle période a été testée avant transmission.

La bonne correction n’est donc pas « remplacer les virgules par des points ». C’est : identifier la convention, convertir explicitement, puis prouver le résultat par des contrôles métier.