POWERQUERY :: Nettoyage de données - Supprimer des caractères entre deux délimiteurs - Fonction automatisée

oguruma · 2 Août 2024

Bonjour,
Il arrive régulièrement quand on reçoit un fichier de l'extérieur que l'on soit amené à effectuer du nettoyage de données. Un cas fréquent, supprimer des caractères entre deux séparateurs.
Exemples (eEmails bidons)

L'idée serait supprimer y compris les délimiteurs les caractères entre (, ) - <, > - ::, ::

ou ici la colonne d'origine n'est pas conservée à l'issue du nettoyage

Pour cela une petite fonction fnDeleteTextBetweenDelimiters

PowerQuery:

let
    Source = (pSTR_String as text, pSTR_LeftDelim as text, pSTR_RightDelim as text) =>  
    let
        STR_LST_NULL={""},
        //***********************************************************************************************
        // On commence par celui de droite et si trouvé on remplace par chaine vide
        //***********************************************************************************************
        LST_Right = List.RemoveMatchingItems(Text.Split(pSTR_String, pSTR_RightDelim), STR_LST_NULL ),

        //***********************************************************************************************
        // Il faut maintenant extraire la partie gauche du délimiteur
        // On balaye le reste de la chaine pour trouve celui de gauche
        //***********************************************************************************************
        LST_Left = List.Transform(
                LST_Right, 
                        // --- Ici autre manière d'exprimer un each en passant par une fonction
                        (x)=>
                            let 
                                //************************************************************************
                                // Calcule sa position pour en déduire le reste de la chaine à fusioinner
                                //************************************************************************
                                INT_Position=Text.PositionOf(x, pSTR_LeftDelim ),
                                //************************************************************************
                                // Par sécurité pour ne pas planter s'il n'est pas trouvé
                                // et on écarte la partie inutile
                                //************************************************************************
                                STR_Value=if INT_Position= -1 then x else Text.Start(x , INT_Position)
                            in
                                STR_Value
                ),
        //**************************************************************************************************
        // Puis assemble les deux chaines pour former celle d'origine sans le contenu entre les séparateurs
        // Les éléments de la liste sont concaténés
        //**************************************************************************************************
        LST_Final = Text.Combine(LST_Left)
    in
        LST_Final
in
    Source

Comment l'utiliser

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="TB_EMAILS_1"]}[Content],
    TBL_CallFunction = Table.AddColumn(Source, "EMAIL_2", each fnDeleteTextBetweenDelimiters([EMAIL], "<", ">"))
in
    TBL_CallFunction

Cas où ne souhaite pas garder la colonne source

Méthode 1

PowerQuery:

let
    Source = TB_EMAILS_1,
    STR_ColumnToClean="EMAIL",
    STR_AddColumn="EMAIL_2",
    STR_StartDelim="<",
    STR_EndDelim=">",
    B_ToDeleteColumn=true,
    STR_EVAL_Each="each fnDeleteTextBetweenDelimitersNoKeep([" & STR_ColumnToClean & "], STR_StartDelim, STR_EndDelim)",
    EVAL_Each=Expression.Evaluate(STR_EVAL_Each ,
        [
            fnDeleteTextBetweenDelimitersNoKeep=fnDeleteTextBetweenDelimiters,
            STR_ColumnToClean=STR_ColumnToClean,
            STR_StartDelim=STR_StartDelim,
            STR_EndDelim=STR_EndDelim
        ]),
    TBL_CallFunction = Table.AddColumn(Source, STR_AddColumn, EVAL_Each),
    TBL_FINAL=if B_ToDeleteColumn
              then 
                    let
                        TBL_DelColumn1 = Table.RemoveColumns(TBL_CallFunction,{STR_ColumnToClean}),
                        TBL_RenameColumn2 = Table.RenameColumns(TBL_DelColumn1,{{STR_AddColumn, STR_ColumnToClean}})
                    in
                        TBL_RenameColumn2
              else
                    TBL_CallFunction
in
    TBL_FINAL

Méthode 2

PowerQuery:

let
    Source = TB_EMAILS_1,
    STR_ColumnToClean="EMAIL",
    STR_StartDelim="<",
    STR_EndDelim=">",
    TBL_Transform = Table.TransformColumns(Source,{{STR_ColumnToClean, (fld) => fnDeleteTextBetweenDelimiters(fld, STR_StartDelim, STR_EndDelim), type text}}),
    TBL_ClearSpaces = Table.TransformColumns(TBL_Transform,{{STR_ColumnToClean, Text.Trim, type text}})
in
    TBL_ClearSpaces

PowerQuery:

let fnTBLDeleteTextBetweenDelimiters = (
        pSTR_Source as any,
        pSTR_ColumnToClean as text,
        pSTR_AddColumn as text,
        pSTR_StartDelim as text,
        pSTR_EndDelim as text,
        optional pB_ToDeleteColumn as logical
    ) =>
    let
        Source = if pSTR_Source is table
                then pSTR_Source
                else Excel.CurrentWorkbook(){[Name=pSTR_Source]}[Content],
        STR_ColumnToClean=pSTR_ColumnToClean,
        STR_AddColumn=pSTR_AddColumn,
        STR_StartDelim=pSTR_StartDelim,
        STR_EndDelim=pSTR_EndDelim,
        B_ToDeleteColumn=if pB_ToDeleteColumn is null then false else pB_ToDeleteColumn,
        STR_EVAL_Each="each fnDeleteTextBetweenDelimiters([" & STR_ColumnToClean & "], STR_StartDelim, STR_EndDelim)",
        EVAL_Each=Expression.Evaluate(STR_EVAL_Each ,
            [
                fnDeleteTextBetweenDelimiters=fnDeleteTextBetweenDelimiters,
                STR_ColumnToClean=STR_ColumnToClean,
                STR_StartDelim=STR_StartDelim,
                STR_EndDelim=STR_EndDelim
            ]),
        TBL_CallFunction = Table.AddColumn(Source, STR_AddColumn, EVAL_Each),
        TBL_FINAL=if B_ToDeleteColumn
                then 
                        let
                            TBL_DelColumn1 = Table.RemoveColumns(TBL_CallFunction,{STR_ColumnToClean}),
                            TBL_RenameColumn2 = Table.RenameColumns(TBL_DelColumn1,{{STR_AddColumn, STR_ColumnToClean}})
                        in
                            TBL_RenameColumn2
                else
                        TBL_CallFunction
    in
        TBL_FINAL
in 
    fnTBLDeleteTextBetweenDelimiters

Version 2

PowerQuery:

let fnTBLDeleteTextBetweenDelimitersV2=
    (
        pSTR_Source as any,
        pSTR_ColumnToClean as text,
        pSTR_StartDelim as text,
        pSTR_EndDelim as text
    )=>

    let
        Source = if pSTR_Source is table
                then pSTR_Source
                else Excel.CurrentWorkbook(){[Name=pSTR_Source]}[Content],
        STR_ColumnToClean=pSTR_ColumnToClean,       
        STR_StartDelim=pSTR_StartDelim,
        STR_EndDelim=pSTR_EndDelim,
        TBL_Transform = Table.TransformColumns(Source,{{STR_ColumnToClean, (fld) => fnDeleteTextBetweenDelimiters(fld, STR_StartDelim, STR_EndDelim), type text}}),
        TBL_ClearSpaces = Table.TransformColumns(TBL_Transform,{{STR_ColumnToClean, Text.Trim, type text}})
    in
        TBL_ClearSpaces
       
in
    fnTBLDeleteTextBetweenDelimitersV2

Le fichier joint sera plus explicite avec les nombreux exemples qu'il comporte

POWERQUERY :: Nettoyage de données - Supprimer des caractères entre deux délimiteurs - Fonction automatisée

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires