[POWERQUERY] :: Importation CSV, nettoyage, conversion des grands nombres comme 1.5M ou 4.7K ou 3.5B et N/A en valeurs numériques

[oguruma]

Bonjour le Forum,

Il se peut que vous receviez un fichier ne comportant que des grands nombres comme ceux cités dans le titre du post ou des apparitions aléatoires dans les colonnes ainsi que N/A. En l'état il serait impossible de traiter ces colonnes correctement. En ayant eu besoin dans un développement récent, je vous propose donc un petit exemple.

Exemple de fichier d'exemple qui sera fourni (fichier généré par code VBA)

Cas d'une valeur N/A dans une colonne

Cas d'un grand nombre dans une colonne

Egalement les ID dans le fichiers sont présentés par tranche

Il convient de traduire TX110 par ID-110

Le but de ce post est de montrer quelques opérations de nettoyages qui pourront être adaptées à vos besoins.

La cible
Pour N/A : traduction par la valeur 0

Cet exemple de code montre une nouvelle fois une bonne manière de manipuler la fonction List.Accumulate permettant de faire une boucle sur les colonnes mois.
List.Intersect({LST_ListeMois, Table.ColumnNames(TB_PROMOTE)}), permet de ne retenir que les mois parmi toutes les colonnes de la table.
Le code M général

let
    Source = Csv.Document(File.Contents("D:\DATA\DATA_FORMATIONS\DATA_RECHERCHES_EXCEL\__DATA_SOURCE\__SOURCE_CSV_03\VENTES_SUR_12_MOIS.csv"),[Delimiter=";", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    TB_PROMOTE = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

[B]Vous noterez cette petite astuce également pour convertir les chiffre de la colonne ID grâce à la fonction Text.Select[/B]
    TB_PREFIX = Table.TransformColumns(TB_TRANSFORM_MOIS, {
        {"ID_Transaction", each "ID-" & Text.Select(Text.From(_), {"0".."9"}), type text} }),

    // ********************************************************************************
    // Liste des colonnes à traiter - pour les isoler des autres colonnes
    // ********************************************************************************
    LST_ListeMois = {"Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre"},

    // *********************************************************************************************
    // On ne garde que les colonnes qui existent réellement dans la table pour éviter les erreurs
    // *********************************************************************************************
    LST_ColonnesExistantes = List.Intersect({LST_ListeMois, Table.ColumnNames(TB_PROMOTE)}),

    // *********************************************************************************************
    // Utilisation de List.Accumulate - on boucle sur les 12 mois
    // *********************************************************************************************
    TB_TRANSFORM_MOIS = List.Accumulate(
        LST_ColonnesExistantes,
        TB_PROMOTE,
        (tableEnCours, nomColonne) =>
        let
            // On gère le point décimal
            TB_REPLACE=Table.ReplaceValue(tableEnCours,".",",",Replacer.ReplaceText,{nomColonne}),             

            // On force les N/A à 0
            TB_REPLACE_NA=Table.ReplaceValue(TB_REPLACE,"N/A","0",Replacer.ReplaceText,{nomColonne}),             

            // Recherche et remplacement des K et M dans les valeurs et calculs en valeurs
            TB_TRANSFORM_COL=Table.TransformColumns(TB_REPLACE_NA, {
                {nomColonne, each ConvertGrandNombre(_)}
            }),
            // Après conversion de la colonne on transforme en nombre décimal
            TB_TRANSFORM_TYPE=Table.TransformColumnTypes(TB_TRANSFORM_COL,{{nomColonne, type number}})
        in
            // Fin de la transormation des valeurs pour chaque mois
           TB_TRANSFORM_TYPE
    ),
    
    // *********************************************************************************************
    // On transforme la colonne ID
    // *********************************************************************************************
    TB_PREFIX = Table.TransformColumns(TB_TRANSFORM_MOIS, {
        {"ID_Transaction", each "ID-" & Text.Select(Text.From(_), {"0".."9"}), type text} }),

    // *********************************************************************************************
    // On transforme la colonne dimension
    // *********************************************************************************************
    TB_DIM_SPLIT = Table.SplitColumn(
        TB_PREFIX,
        "Dimensions",
        Splitter.SplitTextByDelimiter("x", QuoteStyle.Csv),
        {"Largeur", "Longueur", "Hauteur"}
    ),
    TB_DIM_TYPE = Table.TransformColumnTypes(TB_DIM_SPLIT, {
        {"Largeur", Int64.Type}, {"Longueur", Int64.Type}, {"Hauteur", Int64.Type}
    })
in
    TB_DIM_TYPE

Le code de la fonction ConvertGrandNombre()

let ConvertGrandNombre = (pGrandNombre as any) =>
    let
        // on récupère la valeur en cours symbolisée par '_'
        Texte = Text.Upper(Text.From(pGrandNombre)), // Par sécurité on converti en majuscule
        Valeur = Number.From(Text.Select(Texte, {"0".."9", ".", ","})),
        // On multiplie selon l'unité détectée
        Multiplicateur =
            if Text.EndsWith(Texte, "K")      then 1000
            else if Text.EndsWith(Texte, "M") then 1000000
            else if Text.EndsWith(Texte, "B") then 1000000000000
                                                    
            else 1
    in
        // On converti temprairement en text
        Text.From(Valeur * Multiplicateur )
in
    ConvertGrandNombre