[POWERQUERY] :: Import CSV, nettoyage, conversion des grands nombres en valeurs numériques, mesures t, kg, g - V2

[oguruma]

Bonjour Le Forum,

ce post fait suite ==> à celui ici avec quelques améliorations et ajout d'une conversion de mesures en kg quand on a dans un fichier des poids exprimés en tonnes, kg, grammes comme 3 t, 2.5 kg, 0.25 g.
Si on souhaite faires des calculs sur cette colonne poids c'est impossible pour deux raisons :
- les poids sont exprimés en valeur texte
- les unités de mesures sont différentes.
Il est donc important d'avoir une seule unité de mesure comme le kg par exemple.
C'est donc l'objet de ce post.

Exemple de fichier

Code maître d'importation du fichier de données

let
    Source = Csv.Document(File.Contents("C:\Users\oguru\desktop\Export_1000_Lignes_02.csv"),[Delimiter=";", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    TB_PROMOTE = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TB_MODIF_TYPE = Table.TransformColumnTypes(TB_PROMOTE,{{"Date", type date}}),
    TB_TRANSFORM=fnTransformColumnGN(TB_MODIF_TYPE,"Total"),
    TB_ADD_COL = Table.AddColumn(TB_TRANSFORM, "Poids_Kg", each Number.From(fnConvertMesureKg([Poids])))
in
    TB_ADD_COL

Fonction de conversion fnConvertMesureKg

let fnTransformColumnMesureKg = (pGrandNombre as any) =>
    let
        // on récupère la valeur en cours symbolisée par '_'
        Texte = Text.Trim(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, "KG")      then 1
            else if Text.EndsWith(Texte, "T") then 1000                                                    
            else if Text.EndsWith(Texte, "G") then 0.001                                                    
            else 1
    in
        // On converti temprairement en text
        Text.From(Valeur * Multiplicateur)
in
    fnTransformColumnMesure

Nous obtenons

J'en profite pour fournir la mise à jour de la fonction permettant de convertir les grands nombres.

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]),

    // ********************************************************************************
    // 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 fnConvertGrandNombre(_)}
            }),
            // 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

Les mises à jours portent sur ces lignes

            // Recherche et remplacement des K et M dans les valeurs et calculs en valeurs

            TB_TRANSFORM_COL=Table.TransformColumns(TB_REPLACE_NA, {

                {nomColonne, each fnConvertGrandNombre(_)}

            }),

            // Après conversion de la colonne on transforme en nombre décimal

            TB_TRANSFORM_TYPE=Table.TransformColumnTypes(TB_TRANSFORM_COL,{{nomColonne, type number}})

Cette fonction c'est un "deux en un" - conversion de la valeur et transformation de la colonne dans la foulée

let fnTransformColumnGrandNombre=(pTable as table, pColonne as text) as table =>
    let

        ConvertGN = (pGrandNombre as any) =>
            let
                // on récupère la valeur en cours symbolisée par '_'
                Texte = Text.Trim(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 ),

       
        TB_TRANSFORM_COL=Table.TransformColumns(pTable, {
                    {pColonne, each ConvertGN(_)}
        }),

        TB_TRANSFORM_TYPE=Table.TransformColumnTypes(TB_TRANSFORM_COL,{{pColonne, type number}})
    in
        TB_TRANSFORM_TYPE
in
    fnTransformColumnGrandNombre

let fnConvertGrandNombre = (pGrandNombre as any) =>
    let
        // on récupère la valeur en cours symbolisée par '_'
        Texte = Text.Trim(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
    fnConvertGrandNombre