POWERQUERY :: Unpivot d'une table de données style Comptable - transposition dans une table structurée - exploitable pour construire des TCD

oguruma

XLDnaute Occasionnel
Qui n'a pas eu à traiter des tableaux comme celui-ci ?

Exemple : ici j'ai simulé des ventes de produits mais on peut aussi traiter des lignes de budgets, etc.

Respecter cette disposition

1708376264479.png

Vous avez le choix sur les libellés.

Respecter aussi cette forme de ventilation et les noms de colonnes sont libres. Ils sont repris automatiquement dans les traitements.
1708376325485.png


Enfin ces noms sont aussi libres mais uniquement 2 colonnes (pour l'instant dans cette version)
1708376370896.png

Ici c'est gamme & produits mais il peut en être autrement selon vos cas à traiter.

1708375339535.png


Constat : ce tableau est inexploitable directement si on souhaite construire des TCD.

L'idée est donc de le transformer pour obtenir un tableau structuré comme celui-ci

1708375431487.png


On retient que l'on produira un tableau de synthèse avec 5 colonnes
1708376480958.png
sont issues du tableau initial

1708376496026.png
seront renseignées dans un tableau de paramètres (Onglet Params)

Et avec ce type de tableau il est donc désormais de construire des TCD comme ceux-ci par exemple

1708375484345.png


Environnement PowerQuery
1708375534093.png


LoadPQ : Requête qui permet de charger une requête externe ; montage très pratique pour modifier le source via VSC par exemple.
RQ_RUN : Requête qui permet l'exécution de la requête chargée et de produire les résultats


PowerQuery:
let
    TbData =  fnGetParameter("TB_PARAMS", "TB_DATA"),
    Qry =  fnGetParameter("TB_PARAMS", "RUN_QUERY"),
    Run = LoadPQ(Qry),
    ToTable=Run(TbData,TB_COLONNES)
in
    ToTable

TB_COLONNES : Table de paramétrage qui expose les colonnes à traiter et leur mode de traitement pour le unpivot

1708375731098.png


La colonne SPLIT sera celle utilisée pour découpage des données

Celles UNPIVOT correspondent à celles-ci
1708375808974.png


Le n° correspondent à l'ordre d'affichage donc /!\

Celles PIVOT correspondent à celles-ci
1708375847510.png


En conclusion si vos tableaux ressemblent à cette structure ça fonctionnera à merveille.
Dans la lecture du code vous constaterez que rien n'est en "dur". Le calculs des noms de colonnes est dynamique en fonction des noms référencés dans la table paramètres TB_COLONNES. L'importance est de bien respecter l'ordre.


Source pour le UnPivot

PowerQuery:
let
    Run = (
            pStrTbData    as text,      // Table des données à pivoter
            TB_COLONNES  as table      // Table qui inventorie les colonnes à afficher
          ) =>

        let
            //-------------------------------------------------------------------------
            // Constantes techniques
            //-------------------------------------------------------------------------
            COLUMN_MERGED="Merged",
            LIST_COLUMNS_TO_MERGE = {"Column1", "Column2"},
            COLUMN1="Column1",
            DELIMITER =";",
            ATTRIBUTE="Attribute",
            VALUE="Value",
            PREFIX_ATTRIBUTE="Attribute",

            //-------------------------------------------------------------------------
            // Liste des colonnes identifiées dans la table paramètres colonnes
            //-------------------------------------------------------------------------
            ListColumnsTBCol=Table.ColumnNames(TB_COLONNES),

            //-------------------------------------------------------------------------
            // Ordre des colonnes à afficher
            //-------------------------------------------------------------------------
            ListColumnsToReorder=Table.Column(TB_COLONNES,ListColumnsTBCol{0}),

            //-------------------------------------------------------------------------
            // Colonne pivot pour le découpafe
            //-------------------------------------------------------------------------
            ListColumnSplitt=Table.Column(TB_COLONNES,ListColumnsTBCol{1}),          

            //-------------------------------------------------------------------------
            // Liste des colonnes unpivot
            //-------------------------------------------------------------------------
            ListColumnUnpivot=Table.Column(TB_COLONNES,ListColumnsTBCol{2}),          

            //-------------------------------------------------------------------------
            // Liste des colonnes pivot
            //-------------------------------------------------------------------------
            ListColumnPivot=Table.Column(TB_COLONNES,ListColumnsTBCol{3}),          
           
            //-------------------------------------------------------------------------
            // Colonne de découpage identifiée par la lettre X
            //-------------------------------------------------------------------------
            ColumnToSplitt=ListColumnsToReorder{List.PositionOf(ListColumnSplitt,"X")},

            //-------------------------------------------------------------------------
            // Colonnes à gauche de la colonne découpage pivot
            //-------------------------------------------------------------------------
            ColumnUnpivot1=ListColumnsToReorder{List.PositionOf(ListColumnUnpivot,1)},
            ColumnUnpivot2=ListColumnsToReorder{List.PositionOf(ListColumnUnpivot,2)},

            //-------------------------------------------------------------------------
            // Colonnes à droite de la colonne décooupage pivot
            //-------------------------------------------------------------------------          
            ColumnPivot1=ListColumnsToReorder{List.PositionOf(ListColumnPivot,3)},
            ColumnPivot2=ListColumnsToReorder{List.PositionOf(ListColumnPivot,4)},
            ColumnPivot3=ListColumnsToReorder{List.PositionOf(ListColumnPivot,5)},

            //-------------------------------------------------------------------------
            // Constantes fonctionnelles
            //-------------------------------------------------------------------------
            REORDER_COLUMNS=ListColumnsToReorder,          
            COLUMNS_TO_UNPIVOT=ATTRIBUTE,
            COLUMN_TO_SPLIT=ColumnToSplitt,

            //-------------------------------------------------------------------------
            // On considère à minimal que les lignes sous-total contiennent Total
            // A modifier si autre chose
            //-------------------------------------------------------------------------
            COLUMN_TOTAL="Total",
           
            //-------------------------------------------------------------------------
            // On récupère le nom du tableau à traiter
            //-------------------------------------------------------------------------
            Source = Excel.CurrentWorkbook(){[Name=pStrTbData]}[Content],

            //-------------------------------------------------------------------------
            // Liste des colonnes du tableau à traiter
            //-------------------------------------------------------------------------
            ListTempColumns = Table.ColumnNames(Source),

            //-------------------------------------------------------------------------
            // On procède au découpage & unpivot
            //-------------------------------------------------------------------------          
            FilledDown01 = Table.FillDown(Source,{COLUMN1}),
            MergedColumns = Table.CombineColumns(FilledDown01,LIST_COLUMNS_TO_MERGE,Combiner.CombineTextByDelimiter(DELIMITER, QuoteStyle.None),COLUMN_MERGED),
            TransposedToTable = Table.Transpose(MergedColumns),
            FilledDown02 = Table.FillDown(TransposedToTable,{COLUMN1}),
            PromotedHeaders = Table.PromoteHeaders(FilledDown02, [PromoteAllScalars=true]),

            //-------------------------------------------------------------------------
            // Dépivotage
            //-------------------------------------------------------------------------
            UnpivotedOtherColumns = Table.UnpivotOtherColumns(PromotedHeaders, {DELIMITER, ColumnUnpivot1 & DELIMITER & ColumnUnpivot2}, ATTRIBUTE, VALUE),

            //-------------------------------------------------------------------------
            // Découpage
            //-------------------------------------------------------------------------
            SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns, COLUMNS_TO_UNPIVOT, Splitter.SplitTextByDelimiter(DELIMITER, QuoteStyle.Csv), {PREFIX_ATTRIBUTE & ".1", PREFIX_ATTRIBUTE & ".2"}),

            //------------------------------------------------------------------------------
            // Renommage des colonnes temporaire selon le tableau des colonnes en paramètre
            //------------------------------------------------------------------------------
            RenColumns = Table.RenameColumns(
                            SplitColumnByDelimiter,
                                {{DELIMITER, COLUMN_TO_SPLIT},
                                {ColumnUnpivot1 & DELIMITER & ColumnUnpivot2, ColumnPivot2},
                                {PREFIX_ATTRIBUTE & ".1", ColumnUnpivot1},
                                {PREFIX_ATTRIBUTE & ".2", ColumnUnpivot2},
                                {VALUE, ColumnPivot3}}
                        ),          
           
            //-------------------------------------------------------------------------
            // On prépare la suppression des lignes sous-total
            //-------------------------------------------------------------------------
            StrGammeNonTotalEnds="Text.EndsWith([" & ColumnUnpivot1 & "], COLUMN_TOTAL)",
            StrGammeNonTotalStarts="Text.StartsWith([" & ColumnUnpivot1 & "], COLUMN_TOTAL)",
            StrNonTotal="each not (" & StrGammeNonTotalEnds & " or " & StrGammeNonTotalStarts  & ")",
            EvalStrGammeNonTotal=Expression.Evaluate(StrNonTotal,[COLUMN_TOTAL=COLUMN_TOTAL,
                                                                  Text.EndsWith=Text.EndsWith,
                                                                  Text.StartsWith=Text.StartsWith]
                                                                  ),                                                                
            FilteredRows = Table.SelectRows(RenColumns,EvalStrGammeNonTotal ),          

            //-------------------------------------------------------------------------
            // Typage arbitraire any par défaut et monétaire pour le total
            // à adapter selon les besoins
            //-------------------------------------------------------------------------
            ChangedType = Table.TransformColumnTypes(FilteredRows,{{ColumnPivot1, type any}, {ColumnPivot2, type any}, {ColumnPivot3, Currency.Type}}),          

            //-------------------------------------------------------------------------
            // On alignes les colonnes selon la table de paramètres Colonnes
            //-------------------------------------------------------------------------
            ReorderedColumns = Table.ReorderColumns(ChangedType,REORDER_COLUMNS),          

            //-------------------------------------------------------------------------
            // On élimine les lignes null que l'on s'est récupéré pendant le découpage
            //-------------------------------------------------------------------------
            StrMontantNonNull="each ([" & ColumnPivot3 & "] <> null)",
            EvalMontantNonNull=Expression.Evaluate(StrMontantNonNull,#shared),          
            FilteredRowsToTable = Table.SelectRows(ReorderedColumns, EvalMontantNonNull)

            //-------------------------------------------------------------------------
            // ET ! enfin c'est fini !!!
            //-------------------------------------------------------------------------
        in
            FilteredRowsToTable
in
    Run

Les tables de paramètres
1708376175497.png


Pour plus de compréhension voir le fichier joint et l'exploiter pas à pas. Pour sa modification je recommande VSC et vous y installez les extensions PowerQuery qui fonctionnent très bien
 

Pièces jointes

  • $__pqPivotUnpivotAdvanced.zip
    92.7 KB · Affichages: 2

Discussions similaires

Statistiques des forums

Discussions
313 328
Messages
2 097 220
Membres
106 880
dernier inscrit
Michel2024