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
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.
Enfin ces noms sont aussi libres mais uniquement 2 colonnes (pour l'instant dans cette version)
Ici c'est gamme & produits mais il peut en être autrement selon vos cas à traiter.
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
On retient que l'on produira un tableau de synthèse avec 5 colonnes
Et avec ce type de tableau il est donc désormais de construire des TCD comme ceux-ci par exemple
Environnement PowerQuery
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
TB_COLONNES : Table de paramétrage qui expose les colonnes à traiter et leur mode de traitement pour le unpivot
La colonne SPLIT sera celle utilisée pour découpage des données
Celles UNPIVOT correspondent à celles-ci
Le n° correspondent à l'ordre d'affichage donc /!\
Celles PIVOT correspondent à celles-ci
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
Les tables de paramètres
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
Exemple : ici j'ai simulé des ventes de produits mais on peut aussi traiter des lignes de budgets, etc.
Respecter cette disposition
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.
Enfin ces noms sont aussi libres mais uniquement 2 colonnes (pour l'instant dans cette version)
Ici c'est gamme & produits mais il peut en être autrement selon vos cas à traiter.
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
On retient que l'on produira un tableau de synthèse avec 5 colonnes
Et avec ce type de tableau il est donc désormais de construire des TCD comme ceux-ci par exemple
Environnement PowerQuery
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
ToTableTB_COLONNES : Table de paramétrage qui expose les colonnes à traiter et leur mode de traitement pour le unpivot
La colonne SPLIT sera celle utilisée pour découpage des données
Celles UNPIVOT correspondent à celles-ci
Le n° correspondent à l'ordre d'affichage donc /!\
Celles PIVOT correspondent à celles-ci
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
RunLes tables de paramètres
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