POWERQUERY :: Extractions multiples de caractères en minuscule, en majuscule et de chiffres - Astuces sur le each et Record

[oguruma]

Bonjour, toujours dans la continuité des différents types d'extractions voici deux petites fonctions qui permettent soit :
- d'extraire les caractères en majuscule
- d'extraire les caractères en minuscule
- d'extraire les chiffres.

Pour cela il existe la fonction Text.Select dans laquelle on passe la chaine à évaluer et le masque de sélection - exemple Text.Select("abc2024", {"a".."z"}) donnera comme résultat "abc".

Le but sera donc de créer une fonction à tiroirs dans laquelle on indiquera si l'extraction concerne soit les majuscules, soit les minuscules, soit les chiffres. A là fin du traitement on précisera si on souhaite créer une nouvelle colonne avec le résultat ou si la colonne à traiter sera remplacée par le résultat.

let fnTBLExtractTextNumber = (
        pSTR_Source as any,
        pSTR_Field as text,
        pSTR_FieldSelect as text,
        pSTR_Type as text,  
        optional pB_AddColumns as logical
    ) =>
    let
       
        //******************************************************************************************
        // Gestion des paramètres
        //******************************************************************************************
        STR_Source=pSTR_Source,
        STR_Field=pSTR_Field,
        STR_FieldSelect=pSTR_FieldSelect,
        STR_Type=Text.Upper(pSTR_Type),
        B_AddColumns=if pB_AddColumns is null then true else pB_AddColumns,

        //******************************************************************************************
        // Constantes
        //******************************************************************************************
        LST_UPPER_CASE={"A".."Z"},
        LST_LOWER_CASE={"a".."z"},
        LST_NUMBER={"0".."9"},
        LST_MIXTE=List.Combine({LST_UPPER_CASE, LST_LOWER_CASE, LST_NUMBER}),

        //******************************************************************************************
        // Type d'extrazction
        //******************************************************************************************
        LST_SelectFilter=if STR_Type="U"
                        then LST_UPPER_CASE
                            else if STR_Type="L"
                                then LST_LOWER_CASE
                                    else if STR_Type="N"
                                        then LST_NUMBER
                                            else LST_MIXTE,
   
        //******************************************************************************************
        // Construction dynamique pour le AddColumn
        //******************************************************************************************
        EVAL_Field=Expression.Evaluate("each Text.Select([" & STR_Field & "], LST_SelectFilter)", [Text.Select=Text.Select, STR_Field=STR_Field, LST_SelectFilter=LST_SelectFilter]),
   
        //******************************************************************************************
        // Table à traiter
        //******************************************************************************************
        Source = if STR_Source is table then STR_Source else Excel.CurrentWorkbook(){[Name=STR_Source]}[Content],

        //******************************************************************************************
        // Choix : soit ajout d'une colonne soit traitement dans la colonne elle-même
        //******************************************************************************************
        TBL_Final = if B_AddColumns then
                        Table.AddColumn(Source, STR_FieldSelect, EVAL_Field)
                    else
                        Table.TransformColumns(Source,{{STR_Field, (x)=> Text.Select(x,LST_SelectFilter), type text}})
    in
        TBL_Final
in
    fnTBLExtractTextNumber

let
    Source = fnTBLExtractTextNumber(
        "TB_TEST",
        "Text",
        "Result",
        "N",  
        true


    )
in
    Source

let
    Source = fnTBLExtractTextNumber(
        "TB_TEST",
        "Text",
        "Result",
        "N",  
        false


    )
in
    Source

Dans le fichier joint d'autres exemples sont à découvrir.

Une nouvelle fois la construction de cette fonction est très pédagogique pour les débutants en langage M.

Enfin en lieu et place de renvoyer une table on peut aussi coder une fonction qui ne renverra que la chaine modifiée que l'on pourra placer dans une nouvelle colonne ou qui remplacera la colonne à examiner

let fnSTRExtractTextNumber = (
        pSTR_String as any,
        pSTR_Type as text
    ) =>
    let
       
        //******************************************************************************************
        // Gestion des paramètres
        //******************************************************************************************
        STR_String=pSTR_String,
        STR_Type=Text.Upper(pSTR_Type),

        //******************************************************************************************
        // Constantes
        //******************************************************************************************
        LST_UPPER_CASE={"A".."Z"},
        LST_LOWER_CASE={"a".."z"},
        LST_NUMBER={"0".."9"},
        LST_MIXTE=List.Combine({LST_UPPER_CASE, LST_LOWER_CASE, LST_NUMBER}),

        //******************************************************************************************
        // Type d'extrazction
        //******************************************************************************************
        LST_SelectFilter=if STR_Type="U"
                        then LST_UPPER_CASE
                            else if STR_Type="L"
                                then LST_LOWER_CASE
                                    else if STR_Type="N"
                                        then LST_NUMBER
                                            else LST_MIXTE,
 
        STR_Final = Text.Select(STR_String, LST_SelectFilter)
    in
        STR_Final
in
    fnSTRExtractTextNumber

let
    Source = Excel.CurrentWorkbook(){[Name="TB_TEST"]}[Content],
    TBL_Final=Table.AddColumn(Source, "Resultat", each fnSTRExtractTextNumber([Text],"L"))
in
    TBL_Final
 
let
    Source = Excel.CurrentWorkbook(){[Name="TB_TEST"]}[Content],
    TBL_Final=Table.TransformColumns(Source, { {"Text", each fnSTRExtractTextNumber(_,"U"), type text} })
in
    TBL_Final

Au passage... voici encore deux requêtes qui permettent de mieux comprendre le fonctionnement de la clause each
each fnSTRExtractTextNumber(_,"U")
peut être remplacé par
(x)=> fnSTRExtractTextNumber(x,"U")

Une nouvelle astuce à propos du each et la gestion des Records

let
    Source = Excel.CurrentWorkbook(){[Name="TB_TEST"]}[Content],
    STR_FIELD="Text",
    TBL_Final=Table.AddColumn(Source, "Resultat", (REC)=> fnSTRExtractTextNumber(Record.Field(REC,STR_FIELD),"L"))
in
    TBL_Final

On récupère la colonne via cette fonction Record.Field dans laquelle on passe un Record et un champ de type texte. Le Record en cours de traitement est obtenu via la clause each et cette fois pour plus de lisibilité on le matérialise par la fonction (REC)=>
Une autre astuce pour s'éviter un Expression.Evaluate si on ne maîtrise pas cette fonction.
On aurait pu écrire each.... Record.Field(_,STR_FIELD)
le '_' symbolise le Record en cours de traitement ce qui peut correspondre à NomDeTable{n} qui renvoie un objet de type Record. {n} représente le n° de ligne ou d'enregistrement.
On peut aussi accéder à la colonne par son index de colonne comme suit :
Record.Field(REC,Record.FieldNames(REC){0})
Record.FieldNames renvoie une liste avec les colonnes qui composent le Record
{0} => index de la colonne dans le Record, soit le 1er (l'index commence à 0)

Voici aussi un exemple de la fonctioin Table.TransformColumns qui n'est pas toujours évidente à comprendre.
La maîtrise de cette fonction peut nous éviter de dupliquer des colonnes pour des traitements intermédiaires et de les supprimer en fin de requête. On gagne ainsi des étape et le requête est donc plus rapide.