Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

POWERQUERY :: Extraire des caractères selon un masque

oguruma

XLDnaute Occasionnel
Bonjour de retour après de longues semaines d'absence....
Ici je vous propose d'extraire des caractères d'une chaine selon un masque.
Masque :
  • "X" : caractères ANSI de 0..127
  • "A" : On ne retient que les lettres majuscules et minuscules
  • "U" : On ne retient que les lettres majuscules
  • "L" : On ne retient que les lettres minuscules
  • "L9" : Caractères numériques et minuscules
  • "U9" : Caractères numériques et majuscules
  • "SU9" : Caractères numériques et majuscules et spéciaux
  • "AU9" : Caractères numériques et majuscules et accentués
  • "SL9" : Caractères numériques et minuscules et spéciaux
  • "AL9" : Caractères numériques et minuscules et accentués
  • "9A" : Caractères numériques et masjuscules
  • "9X" : Caractères numériques et majuscules et minuscules

La fonction PowerQuery

PowerQuery:
fnExtractCharacters = (
    SourceField as text,                // Chaine de caractère initale à explourer
    StripMode as text,                  // Masque représentant les caractères à extraire de la chaine
    optional pAddChar as logical,       // Possibilité de retenir des caractères (AddChar) supplémentaires qui auraient été écartés par le masque
    optional pListChar as text) =>      // Possibilité de spécifier des caractères à retenir (liste restrcitive) qui auraient été écartés par le masque

On peut aussi forcer la présence de caractères soit une liste définie par défaut dans le code soit en la précisant à l'appel de la fonction.
La liste par défaut :
{",", ".", ";", ":", "!", "§", "%", "£", "=", "+", "@", "_", "{", "}", "[", "]", "`", "&", "~", "°", "^", "*", "µ", "¤", "¨", "<", ">", "\", "/", "?", "ù", "#", " "}

Exemple d'appel :
Table.AddColumn(#"Changed Type", "ALL", each fnExtractCharacters([CHAINE], "X", false))
Table.AddColumn(#"Changed Type", "NUMBER", each fnExtractCharacters([CHAINE], "9",true)) --> on ajoute au masque la liste par défaut
Table.AddColumn(#"Changed Type", "TEXT", each fnExtractCharacters([CHAINE], "A"))
Table.AddColumn(#"Changed Type", "TEXT", each fnExtractCharacters([CHAINE], "U",true,":,9")) --> on ajoute au masque les caractères ":,9"

Le code complet de la fonction

PowerQuery:
let fnExtractCharacters = (
    SourceField as text,                // Chaine de caractère initale à explourer
    StripMode as text,                  // Masque représentant les caractères à extraire de la chaine
    optional pAddChar as logical,       // Possibilité de retenir des caractères (AddChar) supplémentaires qui auraient été écartés par le masque
    optional pListChar as text) =>      // Possibilité de spécifier des caractères à retenir (liste restrcitive) qui auraient été écartés par le masque

    let
        // Liste par défaut des caractères à ajouter
        //------------------------------------------
        AddChar = {",", ".", ";", ":", "!", "§", "%", "£", "=", "+", "@", "_", "{", "}", "[", "]", "`", "&", "~", "°", "^", "*", "µ", "¤", "¨", "<", ">", "\", "/", "?", "ù", "#", " "},

        // L'ajout de caractères est-il activié
        //-------------------------------------
        bAddChar = if pAddChar is null then false else pAddChar,

        // Des caractères spécifiques sont-ils précisés afin de ne pas prendre en considération la liste par défaut dans AddChar
        //----------------------------------------------------------------------------------------------------------------------
        sListChar = if pListChar is null then false else pListChar,
       
        // Prise en compte des caractères passé en paramètre afin de les retenir pour le résultat final
        //---------------------------------------------------------------------------------------------
        AddCharFinal =if sListChar <> false and bAddChar
                      then
                            let
                                s1=Text.ToList(pListChar)
                            in
                                s1
                      else
                            AddChar,

        // Construction du masque d'extraction des caractères
        //---------------------------------------------------
        Pattern =
            // Alphanumérique pour les caractères ANSI de 0..127
            if StripMode = "X"
            then List.Transform({0..127}, each Character.FromNumber(_))
            else
                // On ne retient que les chiffres de 0..9 ainsi que les signes -,+, le symbole monétaire et la décimale . ou ,
                if StripMode = "9"
                then {"0".."9", ".", ",", "-", "+", "€"}
                else
                // On ne retient que les lettres majuscules et minuscules
                if StripMode = "A"
                    then {"A".."Z","a".."z"}
                    else
                        // On ne retient que les majuscules
                        if StripMode = "U"
                        then {"A".."Z"}
                        else
                            // On ne retient que les minuscules
                            if StripMode = "L"
                            then {"a".."z"}
                            else
                                // Combinaison numérique et minuscules
                                if StripMode = "L9"
                                then {"0".."9", ".", ",", "-", "+", "€", "a".."z"}
                                else
                                    // Combinaison numérique et majuscules
                                    if StripMode = "U9"
                                    then {"0".."9", ".", ",", "-", "+", "€", "A".."Z"}
                                    else
                                        // Combinaison caractères spéciaux, numériques et majuscules
                                        if StripMode = "SU9"
                                        then
                                            let
                                                s1 = {"0".."9", ".", ",", "-", "+", "€", "A".."Z"},
                                                s2 = {123..190},
                                                s3 = List.Transform(s2, each Character.FromNumber(_)),
                                                s4 = List.Combine({s1,s3})
                                            in
                                                s4
                                        else
                                            // Combinaison caractères accentués, numériques et majuscules
                                            if StripMode = "AU9"
                                            then
                                                let
                                                   s1= {"0".."9", ".", ",", "-", "+", "€", "A".."Z"},
                                                   s2 = {191..255},
                                                   s3 = List.Transform(s2, each Character.FromNumber(_)),
                                                   s4 = List.Combine({s1,s3})
                                                in
                                                    s4
                                            else
                                                // Combinaison caractères spéciaux, numériques et minuscules
                                                if StripMode = "SL9"
                                                then
                                                    let
                                                        s1 = {"0".."9", ".", ",", "-", "+", "€", "a".."z"},
                                                        s2 = {123..190},
                                                        s3 = List.Transform(s2, each Character.FromNumber(_)),
                                                        s4 = List.Combine({s1,s3})
                                                    in
                                                        s4  
                                                else
                                                    // Combinaison caractères accentués, numériques et minuscules
                                                    if StripMode = "AL9"
                                                    then
                                                        let
                                                           s1= {"0".."9", ".", ",", "-", "+","a".."z"},
                                                           s2 = {191..255},
                                                           s3 = List.Transform(s2, each Character.FromNumber(_)),
                                                           s4 = List.Combine({s1,s3})
                                                        in
                                                            s4
                                                    else
                                                        // Combinaison numérique et majuscules
                                                        if StripMode = "9A"
                                                        then {"0".."9", "A".."Z", "-", "+", ".", ","}
                                                        else
                                                            // Combinaison numérique et minuscules+majuscules
                                                            if StripMode = "9X"
                                                            then {"0".."9", "A".."Z", "a".."z", "-", ".", "+", ","}
                                                            else
                                                                null,  

        // Construction du masque final
        //-----------------------------
        PatternFinal = if bAddChar
                       then
                            let
                                s = List.Combine({Pattern,AddCharFinal})
                            in
                                s
                        else
                            Pattern,

        // Extraction des caractères selon le masque
        //------------------------------------------
        FinalString = Text.Select(SourceField, PatternFinal)
    in
        FinalString
in
    fnExtractCharacters

Le code est assez ouvert et vous pouvez ajouter vos propres masques
 

Pièces jointes

  • PQWExtractFromPicture_V0.001.xlsx
    62.7 KB · Affichages: 0

oguruma

XLDnaute Occasionnel
Une évolution de la fonction. Le masque est construit dynamiquement. On évite ainsi la série de tests en cascade.
PowerQuery:
let fnExtractCharacters = (
    SourceField as text,                // Chaine de caractère initale à explourer
    StripMode as text,                  // Masque représentant les caractères à extraire de la chaine
    optional pAddChar as logical,       // Possibilité de retenir des caractères (AddChar) supplémentaires qui auraient été écartés par le masque
    optional pListChar as text) =>      // Possibilité de spécifier des caractères à retenir (liste restrcitive) qui auraient été écartés par le masque   

    let
    
        //----------------------------------------------------------------------------------------
        // Création des masques au préalable
        //----------------------------------------------------------------------------------------
        LIST_X=List.Transform({0..127}, each Character.FromNumber(_)),
        LIST_9={"0".."9", ".", ",", "-", "+", "€"} ,
        LIST_A={"A".."Z","a".."z"},
        LIST_U={"A".."Z"},
        LIST_L={"a".."z"},
        LIST_L9={"0".."9", ".", ",", "-", "+", "€", "a".."z"},
        LIST_U9={"0".."9", ".", ",", "-", "+", "€", "A".."Z"},
        LIST_SU9=
                let 
                    s1 = {"0".."9", ".", ",", "-", "+", "€", "A".."Z"},
                    s2 = {123..190},
                    s3 = List.Transform(s2, each Character.FromNumber(_)),
                    s4 = List.Combine({s1,s3})
                in
                    s4,
        LIST_AU9=
                let
                    s1= {"0".."9", ".", ",", "-", "+", "€", "A".."Z"},
                    s2 = {191..255},
                    s3 = List.Transform(s2, each Character.FromNumber(_)),
                    s4 = List.Combine({s1,s3})
                in
                    s4,
        LIST_SL9=
                let 
                    s1 = {"0".."9", ".", ",", "-", "+", "€", "a".."z"},
                    s2 = {123..190},
                    s3 = List.Transform(s2, each Character.FromNumber(_)),
                    s4 = List.Combine({s1,s3})
                in
                    s4,   
        LIST_AL9=
                let
                    s1= {"0".."9", ".", ",", "-", "+","a".."z"},
                    s2 = {191..255},
                    s3 = List.Transform(s2, each Character.FromNumber(_)),
                    s4 = List.Combine({s1,s3})
                in
                    s4,
        LIST_9A={"0".."9", "A".."Z", "-", "+", ".", ","},
        LIST_9X={"0".."9", "A".."Z", "a".."z", "-", ".", "+", ","},

        // Liste par défaut des caractères à ajouter
        //------------------------------------------
        AddChar = {",", ".", ";", ":", "!", "§", "%", "£", "=", "+", "@", "_", "{", "}", "[", "]", "`", "&", "~", "°", "^", "*", "µ", "¤", "¨", "<", ">", "\", "/", "?", "ù", "#", " "},

        // L'ajout de caractères est-il activié
        //-------------------------------------
        bAddChar = if pAddChar is null then false else pAddChar,

        // Des caractères spécifiques sont-ils précisés afin de ne pas prendre en considération la liste par défaut dans AddChar
        //----------------------------------------------------------------------------------------------------------------------
        sListChar = if pListChar is null then false else pListChar,
        
        // Prise en compte des caractères passé en paramètre afin de les retenir pour le résultat final
        //---------------------------------------------------------------------------------------------
        AddCharFinal =if sListChar <> false and bAddChar
                      then
                            let
                                s1=Text.ToList(pListChar)
                            in
                                s1
                      else
                            AddChar,

        //----------------------------------------------------------------------------------------
        // Construction dynamique du masque d'extraction des caractères
        //----------------------------------------------------------------------------------------
        Pattern=Expression.Evaluate("LIST_" & StripMode,[
                                                LIST_X=LIST_X,
                                                LIST_A=LIST_A,
                                                LIST_9=LIST_9,
                                                LIST_U=LIST_U,
                                                LIST_L=LIST_L,
                                                LIST_L9=LIST_L9,
                                                LIST_U9=LIST_U9,
                                                LIST_SU9=LIST_SU9,
                                                LIST_AU9=LIST_AU9,
                                                LIST_SL9=LIST_SL9,
                                                LIST_AL9=LIST_AL9,
                                                LIST_9A=LIST_9A,
                                                LIST_9X=LIST_9X
                                              ]),

        // Construction du masque final
        //-----------------------------
        PatternFinal = if bAddChar
                       then
                            let
                                s = List.Combine({Pattern,AddCharFinal})
                            in
                                s
                        else
                            Pattern,

        // Extraction des caractères selon le masque
        //------------------------------------------
        FinalString = Text.Select(SourceField, PatternFinal)
    in
        FinalString
in
    fnExtractCharacters

La construction dynamique du masque se situe ici en faisant appel à cette fonction puissante Expression.Evaluate qui est le pendant de la fonction INDIRECT sous Excel.

PowerQuery:
        Pattern=Expression.Evaluate("LIST_" & StripMode,[

                                                LIST_X=LIST_X,

                                                LIST_A=LIST_A,

                                                LIST_9=LIST_9,

                                                LIST_U=LIST_U,

                                                LIST_L=LIST_L,

                                                LIST_L9=LIST_L9,

                                                LIST_U9=LIST_U9,

                                                LIST_SU9=LIST_SU9,

                                                LIST_AU9=LIST_AU9,

                                                LIST_SL9=LIST_SL9,

                                                LIST_AL9=LIST_AL9,

                                                LIST_9A=LIST_9A,

                                                LIST_9X=LIST_9X

                                              ]),
 

Pièces jointes

  • PQWExtractFromPicture_V0.005.xlsx
    115 KB · Affichages: 1

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…