POWERQUERY :: Extraction entre délimiteurs/Nettoyage de caractères suite....

oguruma · 4 Août 2024

Bonjour,
Ce post est un peu la suite de celui-ci : https://excel-downloads.com/threads...ux-delimiteurs-fonction-automatisee.20083969/

Exemple - Nous sommes actuellement dans le thème... sportif du moment
Classement bidon

Et obtenir ceci

Judo : c'est un peu erroné après la superbe victoire de l'équipe de France qui détrône pour la seconde fois les Japonais après être menée 2 combats à 0
(voici pour le clin d'oeil de mon sport)

Le but est donc de se débarrasser des chiffres, et du '-'

Plusieurs manières d'y parvenir - d'où plusieurs fonctions

méthode 1 - fnRemoveAndRankV1

PowerQuery:

let fnRemoveAndRankV1=(
        pSTR_Source as any,
        pSTR_Field as text,
        pSTR_FieldFinal as text,
        pSTR_GroupBy as text,
        pSTR_Sep1 as text,
        pSTR_Sep2 as text,
        pSTR_Sep3 as text
    ) =>
  
    let
        STR_Source=pSTR_Source,
        Source = if pSTR_Source is table then pSTR_Source else  Excel.CurrentWorkbook(){[Name=STR_Source]}[Content],
        STR_Field=pSTR_Field,
        STR_Field_1=STR_Field & ".1",
        STR_Field_2=STR_Field & ".2",
        STR_FieldFinal=pSTR_FieldFinal,
        STR_GroupBy=pSTR_GroupBy,
        STR_Sep1=pSTR_Sep1,
        STR_Sep2=pSTR_Sep2,
        STR_Sep3=pSTR_Sep3,
      
        TBL_SplitDelim1 = Table.ExpandListColumn(Table.TransformColumns(Source, {{STR_Field, Splitter.SplitTextByDelimiter(STR_Sep1, QuoteStyle.Csv)}}), STR_Field),
        TBL_SplitDelim2 = Table.SplitColumn(TBL_SplitDelim1, STR_Field, Splitter.SplitTextByDelimiter(STR_Sep2, QuoteStyle.Csv), {STR_Field_1, STR_Field_2}),
        STR_Each="each Text.Combine([" & STR_Field_2 & "],STR_Sep3)",
        EVAL_Each=Expression.Evaluate(STR_Each,[Text.Combine=Text.Combine, STR_Sep3=STR_Sep3]),
        TBL_Final = Table.Group(TBL_SplitDelim2, {STR_GroupBy}, {{STR_FieldFinal, EVAL_Each, type text}}),
        TBL_TrimColum0 = Table.TransformColumns(TBL_Final,{{STR_FieldFinal, Text.Trim, type text}}),
        TBL_TrimColum = Table.TransformColumns(TBL_TrimColum0,{{STR_FieldFinal, each fnRemoveMaxSpaces(_), type text}})
    in
        TBL_TrimColum
in
    fnRemoveAndRankV1

PowerQuery:

let
    Source= fnRemoveAndRankV1(
        "TB_JO_BIDON",
        "CLASSEMENT",
        "CLASSEMENT FINAL",
        "DISCIPLINE",
        ",",
        "-",
        ", "
    )
in
    Source

méthode 2 - fnRemoveAndRankV2

PowerQuery:

let fnRemoveAndRankV2=(
        pSTR_Source as any,
        pSTR_Field as text,
        pSTR_FieldFinal as text,
        pSTR_GroupBy as text,
        pSTR_Sep1 as text,
        pSTR_Sep2 as text,
        pSTR_Sep3 as text
    ) =>
  
    let
        STR_Source=pSTR_Source,
        Source = if pSTR_Source is table then pSTR_Source else  Excel.CurrentWorkbook(){[Name=STR_Source]}[Content],
        STR_Field=pSTR_Field,
        STR_Field_1=STR_Field & ".1",
        STR_Field_2=STR_Field & ".2",
        STR_FieldFinal=pSTR_FieldFinal,
        STR_GroupBy=pSTR_GroupBy,
        STR_Sep1=pSTR_Sep1,
        STR_Sep2=pSTR_Sep2,
        STR_Sep3=pSTR_Sep3,

        LST_Columns=Table.ColumnNames(Source),
        INT_ColCount=List.Count(LST_Columns),
        INT_Position=List.PositionOf(LST_Columns,STR_Field),
        LST_FirstN=List.FirstN(LST_Columns,INT_Position),
        LST_LastN=List.LastN(LST_Columns,INT_ColCount - INT_Position - 1),
        TBL_SelectColumnsRight = Table.SelectColumns(Source,LST_LastN),
        TBL_SplitDelim1 = Table.ExpandListColumn(Table.TransformColumns(Source, {{STR_Field, Splitter.SplitTextByDelimiter(STR_Sep1, QuoteStyle.Csv)}}), STR_Field),
        TBL_SplitDelim2 = Table.SplitColumn(TBL_SplitDelim1, STR_Field, Splitter.SplitTextByDelimiter(STR_Sep2, QuoteStyle.Csv), {STR_Field_1, STR_Field_2}),
        STR_Each="each Text.Combine([" & STR_Field_2 & "],STR_Sep3)",
        EVAL_Each=Expression.Evaluate(STR_Each,[Text.Combine=Text.Combine, STR_Sep3=STR_Sep3]),
        TBL_Final = Table.Group(TBL_SplitDelim2, LST_FirstN, {{STR_FieldFinal, EVAL_Each, type text}}),
        TBL_TrimColum0 = Table.TransformColumns(TBL_Final,{{STR_FieldFinal, Text.Trim, type text}}),
        TBL_TrimColum = Table.TransformColumns(TBL_TrimColum0,{{STR_FieldFinal, each fnRemoveMaxSpaces(_), type text}})

    in
        TBL_TrimColum
in
    fnRemoveAndRankV2

PowerQuery:

let
    Source= fnRemoveAndRankV2
    (
        "TB_JO_BIDON",
        "CLASSEMENT",
        "CLASSEMENT FINAL",
        "DISCIPLINE",
        ",",
        "-",
        ", "
    )
in
    Source

méthode 3 - fnRemoveAndRankV3

PowerQuery:

let fnRemoveAndRankV3=(
        pSTR_Source as any,
        pSTR_Field as text,
        pSTR_FieldFinal as text,
        pSTR_GroupBy as text,
        pSTR_Sep1 as text,
        pSTR_Sep2 as text,
        pSTR_Sep3 as text
    ) =>
  
    let
        STR_Source=pSTR_Source,
        Source = if pSTR_Source is table then pSTR_Source else  Excel.CurrentWorkbook(){[Name=STR_Source]}[Content],
        STR_Field=pSTR_Field,
        STR_Field_1=STR_Field & ".1",
        STR_Field_2=STR_Field & ".2",
        STR_FieldFinal=pSTR_FieldFinal,
        STR_GroupBy=pSTR_GroupBy,
        STR_Sep1=pSTR_Sep1,
        STR_Sep2=pSTR_Sep2,
        STR_Sep3=pSTR_Sep3,

        STR_INDEX="tmp_Index",
        STR_TMP_INNER_COLUMN="tmp_Columun_InnerJoind",
        LST_Columns=Table.ColumnNames(Source),
        INT_ColCount=List.Count(LST_Columns),
        INT_Position=List.PositionOf(LST_Columns,STR_Field),
        LST_FirstN=List.FirstN(LST_Columns,INT_Position),
        LST_LastN=List.LastN(LST_Columns,INT_ColCount - INT_Position - 1),
        TBL_SelectColumnsRight = Table.SelectColumns(Source,LST_LastN),
        TBL_SplitDelim1 = Table.ExpandListColumn(Table.TransformColumns(Source, {{STR_Field, Splitter.SplitTextByDelimiter(STR_Sep1, QuoteStyle.Csv)}}), STR_Field),
        TBL_SplitDelim2 = Table.SplitColumn(TBL_SplitDelim1, STR_Field, Splitter.SplitTextByDelimiter(STR_Sep2, QuoteStyle.Csv), {STR_Field_1, STR_Field_2}),
        STR_Each="each Text.Combine([" & STR_Field_2 & "],STR_Sep3)",
        EVAL_Each=Expression.Evaluate(STR_Each,[Text.Combine=Text.Combine, STR_Sep3=STR_Sep3]),

        TBL_Final = Table.Group(TBL_SplitDelim2, LST_FirstN, {{STR_FieldFinal, EVAL_Each, type text}}), 
        TBL_IndexLeft = Table.AddIndexColumn(TBL_Final, STR_INDEX, 0, 1, Int64.Type),
        TBL_IndexRight = Table.AddIndexColumn(TBL_SelectColumnsRight, STR_INDEX, 0, 1, Int64.Type),
        TBL_InnerJoin=Table.NestedJoin(TBL_IndexLeft, {STR_INDEX}, TBL_IndexRight, {STR_INDEX}, STR_TMP_INNER_COLUMN, JoinKind.Inner),
        TBL_Expand = Table.ExpandTableColumn(TBL_InnerJoin, STR_TMP_INNER_COLUMN, LST_LastN, LST_LastN),
        TBL_DeleteIndex = Table.RemoveColumns(TBL_Expand,{STR_INDEX}),
        TBL_TrimColum0 = Table.TransformColumns(TBL_DeleteIndex,{{STR_FieldFinal, Text.Trim, type text}}),
        TBL_TrimColum = Table.TransformColumns(TBL_TrimColum0,{{STR_FieldFinal, each fnRemoveMaxSpaces(_), type text}})



    in
        TBL_TrimColum
in
    fnRemoveAndRankV3

Code:

PowerQuery:

let
    Source= fnRemoveAndRankV3
    (
        "TB_JO_BIDON",
        "CLASSEMENT",
        "CLASSEMENT FINAL",
        "DISCIPLINE",
        ",",
        "-",
        ", "
    )
in
    Source

méthode 3 - fnRemoveAndRankV4

PowerQuery:

let fnRemoveAndRankV4=(
        pSTR_Source as any,
        pSTR_Field as text
    ) =>

    let
        STR_Source=pSTR_Source,
        Source = if pSTR_Source is table then pSTR_Source else  Excel.CurrentWorkbook(){[Name=STR_Source]}[Content],
        STR_Field=pSTR_Field,
        LST_Char={"#","-","1","2","3","4","5","6","7","8","9","0", ";"},
        TBL_Final0=Table.TransformColumns(Source,{{STR_Field,
                            (x)=>
                                let
                                    V0=Text.Trim(Text.Remove(x, LST_Char)),
                                    V1=fnRemoveMaxSpaces(V0)
                                in
                                    V1                                             
                            , type text}}
                        ),     
        TBL_Final = Table.TransformColumns(TBL_Final0,{{STR_Field, each fnRemoveMaxSpaces(_), type text}})
    in
        TBL_Final
in
    fnRemoveAndRankV4

PowerQuery:

let
    Source= fnRemoveAndRankV4
    (
        "TB_JO_BIDON_AUTRE",
        "RESULTATS"
    )
in
    Source

Afin de supprimer les espaces inutiles entre chaque mot on fait appel à cette fonction développée pour l'occasion fnRemoveMaxSpaces

PowerQuery:

let fnRemoveMaxSpaces =(pSTR_String as text) =>
    let
        STR_String = pSTR_String,
        STR_TWO_BLANK="  ",
        STR_NULL_SPACE="",
        STR_BLANK=" ",
        LST_Split = Text.Split(STR_String,STR_TWO_BLANK),
        LST_Select = List.Select(LST_Split, (x)=> x<> STR_NULL_SPACE),
        STR_FinalStringCombine = Text.Combine(LST_Select,STR_BLANK)
    in
        STR_FinalStringCombine
in
    fnRemoveMaxSpaces

Pour apporter une conclusion sur ces 4 méthodes :
certes on pourrait préférer la version 4 que j'ai développé pour une version alternative..
Elle peut rendre de nombreux services pour des cas simples.
Ce qui me gêne un peu... on place en "dur" une liste de caractères à éliminer....
(on peut aussi passer cette chaine en paramètre puis Text.Split pour créer la liste... je n'ai pas voulu le faire )
Les 3 autres version certes un peu plus complexes, certains diront peut-être inutiles, elles n'apportent pas la contrainte d'une chaine de caractères à éliminer en "dur".
Selon les versions on a le choix sur les colonnes que l'on souhaite conserver.
Le code de ces 4 fonctions est très pédagogique pour les débutants. Une nouvelle fois via Expression.Evaluate on voit comment gérer des colonnes en dynamique.
Egalement dans la version, la petite astuce via un Inner.Join la possibilité de mettre bout à bout (concaténer) des colonnes et cela en passant par une colonne Index temporaire qui servira pour relier deux tables.

Autre forme d'extraction

On extrait les caractères entre [XYZ- et ]

Une fonction fnExtractFromString

PowerQuery:

let fnExtractFromString= (
        pSTR_Field as text,
        pSTR_Delim1 as text,
        pSTR_Delim2 as text,
        pSTR_Mask as text

    ) =>
  
    let
        STR_Field=pSTR_Field,
        STR_Delim1=pSTR_Delim1,
        STR_Delim2=pSTR_Delim2,
        STR_Mask=pSTR_Mask,
        STR_Default= "[" &STR_Field & "]",
      
        STR_Splitter="each try Splitter.SplitTextByEachDelimiter(  {""" & STR_Delim1 & STR_Mask & """, """ & STR_Delim2 & """}  )(  [" & STR_Field & "]  ){1} otherwise " & STR_Default,
      
        EVAL_Each=Expression.Evaluate(STR_Splitter,
                [
                    Splitter.SplitTextByEachDelimiter=Splitter.SplitTextByEachDelimiter,
                    STR_Delim1=STR_Delim1,
                    STR_Delim2=STR_Delim2,
                    STR_Field=STR_Field,
                    STR_Mask=STR_Mask,
                    STR_Default=STR_Default
               ]
            ),
        STR_ExtractFromMask = EVAL_Each 
    in
        STR_ExtractFromMask
in
    fnExtractFromString

Exemple
let
Source = #table({"COMMENTAIRES"}, {
{"Texte..."},
{"Texte...[XYZ-12345] Texte..."},
{"Texte...[XYZ-123456]"},
{"[XYZ-1234567]Texte... Texte..."}
}),
TBL_ExtractFromMask = Table.AddColumn(Source, "COMMENTAIRES_VALEURS", try fnExtractFromString ("COMMENTAIRES", "[", "]", "XYZ-") otherwise null)
in
TBL_ExtractFromMask

Autre souhait d'extraction : empiler les valeurs multiples extraites soit dans une liste soit dans une table

PowerQuery:

let fnTextBetweenDelimitersOccAll=
    (
        pSTR_InputText as text,
        pSTR_StartDelimiter as text,
        pSTR_EndDelimiter as text,
        optional pSTR_Alternative_Dummy as text,
        optional pB_ToTable as logical,
        optional pSTR_ColumnTitle as text
    ) =>

    let
        //****************************************************************************************************
        // Gestion des paramètres
        //****************************************************************************************************
        STR_AlternativeDummy = pSTR_Alternative_Dummy,
        // On va choisir un délimiteur improbable d'être utilisé si pas d'autre passé en paramètre
        STR_Dummy = if STR_AlternativeDummy is null then "µ" else STR_AlternativeDummy,
        STR_String = pSTR_InputText,
        STR_ParaStart = pSTR_StartDelimiter,
        STR_ParaEnd = pSTR_EndDelimiter,

        //****************************************************************************************************
        // Si on souhaite convertir la liste en table
        //****************************************************************************************************
        B_ToTable=if pB_ToTable is null then false else pB_ToTable,
        STR_ColumnTitle=if pSTR_ColumnTitle is null then "LISTE_VALEURS" else pSTR_ColumnTitle,

        //****************************************************************************************************
        // Pour complexifier on va prévoir que les séparateurs sont identiques.. au cas où.....
        //****************************************************************************************************
        B_IdenticalDelimiters = STR_ParaEnd = STR_ParaStart,
      
        //****************************************************************************************************
        // Donc on prévoit le coup si les séparateurs sont identiques pour la construction de la liste finale
        //****************************************************************************************************
        STR_SplitText = Text.Split(STR_String, STR_ParaStart),
        LST_ResultIdenticalDelimiters = List.Alternate(STR_SplitText,1,1),
      
        //****************************************************************************************************
        // On considère que les séparateurs sont différents
        // On vas passer par un délimiteur phantôme de fin par sécurité
        //****************************************************************************************************     
        STR_DummyParaEnd = Text.Replace(STR_ParaEnd, STR_ParaStart, STR_Dummy),
        STR_DummyString = Text.Replace(STR_String, STR_ParaEnd, STR_DummyParaEnd),

        //****************************************************************************************************
        // On commence à créer une 1ère liste
        //****************************************************************************************************
        LST_SplitByParaStart = List.Skip(Text.Split(STR_DummyString, STR_ParaStart),1),

        //****************************************************************************************************
        // Basculement temporaire des résultats dans une table
        //****************************************************************************************************
        TBL_ConvertToTable = Table.FromList(LST_SplitByParaStart, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        TBL_ExtractBeforeParaEnd = Table.AddColumn(TBL_ConvertToTable, "Result", each Text.BeforeDelimiter([Column1], STR_DummyParaEnd)),

        //****************************************************************************************************
        // Table temporaire définitive en supprimant la colonne de traitement qui devient inutile
        //****************************************************************************************************
        TBL_Cleanup = Table.RemoveColumns(TBL_ExtractBeforeParaEnd,{"Column1"}, MissingField.Ignore)[Result],

        //****************************************************************************************************
        // Enfin on construite la liste des éléments trouvés
        //****************************************************************************************************     
        ANY_Result = if Text.Contains(STR_String, STR_ParaStart) and Text.Contains(STR_String, STR_ParaEnd)
                    then (if B_IdenticalDelimiters
                            then LST_ResultIdenticalDelimiters
                            else TBL_Cleanup)
                    else error Error.Record( "ERREUR :: Un des séparateurs n'a pas été trouvé" ),

        ANY_TBL_ToTable=if B_ToTable
                        then
                            let
                                TBL_ToTable=Table.FromList(ANY_Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                                TBL_Rename = Table.RenameColumns(TBL_ToTable,{{"Column1", STR_ColumnTitle}})
                            in
                                TBL_Rename
                        else
                            ANY_Result
    in
        ANY_TBL_ToTable
in
    fnTextBetweenDelimitersOccAll

PowerQuery:

let
    Source = fnExtractAllOccurencesBetweenDelimiters("Texte...(123) Texte...(456) Texte...(789)... blabla", "(", ")")
in
    Source

PowerQuery:

= fnExtractAllOccurencesBetweenDelimiters("Texte...!123! Texte...!456! Texte...!789!... blabla", "!", "!")

PowerQuery:

= fnExtractAllOccurencesBetweenDelimiters("Texte...-123- Texte...-456- Texte...-789-... blabla...aerty -toto-", "-", "-",null,true,"LISTE")

Voir le fichier joint pour plus de compréhension des exemples présentés

POWERQUERY :: Extraction entre délimiteurs/Nettoyage de caractères suite....

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires