oguruma
XLDnaute Occasionnel
Sur la base de cette structure de fichiers (exemple de mesures de flux routiers sur des autoroutes - données bidons)
et un onglet par mois
et deux fichiers un pour les véhicules légers et un pour les véhicules lourds
obtenir par exemple les rapports suivants :
Environnement PowerQuery
Toutes les requêtes ne sont pas ici démontrées en détails. Je retiendrai les principales. A tester avec les fichiers joints.
On remarquera la présence de la fonction fnSelectCase qui est mise en application et expliquée ici --> https://excel-downloads.com/threads...-case-vba-ou-comme-en-dax-le-switch.20081152/ ainsi que les fonctions de totalisation documentées ici --> https://excel-downloads.com/threads...somme-des-lignes-somme-des-colonnes.20081021/
Celles qui nous intéressent pour le moment :
et son utilisation très simple
et plus complète avec des filtrages sur le dossier, fichier, extension, nom de table, etc
Et application du SelectCase
Nous obtenons ceci
A partir de cette catégorisation on peut effectuer des regroupements très facilement
pour obtenir ceci
ou ceci
et un onglet par mois
et deux fichiers un pour les véhicules légers et un pour les véhicules lourds
obtenir par exemple les rapports suivants :
Environnement PowerQuery
Toutes les requêtes ne sont pas ici démontrées en détails. Je retiendrai les principales. A tester avec les fichiers joints.
On remarquera la présence de la fonction fnSelectCase qui est mise en application et expliquée ici --> https://excel-downloads.com/threads...-case-vba-ou-comme-en-dax-le-switch.20081152/ ainsi que les fonctions de totalisation documentées ici --> https://excel-downloads.com/threads...somme-des-lignes-somme-des-colonnes.20081021/
Celles qui nous intéressent pour le moment :
PowerQuery:
let
//-----------------------------------------------------------------------------
// Combinaison d'un dossier de fichiers Excel
// On ne récupère que les objets tables - tableaux structurés - Kind = Table
//-----------------------------------------------------------------------------
fnCombineMultiTablesEXCEL_V = (
pFolder as text,
optional pFilterFolder as any,
optional pFilterExtention as any,
optional pFilterFiles as any,
optional pFilterTable as any,
optional pRefItem as any
) as table =>
let
//-----------------------------------------------------------------------------
// Fonction : On récupère le contenu du dossier
//-----------------------------------------------------------------------------
GetSource = () =>
let
Src = try
Folder.Files(pFolder)
otherwise
#table({},{}),
ToSrc = try
Src
otherwise
#table({},{}),
//------------------------------
// Filtre sur les dossiers
//-------------------------------
TbFilterFolder = try if pFilterFolder is null or pFilterFolder = false then
ToSrc
else Table.SelectRows(ToSrc, each [Folder Path] = pFilterFolder)
otherwise
#table({},{}),
//------------------------------
// Filtre sur l'extension
//------------------------------
TbFilterExtension = try if pFilterExtention is null or pFilterExtention = false then
TbFilterFolder
else Table.SelectRows(TbFilterFolder, each Text.Contains([Extension], pFilterExtention))
otherwise
#table({},{}),
//------------------------------
// Filtre sur les fichiers
//------------------------------
TbFilterFiles = try if pFilterFiles is null or pFilterFiles = false then
TbFilterExtension
else Table.SelectRows(TbFilterExtension, each Text.StartsWith([Name], pFilterFiles))
otherwise
#table({},{})
in
TbFilterFiles,
//-----------------------------------------------------------------------------
// Fonction : On récupère la liste des binaires
//-----------------------------------------------------------------------------
GetContent = () =>
let
SrcContent = Source[Content],
ToContent = try
SrcContent
otherwise
#table({},{})
in
SrcContent,
//-----------------------------------------------------------------------------
// Fonction : Construction de la table finale
//-----------------------------------------------------------------------------
Build = () =>
let
//-----------------------------------------------------------------------------
// Pour chaque binaire on récupère les tables (tableaux structurés)
//-----------------------------------------------------------------------------
TbListCombine = List.Accumulate(
Content,
#table({},{}),
(state,current) =>
let
Classeur=Excel.Workbook(current),
// On ne retient que les tableaux structurés
Filter=Table.SelectRows(Classeur, each ([Kind] = "Table")),
CombineFilter=Table.Combine({state,Filter})
in
CombineFilter
),
//-----------------------------------------------------------------------------
// Filtre éventuel sur les noms des tables à récupérer
//-----------------------------------------------------------------------------
FilterTable = try if pFilterTable is null or pFilterTable = false then
TbListCombine
else Table.SelectRows(TbListCombine, each Text.StartsWith([Item], pFilterTable))
otherwise
#table({},{}),
//-----------------------------------------------------------------------------
// On extrait le les colonnes du 1er classeur qui va servir de référence
//-----------------------------------------------------------------------------
ListData=if Table.IsEmpty(FilterTable) then
{}
else FilterTable[Data],
ColNameRef={"Item"},
ListColumns= if List.IsEmpty(ListData) then
{} else
Table.ColumnNames(ListData{0}),
//-----------------------------------------------------------------------------
// On récupère les data dans les binaires pour les convertir en table
//-----------------------------------------------------------------------------
ToData = Table.ExpandTableColumn(FilterTable, "Data", ListColumns),
//---------------------------------------------------------------------------------
// On ne retient que les colonnes de référence avec Item (nom de la table) ou pas
//---------------------------------------------------------------------------------
ToSelectColumns = if pRefItem is null or pRefItem = false then
Table.SelectColumns(ToData, ListColumns)
else Table.SelectColumns(ToData, ColNameRef & ListColumns)
in
ToSelectColumns,
//-----------------------------------------------------------------------------
// MAIN PROCEDURE
//-----------------------------------------------------------------------------
Source = GetSource(),
Content = if not Table.IsEmpty(Source) then
GetContent()
else #table({"ERROR"},{{"#ERROR EMPTY"}}),
ToBuild = if List.IsEmpty(Content) then
#table({},{})
else Build()
in
ToBuild
in
fnCombineMultiTablesEXCEL_Vet son utilisation très simple
PowerQuery:
= fnCombineMultiTablesEXCEL_V("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX")et plus complète avec des filtrages sur le dossier, fichier, extension, nom de table, etc
PowerQuery:
let
Source = fnCombineMultiTablesEXCEL_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
null,
true
),Et application du SelectCase
PowerQuery:
let
Source = fnCombineMultiTablesEXCEL_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
null,
true
),
[B] AddType = Table.AddColumn(Source, "TypeVehicule", each fnSelectCase(Text.Middle([Item],0,8),{"TB_LEGER","TB_LOURD"},{"VEHICULE LEGER","VEHICULE LOURD"})),
#"Lignes filtrées" = Table.SelectRows(AddType, each true)[/B]
in
#"Lignes filtrées"Nous obtenons ceci
A partir de cette catégorisation on peut effectuer des regroupements très facilement
PowerQuery:
let
Source = fnCombineMultiTablesEXCEL_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
null,
true
),
AddType = Table.AddColumn(Source, "TypeVehicule", each fnSelectCase(Text.Middle([Item],0,8),{"TB_LEGER","TB_LOURD"},{"LEGER","LOURD"})),
#"Lignes groupées" = Table.Group(AddType, {"TypeVehicule"}, {{"Nombre", each List.Sum([NbFlux]), type number}})
in
#"Lignes groupées"pour obtenir ceci
ou ceci
PowerQuery:
let
Source = fnCombineMultiTablesEXCEL_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
null,
true
),
AddType = Table.AddColumn(Source, "TypeVehicule", each fnSelectCase(Text.Middle([Item],0,8),{"TB_LEGER","TB_LOURD"},{"LEGER","LOURD"})),
#"Lignes groupées" = Table.Group(AddType, {"TypeVehicule", "Date"}, {{"Nombre", each List.Sum([NbFlux]), type number}}),
#"Colonne dynamique" = Table.Pivot(#"Lignes groupées", List.Distinct(#"Lignes groupées"[TypeVehicule]), "TypeVehicule", "Nombre", List.Sum),
#"Lignes triées" = Table.Sort(#"Colonne dynamique",{{"Date", Order.Ascending}}),
#"Type modifié" = Table.TransformColumnTypes(#"Lignes triées",{{"LOURD", Int64.Type}, {"LEGER", Int64.Type}})
in
#"Type modifié"
PowerQuery:
let
Source = fnCombineMultiTablesEXCEL_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
"TB",
true
),
AddType = Table.AddColumn(Source, "TypeVehicule", each fnSelectCase(Text.Middle([Item],0,8),{"TB_LEGER","TB_LOURD"},{"LEGER","LOURD"})),
Totals=fnTotalEachColumnsV(AddType,3,"Total journalier",true)
in
Totals
PowerQuery:
let
Source = fnCombineMultiTablesEXCELGroupBy_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"Autoroute",
"Nombre flux",
"NbFlux",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
"L",
"TB",
true
),
Totals=fnTotalEachColumnsV (Source,1,"Total autoroute",true)
in
Totals
PowerQuery:
let
Source = fnCombineMultiTablesEXCELPivot_V(
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS",
"Autoroute",
"Nombre flux",
"NbFlux",
"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\FLUX_ROUTIERS\FLUX\",
"xl",
false,
"TB",
true
),
Totals = fnTotalEachColumnsHV(Source,2,"Total flux journaliers","Total autoroute",true)
in
Totals
Dernière édition: