oguruma
XLDnaute Occasionnel
Dans ce post l'idée est de consolider des onglets de même nature d'un classeur externe à partir d'un classeur de consolidation.
Exemple de classeur source - un onglet pour chaque année :
Le but étant de consolider dans un seul classeur les 3 années contenues dans un classeur externe.
La requête effectue les opérations suivantes
- lecture du classeur source et capture de tous les onglets
- renommage des colonnes
- typage des colonnes
Pour la table de mapping peu importe le nom des colonnes la requête les gère dynamiquement. Il suffit de respecter l'ordre ci-dessous
Les paramètres
Exemple de classeur source - un onglet pour chaque année :
Le but étant de consolider dans un seul classeur les 3 années contenues dans un classeur externe.
La requête effectue les opérations suivantes
- lecture du classeur source et capture de tous les onglets
- renommage des colonnes
- typage des colonnes
Pour la table de mapping peu importe le nom des colonnes la requête les gère dynamiquement. Il suffit de respecter l'ordre ci-dessous
Les paramètres
PowerQuery:
let
//------------------------------------------------------------------------------------------------
// Combinaison des onglets d'un fichier Excel externe
// avec renommage et typage des colonnes
//------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------
// On récupère les paramètres
//------------------------------------------------------------------------------------------------
// Le classeur avec ses onglets
FilePath = fnGetParameters("TB_PARAMS","DOSSIER") & "\" & fnGetParameters("TB_PARAMS","FICHIER"),
// La table de mapping avec les nouveaux types
T_Mapping=fnGetParameters("TB_PARAMS","TABLE_RENAME"),
//------------------------------------------------------------------------------------------------
// Table de mapping
//------------------------------------------------------------------------------------------------
TbMapping = Excel.CurrentWorkbook(){[Name=T_Mapping]}[Content],
//------------------------------------------------------------------------------------------------
// On prépare la table de mapping pour typer les colonnes
//------------------------------------------------------------------------------------------------
ListColumnsTbMapping=Table.ColumnNames(TbMapping),
Ancien=ListColumnsTbMapping{0},
Nouveau=ListColumnsTbMapping{1},
Type=ListColumnsTbMapping{2},
//------------------------------------------------------------------------------------------------
// On prépare le mapping des noms de colonnes
//------------------------------------------------------------------------------------------------
ToStringAncien="TbMapping[" & Ancien & "]",
ListAncien=Expression.Evaluate(ToStringAncien,[TbMapping=TbMapping]),
ToStringNouveau="TbMapping[" & Nouveau & "]",
ListNouveau=Expression.Evaluate(ToStringNouveau,[TbMapping=TbMapping]),
ToStringType="TbMapping[" & Type & "]",
ListType=Expression.Evaluate(ToStringType,[TbMapping=TbMapping]),
//------------------------------------------------------------------------------------------------
// On applique le renommage des colonnes
ChangeColTypes = Table.TransformColumnTypes(TbMapping,{{ListColumnsTbMapping{0}, type text}, {ListColumnsTbMapping{1}, type text}, {ListColumnsTbMapping{2}, type text}}),
//------------------------------------------------------------------------------------------------
// Suppression de la colonne ancien dans la table de mapping pour attérir sur le nouveau nom
//------------------------------------------------------------------------------------------------
SupprColAncien = Table.RemoveColumns(ChangeColTypes,{"ANCIEN"}),
//------------------------------------------------------------------------------------------------
// Construction du contexte powerquery pour le typage des colonnes
//------------------------------------------------------------------------------------------------
ToType = Table.TransformColumns(SupprColAncien,{{ListColumnsTbMapping{2}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),
//------------------------------------------------------------------------------------------------
// Traitements sur les colonnes pour le typage
//------------------------------------------------------------------------------------------------
ToField = Table.AddColumn(ToType, "CustomTypes", each Record.FieldValues(_)),
RemoveOtherCols = Table.SelectColumns(ToField,{"CustomTypes"}),
ToListType = RemoveOtherCols[CustomTypes],
//------------------------------------------------------------------------------------------------
// Capture des onglets
//------------------------------------------------------------------------------------------------
Source = Excel.Workbook(File.Contents(FilePath), null, true),
Filter = Table.SelectRows(Source, each ([Kind] = "Sheet")),
TransformColumns_TABLE = Table.TransformColumns(Filter,{{"Data", each Table.PromoteHeaders(_), type table}}),
//------------------------------------------------------------------------------------------------
// Renommage des colonnes
//------------------------------------------------------------------------------------------------
TransformColumns_ZIP = Table.TransformColumns(TransformColumns_TABLE, {{"Data", each Table.RenameColumns(_, List.Zip({ListAncien,ListNouveau}) , MissingField.Ignore), type table}}),
//------------------------------------------------------------------------------------------------
// Transformation en table de données
//------------------------------------------------------------------------------------------------
Columns_DATA = Table.SelectColumns(TransformColumns_ZIP,{"Data"}),
ToTable = Table.ExpandTableColumn(Columns_DATA, "Data", ListNouveau, ListNouveau),
//------------------------------------------------------------------------------------------------
// Application des types sur les colonnes
//------------------------------------------------------------------------------------------------
ToTableType = Table.TransformColumnTypes(ToTable,ToListType)
in
ToTableType
PowerQuery:
let
fnGetParameters = (pTable as text, pName as any) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([PARAMETRE] = pName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"VALEUR")
in
Value
in
fnGetParameters
