POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2

[oguruma]

Bonjour,

L’objet de ce post résume ceux déposés à propos trucs & astuces POWERQUERY concernant les importations de dossiers contenant soit des fichiers Excel soit des fichiers .csv.

Certaines fonctions ont été aussi revues et corrigées par rapport à leurs versions initiales déposées dans ce forum.

Le thème retenu pour présenter ces fonctionnalités : La Certification dynamique de données (ma spécialité désormais depuis quelques années – chef de projets).

Tout ce qui est cité est totalement fictif. Pour autant si certains sont dans ce domaine ça pourrait les aider à faire évoluer leurs tableaux car le vocabulaire reste le même. Les exemples développés pourraient servir de socles pour leurs besoins personnels.

Les fonctions sont largement documentées et elles seront reprises une à une dans ce post. Afin de ne pas alourdir le fichier de synthèse toutes les requêtes ne sont pas chargées. Je vous invite donc à les consulter dans l’éditeur de requêtes PowerQuery.

Une liste de code Source que j'invite les débutants et ceux qui souhaiteraient franchir le pas pour se mettre à PowerQuery et au langage M. En effet quand on creuse le sujet ce n'est de la simple formule à la "sauce Excel" mais dans certains de la réelle programmation avec parfois un peu d'algorithme. Je vous invite à "détourer" (pour ceux qui ne maîtrisent pas encore) les bloc let ... in que l'on peut assimiler à un bloc d'instructions que l'on peut placer un peu partout quand le résultat à obtenir nécessaire plusieurs traitements pour l'étape suivante. On remarquera aussi dans certaines fonctions que l'on peut découper les traitements en sous-fonctions un peu comme des procédures ou fonctions en VBA. Cela facilite la maintenance et permet par quelques astuces quand cela est possible de sortir proprement du code sans planter en renvoyant un tableau vide #table({},{}) ou une liste vide {}.
Enfin dans certains utilitaires on remarquera aussi la puissance de la fonction List.Accumulate sans oublier l'éternel Expression.Evaluate. Certes elles ne sont pas simples au premier abord mais dès que on les maîtrise un peu c'est la porte des solutions compliquées.

Bonne lecture et apprentissage.

Cette synthèse traite les points suivants :

• L’importation d’un dossier contenant des fichiers .csv
• L’importation d’un dossier contenant des fichier Excel
• L’importation d’une liste de fichiers .csv spécifiques à partir d’un dossier
• Possible que dans dossier on souhaite importer des .csv particuliers
• L’importation d’une liste de fichiers Excel spécifiques à partir d’un dossier
• Possible que dans dossier on souhaite importer des fichiers Excel particuliers
• L’importation d’un seul et unique fichier .csv
• L’importation d’un seul et unique fichier Excel soit via un tableau structuré (si défini) soit via son onglet (quand il n’y a pas de de tableau structuré)
• Le renommage « à la volée » des colonnes à l’issue de l’importation ou pendant
• La gestion « à la volée » des types de colonnes à l’issue de l’importation
• La totalisation des tableaux soit en ligne soit en colonne soit les deux à l’issue de l’importation
• La possibilité de pivoter des colonnes à l’issue de l’importation
• La possibilité de dépivoter des colonnes à l’issue de l’importation
• La recherche de données dans d’autres tables via la simulation du RECHERCHEX (XLOOKUPV)
• La recherche de données dans d’autres tables via la simulation du EQUIV/INDEX
• La simulation du SELECT CASE ou comme SWITCH comme certains l’appellent
• La possibilité de faire des cumuls sur une colonne
• La possibilité de calculer dans une colonne les % par rapport à son total
• La possibilité d’effectuer des regroupements à l’issue de l’importation

Pour toutes ces fonctionnalités ci-dessus des fonctions PowerQuery ont été développées.



L’IMPORTATION DE DOSSIERS CSV ET FICHIERS CSV

Table de paramétrage : TB_PARAMS_CSV

PARAMETRE	VALEUR
DOSSIER_CSV	D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION
DELIMITEUR_CSV	;
ENCODE_CSV	65001
NBCOLS_CSV	4
FICHIER_CSV_1	TECHNIC_ARPEGE.csv
FICHIER_CSV_2	TECHNIC_BLIND.csv
LISTE_FICHIERS_CSV	TECHNIC_SOLO.csv;TECHNIC_IRON.csv;TECHNIC_GAMMA.csv
FILTRE_CSV	TECHNIC
EXTENSION	.csv

Les différentes tables de mapping pour le renommage et typage des colonnes

TBL_TYPES_CSV

COLONNE	TYPE
PHASE_CERTIFICATION	type text
CODE_APPLI	type text
INSTANCES_SERVEUR	type number
PUISSANCE_MACHINE	type number
TBL_RENOM_CSV
ANCIEN	NOUVEAU		TBL_RENOM_CSV2
PHASE_CERTIFICATION	Phase		ANCIEN	NOUVEAU
CODE_APPLI	Application		PHASE_CERTIFICATION	Phase
INSTANCES_SERVEUR	Instance		CODE_APPLI	Application
PUISSANCE_MACHINE	Puissance		TOT_INSTANCES	Total instances

Exemple après importation d’un dossier contenant des fichiers .csv

RQ_COMBINE_CSV_BY_STEP
PHASE_CERTIFICATION	CODE_APPLI	INSTANCES_SERVEUR	PUISSANCE_MACHINE
CD0	BC	10​	1969​
CD0	AB	7​	3056​
CD0	SK	5​	4991​
CD1	WA	7​	2374​
CD1	OR	6​	2445​
CD1	ID	6​	1812​

let

SOURCE_CONTENT="Content",



pPath=fnGetParameter("TB_PARAMS_CSV","DOSSIER_CSV"),

pDelim=fnGetParameter("TB_PARAMS_CSV","DELIMITEUR_CSV"),

pEncoding=fnGetParameter("TB_PARAMS_CSV","ENCODE_CSV"),



//-------------------------------------------------------------------------------------

// Fonction pour la lecture des binaires bufferisés

//-------------------------------------------------------------------------------------

fnReadBinaryFile=(pFile2 as binary) as table =>

Table.PromoteHeaders( Csv.Document(pFile2, [Delimiter=pDelim,Encoding=pEncoding])),



//-------------------------------------------------------------------------------------

// Définition de la source de données

//-------------------------------------------------------------------------------------

Source = Folder.Files(pPath),

FilterCSV = Table.SelectRows(Source, each ([Extension] = ".csv")),

FilterFile = Table.SelectRows(FilterCSV, each Text.StartsWith([Name], "TECHNIC")),



//-------------------------------------------------------------------------------------

// Bufferisation des binaires

//-------------------------------------------------------------------------------------

DrillDownContent = Table.SelectColumns(FilterFile,{SOURCE_CONTENT}),

ListTransform = List.Transform (DrillDownContent[Content], Binary.Buffer),

//-------------------------------------------------------------------------------------

// Transformation et lecture des binaires

//-------------------------------------------------------------------------------------

ReadBinaryFiles1 =List.Transform(ListTransform, each Table.PromoteHeaders( Csv.Document(_, [Delimiter=pDelim,Encoding=pEncoding]))),

// List.Transform(ListTransform, fnReadBinaryFile),



ReadBinaryFiles=ReadBinaryFiles1,

//-------------------------------------------------------------------------------------

// Combinaison du fichier

//-------------------------------------------------------------------------------------

TableCombine = Table.Combine(ReadBinaryFiles )



in

Table.TransformColumnTypes(TableCombine,

if fnTransformTypes("TBL_TYPES_CSV") is null then

Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(TableCombine), each {_, type any}))

else try

fnTransformTypes("TBL_TYPES_CSV")

otherwise

Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(TableCombine), each {_, type any}))

)

Importation automatisée via la table de paramétrage

let

//-------------------------------------------------------------------------------------

// Combinaison des fichiers contenus dans un dossier

//-------------------------------------------------------------------------------------



//-------------------------------------------------------------------------------------

// On récupère les paramètres

//-------------------------------------------------------------------------------------

pPath=fnGetParameter("TB_PARAMS_CSV","DOSSIER_CSV"),

pDelim=fnGetParameter("TB_PARAMS_CSV","DELIMITEUR_CSV"),

pNbCols=fnGetParameter("TB_PARAMS_CSV","NBCOLS_CSV"),

pEncoding=fnGetParameter("TB_PARAMS_CSV","ENCODE_CSV"),

pFilterFile=fnGetParameter("TB_PARAMS_CSV","FILTRE_CSV"),

pExt=fnGetParameter("TB_PARAMS_CSV","EXTENSION"),



//-------------------------------------------------------------------------------------

// Fonction permettant de combiner les fichiers présents dans un dossier

//-------------------------------------------------------------------------------------

// Source = fnGetFromFolderCSV(pPath, pDelim, pEncoding, null, pExt)



//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des fichiers .csv

//----------------------------------------------------------------------------------

Source = fnGetFromFolderCSV(pPath),



//----------------------------------------------------------------------------------

// Applicaton de la transformation des types

//----------------------------------------------------------------------------------

ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),



//----------------------------------------------------------------------------------

// Application du renommage des colonnes

//----------------------------------------------------------------------------------

ToTableRename=fnRenameColumns(ToTableModifType,"TBL_RENOM_CSV"),



//----------------------------------------------------------------------------------

// Aplication du XLOOPV forme 1

//----------------------------------------------------------------------------------

AddColumnsMOE = Table.AddColumn(ToTableRename, "MOE", each fnXLookupV1([Application],"TBL_APPLIS_CSV","CODE_APPLI","MOE",true,"Inconnu"))

in

AddColumnsMOE

Appel de la fonction : Source = fnGetFromFolderCSV(pPath)

let fnGetFromFolder = (

pPath as text,

optional ppDelim as text,

optional ppEncoding as number,

optional ppFileFilter as text,

optional ppExtension as text

) as table =>



//---------------------------------------------------------------------------------

// Combinaison du contenu d'un dossier

// Ici nous passons pas par le code mis automatiquement quand on fait appel à l'assistant

// L’importation s'effectue directement via les binaires des fichiers

//---------------------------------------------------------------------------------



let

SOURCE_CONTENT="Content",

pDelim=if ppDelim is null then ";" else ppDelim,

pEncoding=if ppEncoding is null then 65001 else ppEncoding,

pExtention=if ppExtension is null then ".csv" else ppExtension,

pFileFilter=if ppFileFilter is null then "*" else ppFileFilter,



//-------------------------------------------------------------------------------------

// Fonction pour la lecture des binaires bufferisés

//-------------------------------------------------------------------------------------

fnReadBinaryFile=(

pFile as binary

) as table =>

Table.PromoteHeaders( Csv.Document(pFile, [Delimiter=pDelim,Encoding=pEncoding])),



//-------------------------------------------------------------------------------------

// Définition de la source de données et filtres sur les fichiers et extension

//-------------------------------------------------------------------------------------

SourcePath = Folder.Files(pPath),

FilterFile = if pFileFilter = "*" then

SourcePath

else Table.SelectRows(SourcePath, each Text.StartsWith([Name], pFileFilter)),

Source = Table.SelectRows(FilterFile, each ([Extension] = pExtention)),



//-------------------------------------------------------------------------------------

// Bufferisation des binaires

//-------------------------------------------------------------------------------------

DrillDownContent = Table.SelectColumns(Source,{SOURCE_CONTENT}),

ListTransform = List.Transform (DrillDownContent[Content], Binary.Buffer),



//-------------------------------------------------------------------------------------

// Transformation et lecture des binaires

//-------------------------------------------------------------------------------------

ReadBinaryFiles = List.Transform(ListTransform, fnReadBinaryFile),



//-------------------------------------------------------------------------------------

// Combinaison du fichier

//-------------------------------------------------------------------------------------

TableCombine = Table.Combine(ReadBinaryFiles )

in

TableCombine

in

try fnGetFromFolder otherwise null

Importation – requête RQ_GetFromFolderCSV_2

On fait appel également aux fonctions :

• Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),
• fnCumulTable(ToTableModifType,"INSTANCES_SERVEUR","Cumul instances"),
• Table.AddColumn(ToTablePct, "LIB_APPLI", each fnIndexExcel("TBL_APPLIS_CSV",fnEquivExcel("TBL_APPLIS_CSV",0,[CODE_APPLI]),1))
• AddColumnsNotation = Table.AddColumn(TypePCT, "Difficulté", each fnSelectCase([CODE_APPLI], "BC;AB;SK;WK", "Sans impact;Faible;Moyen;Elevé", "Hors quota", null, null)),
• AddColumnsPlateau = Table.AddColumn(AddColumnsNotation, "Plateau test", each fnXLookupV2("TBL_LIB_CD", "PHASE_CERTIFICATION", [PHASE_CERTIFICATION], "PLATEAU_TEST")),
• fnTotalEachColumnsV (ReorderColumns, 7, "Total puissance Instance", true)

let

//-------------------------------------------------------------------------------------

// Combinaison des fichiers contenus dans un dossier

//-------------------------------------------------------------------------------------



//-------------------------------------------------------------------------------------

// On récupère les paramètrs

//-------------------------------------------------------------------------------------

pPath=fnGetParameter("TB_PARAMS_CSV","DOSSIER_CSV"),

pDelim=fnGetParameter("TB_PARAMS_CSV","DELIMITEUR_CSV"),

pNbCols=fnGetParameter("TB_PARAMS_CSV","NBCOLS_CSV"),

pEncoding=fnGetParameter("TB_PARAMS_CSV","ENCODE_CSV"),

pFilterFile=fnGetParameter("TB_PARAMS_CSV","FILTRE_CSV"),

pExt=fnGetParameter("TB_PARAMS_CSV","EXTENSION"),



Source = fnGetFromFolderCSV(pPath),



//----------------------------------------------------------------------------------

// Modification des types des colonnes

//----------------------------------------------------------------------------------

ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),



//----------------------------------------------------------------------------------

// Application des fonctions de cumuls et Pct

//----------------------------------------------------------------------------------

TableToCumul = fnCumulTable(ToTableModifType,"INSTANCES_SERVEUR","Cumul instances"),

ToTablePct=fnPct(TableToCumul,"PUISSANCE_MACHINE","Pct Puissance"),



//----------------------------------------------------------------------------------

// Application du INDEX/EQUIV vs EXCEL

//----------------------------------------------------------------------------------

AddLibAppliColumns = Table.AddColumn(ToTablePct, "LIB_APPLI", each fnIndexExcel("TBL_APPLIS_CSV",fnEquivExcel("TBL_APPLIS_CSV",0,[CODE_APPLI]),1)),

TypePCT = Table.TransformColumnTypes(AddLibAppliColumns,{{"Pct Puissance", Percentage.Type}}),



//----------------------------------------------------------------------------------

// Application du SELECT CASE

//----------------------------------------------------------------------------------

AddColumnsNotation = Table.AddColumn(TypePCT, "Difficulté", each fnSelectCase([CODE_APPLI], "BC;AB;SK;WK", "Sans impact;Faible;Moyen;Elevé", "Hors quota", null, null)),



//----------------------------------------------------------------------------------

// Application du XLOOKUP (Excel) forme 2

//----------------------------------------------------------------------------------

AddColumnsPlateau = Table.AddColumn(AddColumnsNotation, "Plateau test", each fnXLookupV2("TBL_LIB_CD", "PHASE_CERTIFICATION", [PHASE_CERTIFICATION], "PLATEAU_TEST")),

ReorderColumns = Table.ReorderColumns(AddColumnsPlateau,{"PHASE_CERTIFICATION", "CODE_APPLI", "LIB_APPLI", "Plateau test", "Difficulté", "Cumul instances", "Pct Puissance", "INSTANCES_SERVEUR", "PUISSANCE_MACHINE"}),



//----------------------------------------------------------------------------------

// Application de la totalisation verticale

//----------------------------------------------------------------------------------

ToTableCumulV= fnTotalEachColumnsV (ReorderColumns, 7, "Total puissance Instance", true)



in

ToTableCumulV

Importation selon une liste de fichiers passée en paramètre - RQ_COMBINE_CSV_BYLIST

On fait appel aux fonctions :

• Source=fnCombineFolderCSVByList(pPath, pDelim, pEncoding, pNbCols, pList),
• ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),
• ToTableRename=fnRenameColumns(ToTableModifType,"TBL_RENOM_CSV")

let

//---------------------------------------------------------------------------------

// Appel de la fonction permettant de combiner plusieurs fichiers spécifiques

// Ces fichiers sont définis dans la table des paramètres

//---------------------------------------------------------------------------------

//-------------------------------------------------------------------------------------

// On récupère les paramètrs

//-------------------------------------------------------------------------------------

pPath=fnGetParameter("TB_PARAMS_CSV","DOSSIER_CSV"),

pDelim=fnGetParameter("TB_PARAMS_CSV","DELIMITEUR_CSV"),

pEncoding=fnGetParameter("TB_PARAMS_CSV","ENCODE_CSV"),

pNbCols=fnGetParameter("TB_PARAMS_CSV","NBCOLS_CSV"),

pList=fnGetParameter("TB_PARAMS_CSV","LISTE_FICHIERS_CSV"),



//----------------------------------------------------------------------------------

// Application de la combinaison par une liste de fichiers fournis

//----------------------------------------------------------------------------------

Source=fnCombineFolderCSVByList(pPath, pDelim, pEncoding, pNbCols, pList),



ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),



//----------------------------------------------------------------------------------

// Application du renommage des colonnes

//----------------------------------------------------------------------------------

ToTableRename=fnRenameColumns(ToTableModifType,"TBL_RENOM_CSV")

in

ToTableRename

Importation et regroupement à l’issue - RQ_COMBINE_CSV_FOLDER_GROUPBY

On fait appel aux fonctions :

• Source = fnGetFromFolderCSV(pPath),
• ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),
• Groupby_Phase = Table.Group(ToTableModifType, {"PHASE_CERTIFICATION", "CODE_APPLI"}, {{"TOT_INSTANCES", each List.Sum([INSTANCES_SERVEUR]), type nullable number}}),
• 
  
• ToTableRename=fnRenameColumns(Groupby_Phase,"TBL_RENOM_CSV2")

let

//-------------------------------------------------------------------------------------

// Combinaison des fichiers contenus dans un dossier

//-------------------------------------------------------------------------------------



//-------------------------------------------------------------------------------------

// On récupère les paramètrs

//-------------------------------------------------------------------------------------

pPath=fnGetParameter("TB_PARAMS_CSV","DOSSIER_CSV"),

pDelim=fnGetParameter("TB_PARAMS_CSV","DELIMITEUR_CSV"),

pNbCols=fnGetParameter("TB_PARAMS_CSV","NBCOLS_CSV"),

pEncoding=fnGetParameter("TB_PARAMS_CSV","ENCODE_CSV"),

pFilterFile=fnGetParameter("TB_PARAMS_CSV","FILTRE_CSV"),

pExt=fnGetParameter("TB_PARAMS_CSV","EXTENSION"),





//-------------------------------------------------------------------------------------

// Fonction permettant de combiner les fichiers présents dans un dossier

//-------------------------------------------------------------------------------------

// Source = fnGetFromFolderCSV(pPath, pDelim, pEncoding, null, pExt)



//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des fichiers .csb=v

//----------------------------------------------------------------------------------

Source = fnGetFromFolderCSV(pPath),



//----------------------------------------------------------------------------------

// Application du typage des colonnes

//----------------------------------------------------------------------------------

ToTableModifType=Table.TransformColumnTypes(Source,fnTransformTypes("TBL_TYPES_CSV")),

Groupby_Phase = Table.Group(ToTableModifType, {"PHASE_CERTIFICATION", "CODE_APPLI"}, {{"TOT_INSTANCES", each List.Sum([INSTANCES_SERVEUR]), type nullable number}}),



//----------------------------------------------------------------------------------

// Application du renommage des colonnes

//----------------------------------------------------------------------------------

ToTableRename=fnRenameColumns(Groupby_Phase,"TBL_RENOM_CSV2")

in

ToTableRename

Fonction permettant d’importer un .CSV

let fnGetCSV = (

pFolder as text,

pFile as text,

pNbCols as number,

optional ppDelim as text,

optional ppEncoding as any

) as table =>



//---------------------------------------------------------------------------------

// Chargement d'un fichier texte .csv

//---------------------------------------------------------------------------------



let

//---------------------------------------------------------------------------------

// Paramètres par défaut

//---------------------------------------------------------------------------------

pDelim = if ppDelim is null then ";" else ppDelim,

pEncoding=if ppEncoding is null then 65001 else ppEncoding,



//---------------------------------------------------------------------------------

// Paramètres techniques d'importation d'un fichier .csv

//---------------------------------------------------------------------------------

pParamCSV=[Delimiter=pDelim, Columns=pNbCols, Encoding=pEncoding, QuoteStyle=QuoteStyle.None],



//---------------------------------------------------------------------------------

// Le combine nécessite une liste de table à combiner

//---------------------------------------------------------------------------------

pPath=pFolder & "\" & pFile,



//---------------------------------------------------------------------------------

// Importation du fichier

//---------------------------------------------------------------------------------

Source = Csv.Document(File.Contents(pPath),pParamCSV),



//---------------------------------------------------------------------------------

// Titre des colonnes

//---------------------------------------------------------------------------------

ToTable = Table.PromoteHeaders(Source, [PromoteAllScalars=true])



in

ToTable

in

try fnGetCSV otherwise null

Fonction permettant d’importer une liste de fichiers .csv – fnCombineFolderCSVByList

A travers celle-ci on voit la mise en œuvre de List.Accumulate

let

//---------------------------------------------------------------------------------

// Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier

// Ce dossier et ces fichiers sont définis dans une table paramètres

//---------------------------------------------------------------------------------



// La table des paramètres et le paramètre contenu les fichiers à combiner

fnCombineByList = (

pPath as text,

pDelim as text,

pEncoding as any,

pNbCols as number,

pList as text,

optional ppSep as text

) as table =>

let

pSep=if ppSep is null then ";" else ppSep,

LstFiles=Text.Split(pList,pSep), // Liste des fichiers à combiner



//-------------------------------------------------------------------------------------

// On va boucler sur la liste des fichiers à combiner

//-------------------------------------------------------------------------------------

LstCombine=List.Accumulate(

LstFiles, // Initialisation de la boucle sur la liste à traiter

#table({},{}), // Initialisation de l'accumulateur sur une table vide

(state,current) => // Boucle de traitement pour combiner les fichiers un à un

let

Source = fnGetCSV(pPath, current, pNbCols, pDelim, pEncoding), // Importation du fichier en cours

Combine = Table.Combine( {state , Source}) // Combinaison du fichier en cours avec ceux déja combinés dans state

in

Combine // Ensemble des fichiers combinés

)

in

LstCombine

in

fnCombineByList // Résultat de la fonction

Fonction permettant d’importer un dossier contenant des fichiers .CSV - fnGetFromFolderCSV

let fnGetFromFolder = (

pPath as text,

optional ppDelim as text,

optional ppEncoding as number,

optional ppFileFilter as text,

optional ppExtension as text

) as table =>



//---------------------------------------------------------------------------------

// Combinaison du contenu d'un dossier

// Ici nous passons pas par le code mis automatiquement quand on fait appel à l'assistant

// Limportation s'effectue directement via les binaires des fichiers

//---------------------------------------------------------------------------------



let

SOURCE_CONTENT="Content",

pDelim=if ppDelim is null then ";" else ppDelim,

pEncoding=if ppEncoding is null then 65001 else ppEncoding,

pExtention=if ppExtension is null then ".csv" else ppExtension,

pFileFilter=if ppFileFilter is null then "*" else ppFileFilter,



//-------------------------------------------------------------------------------------

// Fonction pour la lecture des binaires bufferisés

//-------------------------------------------------------------------------------------

fnReadBinaryFile=(

pFile as binary

) as table =>

Table.PromoteHeaders( Csv.Document(pFile, [Delimiter=pDelim,Encoding=pEncoding])),



//-------------------------------------------------------------------------------------

// Définition de la source de données et filtres sur les fichiers et extension

//-------------------------------------------------------------------------------------

SourcePath = Folder.Files(pPath),

FilterFile = if pFileFilter = "*" then

SourcePath

else Table.SelectRows(SourcePath, each Text.StartsWith([Name], pFileFilter)),

Source = Table.SelectRows(FilterFile, each ([Extension] = pExtention)),



//-------------------------------------------------------------------------------------

// Bufferisation des binaires

//-------------------------------------------------------------------------------------

DrillDownContent = Table.SelectColumns(Source,{SOURCE_CONTENT}),

ListTransform = List.Transform (DrillDownContent[Content], Binary.Buffer),



//-------------------------------------------------------------------------------------

// Transformation et lecture des binaires

//-------------------------------------------------------------------------------------

ReadBinaryFiles = List.Transform(ListTransform, fnReadBinaryFile),



//-------------------------------------------------------------------------------------

// Combinaison du fichier

//-------------------------------------------------------------------------------------

TableCombine = Table.Combine(ReadBinaryFiles )

in

TableCombine

in

try fnGetFromFolder otherwise null

IMPORTATION DE DOSSIERS CONTENANT DES FICHIERS EXCEL

Exemple de table de paramétrage utilisées dans les importations .CSV et EXCEL

PARAMETRE	VALEUR
DOSSIER_EXCEL	D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION
FILTRE_SOUS_DOSSIER_EXCEL	D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021\
FILTRE_FICHIERS
FILTRE_EXTENSION	.xlsx
FILTRE_TABLE_EXCEL	TBP_
COLONNES_TECHNIQUES	OUI
FICHIER_EXCEL_1	STRAT_GAMME.xlsx
FICHIER_EXCEL_2	STRAT_GAMMA.xlsx
LISTE_FICHIERS_EXCEL	STRAT_GAMME.xlsx;STRAT_GAMMA.xlsx
COLONNES_TRI	DossierExcel
OFFSET_CUMUL	13
TITRE_COLONNE
TITRE_LIGNE
DOSSIER_FICHIER	D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021
PHASE_CERTIFICATION	NOM_CERTIF	NOM_DU_CP	PLATEAU_TEST
CD0	Rodage Technique	CP_NOM1	Plateau1
CD1	Rodage réseau	CP_NOM2	Plateau2
CD2	Rodage disque	CP_NOM3	Plateau3
CD3	Rodage flux	CP_NOM4	Plateau4
CD4	Rodage Alpha	CP_NOM5	Plateau5
CD5	Rodage Golden	CP_NOM6	Plateau6

CODE_APPLI	LIB_APPLI	MOE
BC	Comptabilité	NOM_MOE1
AB	Paie	NOM_MOE2
SK	Commercial	NOM_MOE3
WA	Marketing	NOM_MOE4
OR	Frais généraux	NOM_MOE5
ID	Abonnements	NOM_MOE6
MO	Agences commerciales	NOM_MOE7
CA	Stratégie	NOM_MOE8

TB_RENOM_CERT_DYNAM
ANCIEN	NOUVEAU
PHASE_CERTIFICATION	Phase
CODE_APPLI	Code application
COMMENT	Commentaires
NB_TRAITEMENENTS	Nbr de traitements
DEVELOPPEMENT	Env. Développement
INTEGRATION	Env. Intégration
QUALIFICATION	Env. Qualification
HOMOL1	Env. Homol Niv. 1
HOMOL2	Env. Homol Niv. 2
RECETTE	Env. Recette métiers

TB_RENAME_TB_COMPO		TB_TYPE_TB_COMPO
ANCIEN	NOUVEAU		ANCIEN	NOUVEAU
PHASE_CERTIFICATION	Phase de certif.		PHASE_CERTIFICATION	type text
CODE_APPLI	Application		CODE_APPLI	type text
VOLUMETRIE_GO	Volumétrie en Go		VOLUMETRIE_GO	type number

TB_TYPE_CERT_DYNAM
COLONNE	TYPE
PHASE_CERTIFICATION	type text
CODE_APPLI	type text
COMMENT	type text
NB_TRAITEMENENTS	type number
DEVELOPPEMENT	type number
INTEGRATION	type number
QUALIFICATION	type number
HOMOL1	type number
HOMOL2	type number
RECETTE	type number

ANCIEN	NOUVEAU	TYPE
PHASE_CERTIFICATION	Phase de CD	type text
CODE_APPLI	Code application	type text
VOLUMETRIE_GO	Volumétrie en Go	type number

TB_MAPPING_RENAME_TYPE2
ANCIEN	NOUVEAU	TYPE
PHASE_CERTIFICATION	Phase	type text
CODE_APPLI	Code application	type text
COMMENT	Commentaires	type text
NB_TRAITEMENENTS	Nbr de traitements	type number
DEVELOPPEMENT	Env. Développement	type number
INTEGRATION	Env. Intégration	type number
QUALIFICATION	Env. Qualification	type number
HOMOL1	Env. Homol Niv. 1	type number
HOMOL2	Env. Homol Niv. 2	type number
RECETTE	Env. Recette métiers	type number

Importer un fichier EXCEL

let

Source = fnGetEXCEL (

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\Sales_CSV",

"VENTES_AA.xlsx",

null,

"Sheet"



)

in

Source

Importer un fichier Excel

let

Source = fnGetEXCEL (

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqCombineExcelFiles\Sales_CSV",

"VENTES_A.xlsx",

"V-A",

"Sheet"



)

in

Source

Importer un fichier Excel

let

Source = fnGetEXCEL (

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",

"STRAT_GAMMA.xlsx",

"Technique",

"Sheet",

3

)

in

Source

Importer un fichier Excel

let

Source = fnGetEXCEL (

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",

"STRAT_GAMMA.xlsx",

"TB_COMPOSANTS",

"Table"

)

in

Source

Importer un fichier Excel

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL","DOSSIER_FICHIER"),

pFile=fnGetParameter("TB_PARAMS_EXCEL","FICHIER_EXCEL_1"),

Source = fnGetEXCEL (pFolder, pFile, "TB_COMPOSANTS", "Table")

in

Source

Importer un dossier contenant des fichiers Excel

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL","DOSSIER_EXCEL"),

pSubFolder=fnGetParameter("TB_PARAMS_EXCEL","FILTRE_SOUS_DOSSIER_EXCEL"),

pFiles=fnGetParameter("TB_PARAMS_EXCEL","FILTRE_FICHIERS"),

pExt=fnGetParameter("TB_PARAMS_EXCEL","FILTRE_EXTENSION"),

pTable=fnGetParameter("TB_PARAMS_EXCEL","FILTRE_TABLE_EXCEL"),

pColTech=fnGetParameter("TB_PARAMS_EXCEL","COLONNES_TECHNIQUES"),

pColSort=fnGetParameter("TB_PARAMS_EXCEL","COLONNES_TRI"),





//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des classeurs Excel

//----------------------------------------------------------------------------------

Source = fnCombineMultiTablesEXCEL_H_Table(

pFolder,

pSubFolder,

pFiles,

pExt,

pTable,

if Text.Upper(Text.Trim(pColTech))="OUI" then true else false,

pColSort

)

in

Source

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL2","DOSSIER_EXCEL"),

pSubFolder=fnGetParameter("TB_PARAMS_EXCEL2","FILTRE_SOUS_DOSSIER_EXCEL"),

pFiles=fnGetParameter("TB_PARAMS_EXCEL2","FILTRE_FICHIERS"),

pExt=fnGetParameter("TB_PARAMS_EXCEL2","FILTRE_EXTENSION"),

pTable=fnGetParameter("TB_PARAMS_EXCEL2","FILTRE_TABLE_EXCEL"),

pColTech=fnGetParameter("TB_PARAMS_EXCEL2","COLONNES_TECHNIQUES"),

pColSort=fnGetParameter("TB_PARAMS_EXCEL2","COLONNES_TRI"),

pOffset=fnGetParameter("TB_PARAMS_EXCEL2","OFFSET_CUMUL"),

pLibTotalH=fnGetParameter("TB_PARAMS_EXCEL2","TITRE_LIGNE"),

pLibTotalV=fnGetParameter("TB_PARAMS_EXCEL2","TITRE_COLONNE"),



//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des classeurs Excel

//----------------------------------------------------------------------------------



Source = fnCombineMultiTablesEXCEL_H_Table(

pFolder,

pSubFolder,

pFiles,

pExt,

pTable,

if Text.Upper(Text.Trim(pColTech))="OUI" then true else false,

pColSort

)

in

fnTotalEachColumnsHV (Source, pOffset, pLibTotalH, pLibTotalV as text, true)

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL3","DOSSIER_EXCEL"),

pSubFolder=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_SOUS_DOSSIER_EXCEL"),

pFiles=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_FICHIERS"),

pExt=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_EXTENSION"),

pTable=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_TABLE_EXCEL"),

pColTech=fnGetParameter("TB_PARAMS_EXCEL3","COLONNES_TECHNIQUES"),

pColSort=fnGetParameter("TB_PARAMS_EXCEL3","COLONNES_TRI"),

pOffset=fnGetParameter("TB_PARAMS_EXCEL3","OFFSET_CUMUL"),

pLibTotalV=fnGetParameter("TB_PARAMS_EXCEL3","TITRE_COLONNE"),



//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des classeurs Excel

//----------------------------------------------------------------------------------



Source = fnCombineMultiTablesEXCEL_H_Table(

pFolder,

pSubFolder,

pFiles,

pExt,

pTable,

if Text.Upper(Text.Trim(pColTech))="OUI" then true else false,

pColSort

),

TotalV= fnTotalEachColumnsV (Source, pOffset, pLibTotalV, true)

in

TotalV

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL3","DOSSIER_EXCEL"),

pSubFolder=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_SOUS_DOSSIER_EXCEL"),

pFiles=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_FICHIERS"),

pExt=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_EXTENSION"),

pTable=fnGetParameter("TB_PARAMS_EXCEL3","FILTRE_TABLE_EXCEL"),

pColTech=fnGetParameter("TB_PARAMS_EXCEL3","COLONNES_TECHNIQUES"),

pColSort=fnGetParameter("TB_PARAMS_EXCEL3","COLONNES_TRI"),

pOffset=fnGetParameter("TB_PARAMS_EXCEL3","OFFSET_CUMUL"),

pLibTotalV=fnGetParameter("TB_PARAMS_EXCEL3","TITRE_COLONNE"),



//----------------------------------------------------------------------------------

// Application de la capture d'un dossier contenant des classeurs Excel

//----------------------------------------------------------------------------------



Source = fnCombineMultiTablesEXCEL_H_Table(

pFolder,

pSubFolder,

pFiles,

pExt,

pTable,

if Text.Upper(Text.Trim(pColTech))="OUI" then true else false,

pColSort

),

TotalV= fnTotalEachColumnsV (Source, pOffset, pLibTotalV, true),





ToPivot=fnPivotTable(TotalV,"CODE_APPLI","VOLUMETRIE_GO")

in

ToPivot

let

Source = fnCombineMultiTablesEXCEL_V_Table("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",null,"STRAT_CHROME",null,"TB_COMPOSANTS",true)

in

Source

let



Source = fnCombineMultiTablesEXCEL_H_Sheet(

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__ComBineSynthese\PQ_FUNCTIONS",

null,

"PROJ",

"Technique"

)

in

Source

let



Source = fnCombineMultiTablesEXCEL_H_Sheet(

"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__ComBineSynthese\PQ_FUNCTIONS",

null,

"PROJ",

"Technique"

),

NbrColumns=Table.ColumnCount(Source),

ListColumnsSource=Table.ColumnNames(Source),

ListColumnsUnpivot=List.FirstN(ListColumnsSource, 4),

UnpivotSource= fnUnpivotTable(Source, ListColumnsUnpivot, "Environnement", "Nbr Incidents" )

in

UnpivotSource

let

pFolder=fnGetParameter("TB_PARAMS_EXCEL","DOSSIER_FICHIER"),

pList=fnGetParameter("TB_PARAMS_EXCEL","LISTE_FICHIERS_EXCEL"),

Source = fnCombineEXCELByList(pFolder, pList, "TBP_TECH", "Table",null)

in

Source

let

Source = fnGetExcelTypeRename("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021","STRAT_CHROME.xlsx","TB_MAPPING_RENAME_TYPE","TB_COMPOSANTS","Table")

in

Source

let

Source = fnGetExcelTypeRename("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021","STRAT_CHROME.xlsx","TB_MAPPING_RENAME_TYPE2","TBP_TECH","Table")

in

Source

let

Source = fnGetExcelTypeRename("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__ComBineSynthese\PQ_FUNCTIONS\01_XLS","STRAT_CHROME.xlsx","TB_MAPPING_RENAME_TYPE","Composants","Sheet",3)

in

Source

LES UTILITAIRES

let fnGetParameter =

(

pTable as text,

pName as text

) =>



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

fnGetParameter

let

//----------------------------------------------------------------------------------------------

// La fonction attend la table des types pour chaque colonne

// La table pass�e en param�tre peut �tre soit de type text soit de type table

//----------------------------------------------------------------------------------------------



fnTransformTypes=(pTable as any) as list =>

let

//------------------------------------------------------------------------------------------

// On �value le type de param�tre table

//------------------------------------------------------------------------------------------

Source= if pTable is text

then Excel.CurrentWorkbook(){[Name=pTable]}[Content]

else if pTable is table then pTable else null,



//-----------------------------------------------------------------------------------------------

// La table des types doit comporter deux colonnes

// colonne 1 : nom de la colonne � transformer

// colonne 2 : type de la colonne

// les noms des colonnes ne sont pas impos�s puisque la fonction les d�tecte � la ligne suivante

//------------------------------------------------------------------------------------------------

LstCol=try Table.ColumnNames(Source) otherwise {},



//------------------------------------------------------------------------------------------

// Les noms des colonnes sont r�cup�r�s dans la variable de liste LstCol

// LstCol{0} = colonne 1 - nom des colonnes

// LstCol{0} = colonne 2 - types de colonnes

//------------------------------------------------------------------------------------------

ChangeColTypes = if List.IsEmpty(LstCol) then

#table({},{})

else Table.TransformColumnTypes(Source,{{LstCol{0}, type text}, {LstCol{1}, type text}}),



//------------------------------------------------------------------------------------------

// On prend aussi en compte les types non primitifs

// [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]

// Astuce importante :

// Il est n�cessaire de les d�clarer afin qu'ils soient reconnus dans le contexte PQ

//------------------------------------------------------------------------------------------

ToType = if Table.IsEmpty(ChangeColTypes) then

#table({},{})

else Table.TransformColumns(ChangeColTypes,{{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),



ToField = if Table.IsEmpty(ToType) then

#table({},{})

else Table.AddColumn(ToType, "CustomTypes", each Record.FieldValues(_)),



//------------------------------------------------------------------------------------------

// Cr�ation de la liste des types � appliquer � chaque colonne

//------------------------------------------------------------------------------------------

RemoveOtherCols = if Table.IsEmpty(ToField) then

#table({},{})

else Table.SelectColumns(ToField,{"CustomTypes"}),



ToList = if Table.IsEmpty(RemoveOtherCols) then

#table({},{})

else RemoveOtherCols[CustomTypes]

in

try ToList otherwise null



in

try fnTransformTypes otherwise null

let fnRenameColumns = (pSource as any, pSourceRen as any ) as table =>

let

Source=if pSource is text then

Excel.CurrentWorkbook(){[Name=pSource]}[Content]

else pSource,



SourceRen=if pSourceRen is text then

Excel.CurrentWorkbook(){[Name=pSourceRen]}[Content]

else pSourceRen,



LstTblRename=Table.ColumnNames(SourceRen),



LstOldNames=Expression.Evaluate("SourceRen[" & LstTblRename{0} & "]",[SourceRen=SourceRen]),

LstNewNames=Expression.Evaluate("SourceRen[" & LstTblRename{1} & "]",[SourceRen=SourceRen]),



RenameColumns=Table.RenameColumns( Source,

List.Zip( { LstOldNames,LstNewNames } ),

MissingField.Ignore )

in

RenameColumns

in

fnRenameColumns

let pqXLOOKUPV1 = (

pLookupValue as any,

pTableName as any,

tmpColumnKeyNumberIndex as any,

tmpColumnReturnIndexNumber as any,

pSort as logical,

optional tmpMSG as any

) as any =>



let



pMSG = if tmpMSG = null then "#N/A" else tmpMSG,

pColumnKeyNumberIndex = if tmpColumnKeyNumberIndex = 0 then 1 else tmpColumnKeyNumberIndex,

pColumnReturnIndexNumber = if tmpColumnReturnIndexNumber = 0 then 1 else tmpColumnReturnIndexNumber,

pTableArray = if pTableName is table then pTableName else Excel.CurrentWorkbook(){[Name=pTableName]}[Content],



Columns = Table.ColumnNames(pTableArray),



pKeyIndex=if tmpColumnKeyNumberIndex is text then List.PositionOf(Columns,tmpColumnKeyNumberIndex) else tmpColumnKeyNumberIndex - 1,

pKeyReturn=if tmpColumnReturnIndexNumber is text then List.PositionOf(Columns,tmpColumnReturnIndexNumber) else tmpColumnReturnIndexNumber - 1,



ColumnsTable = Table.FromList(

Columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),



ColumnNameMatch = Record.Field(

ColumnsTable{pKeyIndex},"Column1"),



ColumnNameReturn = Record.Field(

ColumnsTable{pKeyReturn},"Column1"),



SortTable =

if pSort = true

then Table.Sort(pTableArray,{{ColumnNameMatch, Order.Ascending}})

else pTableArray,



RenameTargetCol = Table.RenameColumns(

SortTable,{{ColumnNameMatch, "__Lookup__"}}),



Lookup = Table.SelectRows(

RenameTargetCol, each [__Lookup__] = pLookupValue),



ReturnValue=

if Table.IsEmpty(Lookup)=true

then pMSG

// Get first record mandatory

else Record.Field(Lookup{0},ColumnNameReturn)



in ReturnValue



in pqXLOOKUPV1

let

//----------------------------------------------------------------------------------------

// Cette fonction simule de manière simplifiée la fonction RechercheX sous Excel

// pTable : table des données

// pField1 : colonne de recherche

// pKey : clef de recherche

// pField2 : colonne résultat

//----------------------------------------------------------------------------------------



fnXLookupIndex = (pTable as any, pField1 as any, pKey as any, pField2 as any) as any =>



let

ParamSource = if pTable is text then

Excel.CurrentWorkbook(){[Name=pTable]}[Content]

else pTable,



//------------------------------------------------------------------------------------

// Construction de la chaine de recherche

//------------------------------------------------------------------------------------

ToString=if pKey is text then

"each ([" & pField1 & "] = """ & pKey & """)"

else if pKey is number then

"each ([" & pField1 & "] = " & Number.ToText(pKey) & ")"

else null,



//------------------------------------------------------------------------------------

// Evaluation PowerQuery de la chaine de recherche

//------------------------------------------------------------------------------------

ToEval=Expression.Evaluate(ToString),



//------------------------------------------------------------------------------------

// Recherche dans la table

//------------------------------------------------------------------------------------

ParamRow = Table.SelectRows(ParamSource, ToEval),



//------------------------------------------------------------------------------------

// On sécurise le retour si la clef n'est pas trouvée

//------------------------------------------------------------------------------------

Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},pField2)



in

try Value otherwise null



in

fnXLookupIndex

let



//----------------------------------------------------------------------------------------

// Simulatiion de la fonction EQUIV (Excel)

//----------------------------------------------------------------------------------------



fnEquivExcel =(pTableSource as any, pCol as number, pKey as any) as any =>



let



//-------------------------------------------------------------------------------------

// On récupère les noms de colonnes de la table

//-------------------------------------------------------------------------------------



pTable=if pTableSource is text then

Excel.CurrentWorkbook(){[Name=pTableSource]}[Content]

else pTableSource,



ListColumns=Table.ColumnNames(pTable),



//-------------------------------------------------------------------------------------

// Sécurisation si la colonne est < 0

//-------------------------------------------------------------------------------------

TargetColumn=if pCol < 0 then

null

else ListColumns{pCol},



//-------------------------------------------------------------------------------------

// On récupère le contenu de la colonne

//-------------------------------------------------------------------------------------

ListValues=Table.Column(pTable,TargetColumn),



//-------------------------------------------------------------------------------------

// On recherche la clef pour récupèrer la ligne où elle se trouve

//-------------------------------------------------------------------------------------

Position=List.PositionOf(ListValues,pKey)



in

//-------------------------------------------------------------------------------------

// Sécuritsation si la valeur n'est pas trouvée

//-------------------------------------------------------------------------------------

if Position < 0 then null else Position

in

fnEquivExcel

let

//----------------------------------------------------------------------------------------

// Simulatiion de la fonction INDEX (Excel)

//----------------------------------------------------------------------------------------

fnIndex =(pTableSource as any, pRow as number, pColumn as number) as any =>



let



pTable=if pTableSource is text then

Excel.CurrentWorkbook(){[Name=pTableSource]}[Content]

else pTableSource,



NbRec=Table.RowCount(pTable),



Rec = if pRow > NbRec

or pRow < 0

or pColumn < 0 then null else pTable{pRow},



NbFields=Record.FieldCount(Rec),



RecReturnValue=if pColumn > NbFields then

null

else Record.SelectFields(Rec,Record.FieldNames(Rec){pColumn})

in

Record.ToTable(RecReturnValue)[Value]{0}

in

fnIndex

let fnCumulTable=(TableName as any, ColumnTotal as text, ColumnNameCumul as text) as table =>

let

fnCumul = (tbl as table, sumcolumn as text, rowindex as number) =>



let

RemovedOtherColumns = Table.SelectColumns(tbl,{sumcolumn, "µµIndexµµ"}),

FilteredRows = Table.SelectRows(RemovedOtherColumns, each [µµIndexµµ] <= rowindex),

RenamedColumns = Table.RenameColumns(FilteredRows,{{sumcolumn, "__µTempµ__"}}),

GroupedRows = Table.Group(RenamedColumns, {}, {{"µµRunningTotalµµ", each List.Sum([__µTempµ__]), type number}}),

TotalCumul = Record.Field(GroupedRows{0},"µµRunningTotalµµ")

in

TotalCumul,



Source =

if TableName is table

then TableName

else Excel.CurrentWorkbook(){[Name=TableName]}[Content],



SourceIndexed = Table.AddIndexColumn(Source, "µµIndexµµ", 0, 1),

CumulIndex = Table.AddColumn(SourceIndexed, ColumnNameCumul, each fnCumul(SourceIndexed,ColumnTotal,[µµIndexµµ])),

SupprTmpIndex = Table.RemoveColumns(CumulIndex,{"µµIndexµµ"}),

CumulTypeNumber = Table.TransformColumnTypes(SupprTmpIndex,{{ColumnNameCumul, type number}, {ColumnTotal, type number}})



in

CumulTypeNumber

in

fnCumulTable

let fnPct =

(

pSrcData as any,

pFieldValue as text,

pPctField as text,

optional pFieldValue2 as text,

optional pPctField2 as text

) as table =>

let

SrcData=pSrcData,

FieldValue=pFieldValue,

PctField=pPctField,

FieldValue2=pFieldValue2,

PctField2=pPctField2,

SourceData = if SrcData is text then Excel.CurrentWorkbook(){[Name=SrcData]}[Content] else SrcData,



ToStringBuffer_1="List.Buffer(SourceData[" & FieldValue & "])",

TmpBuffer_1=Expression.Evaluate(ToStringBuffer_1, [List.Buffer=List.Buffer,SourceData=SourceData]),

ToStringBuffer_2="List.Buffer(SourceData[" & FieldValue2 & "])",

TmpBuffer_2=if FieldValue2 is null

then null

else Expression.Evaluate(ToStringBuffer_2, [List.Buffer=List.Buffer,SourceData=SourceData]),



SumTmpBuffer_1=List.Sum(TmpBuffer_1),

SumTmpBuffer_2= if FieldValue2 is null

then null

else List.Sum(TmpBuffer_2),

TmpTablePct_1 = Expression.Evaluate("Table.AddColumn(SourceData, PctField, each [" & FieldValue & "]/SumTmpBuffer_1)",

[SourceData=SourceData, Table.AddColumn=Table.AddColumn, PctField=PctField, FieldValue=FieldValue,SumTmpBuffer_1=SumTmpBuffer_1 ]),

TmpTablePct_2 = if FieldValue2 is null

then null

else Expression.Evaluate("Table.AddColumn(TmpTablePct_1, PctField2, each [" & FieldValue2 & "]/SumTmpBuffer_2)",

[SourceData=SourceData, TmpTablePct_1=TmpTablePct_1, Table.AddColumn=Table.AddColumn, PctField2=PctField2, FieldValue2=FieldValue2,SumTmpBuffer_2=SumTmpBuffer_2 ]),

Result= if TmpTablePct_2 is null then TmpTablePct_1 else TmpTablePct_2

in

Result

in

fnPct

let

SelectCase = (

pValue as any,

pSelect as any,

pCase as any,

optional pDefault as any,

optional pSep as any,

optional pType as any

) as any =>



let

Separator=if pSep is null then ";" else pSep,

pList1=if pSelect is list then

if List.IsEmpty(pSelect) then {pValue} else pSelect

else Text.Split(Text.Trim(pSelect),Separator),



pList2=if pCase is list then

if List.IsEmpty(pCase) then {pValue} else pCase

else Text.Split(Text.Trim(pCase),Separator),



Position=List.PositionOf (pList1,pValue),

ReturnValue=if Position=-1 then pDefault else pList2{Position},

EvalReturn=if pType = "any" or pType is null then

ReturnValue

else if pType = "table" then

Expression.Evaluate(ReturnValue,#shared)

else if pType = "list" then

{ReturnValue}

else if pType = "number" then

Number.FromText(ReturnValue)

else if pType = "date" then

Date.FromText(ReturnValue)

else ReturnValue



in

EvalReturn

in

SelectCase

let



fnTotalEachColumnsHV =(pTable as any, pOffset as number, pLibTotalH as text, pLibTotalV as text, optional pBuff as logical) as table =>



let



//-----------------------------------------------------------------------------------

// Fonction permettant de totaliser les colonnes d'un tableau

//-----------------------------------------------------------------------------------



/*

pTable="TB_VENTES",

pOffset=1,

pLibTotalV="Total ventes",

pLibTotalH="Total"

pBuff=true

*/



//-----------------------------------------------------------------------------------

// On importe la source en passant par les buffers pour optimiser les calculs

// Fait-on le choix de bufferiser la table des données

//-----------------------------------------------------------------------------------

Source = if pTable is table then

pTable

else Excel.CurrentWorkbook(){[Name = pTable]}[Content],



// Choix si buffer

bSource=if pBuff is null then

Source

else if pBuff then

Table.Buffer(Source)

else Source ,



// Nombre de colonnes à la source

ColNamesSource = List.Buffer(Table.ColumnNames(bSource)),

FirstN=List.FirstN(ColNames,pOffset),



// On ajoute la colonne total en fin de ligne

TblAddColumnTotal = Table.AddColumn(

bSource,

pLibTotalH,

each List.Sum(

Record.ToList(

Record.SelectFields(_,

List.RemoveItems(Table.ColumnNames(Source), FirstN))))),



//-----------------------------------------------------------------------------------

// On récupère les colonnes de la table des données + la colonne total ajoutée

// Là on peut bufferiser sans risque sur le nbr de colonnes

//-----------------------------------------------------------------------------------

ColNames = List.Buffer(Table.ColumnNames(TblAddColumnTotal)),



//-----------------------------------------------------------------------------------

// On ne retient que les colonnes à cumuler en écartant celles de gauche

// La colonne total ajoutée est conservée

//-----------------------------------------------------------------------------------

TotalsHeaders=List.Buffer(List.LastN(ColNames,List.Count(ColNames) - pOffset)),



//-----------------------------------------------------------------------------------

// On crée une table temporaire qui va contenir le total de chaque colonne

// Et conserve le titre de la 1ère colonne par ColNames{0}

//-----------------------------------------------------------------------------------

TblFirstColTmp = Table.FromColumns({{pLibTotalV}}, {ColNames{0}}),



//-----------------------------------------------------------------------------------

// On fait le cumul de chaque colonne dans la table temporaire

// Boucle pour les cumuls par mois

// Une manière de mettre en oeuvre List.Accumulate

//-----------------------------------------------------------------------------------

CumulColumnsTblTmp = List.Accumulate(

TotalsHeaders, // Liste des colonnes à cumuler

TblFirstColTmp, // Initialisation sur la 1ère colonne de la table temporaire

(state, current) => // On va boucler sur les colonnes à totaliser

// La colonne en cours de traitement est totalisée

// Table.Column(bSource, current) ==> représente la colonne en cours

// traduite sous forme de liste pour faire le total de cette liste

Table.AddColumn(state, current, each List.Sum(Table.Column(TblAddColumnTotal, current)), type number)

),



//-----------------------------------------------------------------------------------

// On fusion la table des cumuls à la table des données pour le tableau final

// Petite astuce pour ajouter un enregistrement à la fin d'un tableau

//-----------------------------------------------------------------------------------

ToTable = Table.Combine({TblAddColumnTotal, CumulColumnsTblTmp})



in

ToTable

in

fnTotalEachColumnsHV

let



fnTotalEachColumnsV =(pTable as any, pOffset as number, pLibTotal as text, optional pBuff as logical) as table =>



let



//-----------------------------------------------------------------------------------

// Fonction permettant de totaliser les colonnes d'un tableau

//-----------------------------------------------------------------------------------



/*

pTable="TB_VENTES",

pOffset=1,

pLibTotal="Total ventes",

pBuff=true

*/



//-----------------------------------------------------------------------------------

// On importe la source en passant par les buffers pour optimiser les calculs

// Fait-on le choix de bufferiser la table des données

//-----------------------------------------------------------------------------------

Source = if pTable is table then

pTable

else Excel.CurrentWorkbook(){[Name = pTable]}[Content],



// Choix si buffer

bSource=if pBuff is null then

Source

else if pBuff then

Table.Buffer(Source)

else Source ,



//-----------------------------------------------------------------------------------

// On récupère les colonnes de la table des données

// Là on peut bufferiser sans risque sur le nbr de colonnes

//-----------------------------------------------------------------------------------

ColNames = List.Buffer(Table.ColumnNames(bSource)),



//-----------------------------------------------------------------------------------

// On ne retient que les colonnes à cumuler en écartant celles de gauche

//-----------------------------------------------------------------------------------

TotalsHeaders=List.Buffer(List.LastN(ColNames,List.Count(ColNames) - pOffset)),



//-----------------------------------------------------------------------------------

// On crée une table temporaire qui va contenir le total de chaque colonne

// Et conserve le titre de la 1ère colonne par ColNames{0}

//-----------------------------------------------------------------------------------

TblFirstColTmp = Table.FromColumns({{pLibTotal}}, {ColNames{0}}),



//-----------------------------------------------------------------------------------

// On fait le cumul de chaque colonne dans la table temporaire

// Boucle pour les cumuls par mois

// Une manière de mettre en oeuvre List.Accumulate

//-----------------------------------------------------------------------------------

CumulColumnsTblTmp = List.Accumulate(

TotalsHeaders, // Liste des colonnes à cumuler

TblFirstColTmp, // Initialisation sur la 1ère colonne de la table temporaire

(state, current) => // On va boucler sur les colonnes à totaliser

// La colonne en cours de traitement est totalisée

// Table.Column(bSource, current) ==> représente la colonne en cours

// traduite sous forme de liste pour faire le total de cette liste

Table.AddColumn(state, current, each List.Sum(Table.Column(bSource, current)), type number)

),



//-----------------------------------------------------------------------------------

// On fusion la table des cumuls à la table des données pour le tableau final

// Petite astuce pour ajouter un enregistrement à la fin d'un tableau

//-----------------------------------------------------------------------------------

ToTable = Table.Combine({bSource, CumulColumnsTblTmp})



in

ToTable

in

fnTotalEachColumnsV

let



fnTotalEachColumnsH =(pTable as any, pOffset as number, pLibTotalH as text, optional pBuff as logical) as table =>



let



//-----------------------------------------------------------------------------------

// Fonction permettant de totaliser les colonnes d'un tableau

//-----------------------------------------------------------------------------------



/*

pTable="TB_VENTES",

pOffset=1,

pLibTotalH="Total"

pBuff=true

*/



//-----------------------------------------------------------------------------------

// On importe la source en passant par les buffers pour optimiser les calculs

// Fait-on le choix de bufferiser la table des données

//-----------------------------------------------------------------------------------

Source = if pTable is table then

pTable

else Excel.CurrentWorkbook(){[Name = pTable]}[Content],



// Choix si buffer

bSource=if pBuff is null then

Source

else if pBuff then

Table.Buffer(Source)

else Source ,



//-----------------------------------------------------------------------------------

// Nombre de colonnes à la source

//-----------------------------------------------------------------------------------

ColNamesSource = List.Buffer(Table.ColumnNames(bSource)),

FirstN=List.FirstN(ColNamesSource,pOffset),



//-----------------------------------------------------------------------------------

// On ajoute la colonne total en fin de ligne

//-----------------------------------------------------------------------------------

TblAddColumnTotal = Table.AddColumn(

bSource,

pLibTotalH,

each List.Sum( // Pour chaque ligne on fait la somme

Record.ToList( // On prend le parti de faire somme de toutes les colonnes

Record.SelectFields(_, // mais on supprime les colonnes de tête

List.RemoveItems(Table.ColumnNames(Source), FirstN)))))



in

TblAddColumnTotal

in

fnTotalEachColumnsH

let fnUnpivotFromTable=(TableName as any, PivotFields as any, AttributeField as any, ValueField as any ) as table =>



let



Source = if TableName is text

then Excel.CurrentWorkbook(){[Name=TableName]}[Content]

else

if TableName is table

then TableName

else "<N/A TABLE>",



PivotFieldsList=if PivotFields is text then

Text.Split(PivotFields, ";")

else PivotFields,



UnpivoTable = Table.UnpivotOtherColumns(Source, PivotFieldsList, AttributeField, ValueField)

in

UnpivoTable



in

fnUnpivotFromTable

let fnPivotFromTable=(pTableName as any, pColPivot as text, pColValue as text) as table =>

let

Source = if pTableName is text

then Excel.CurrentWorkbook(){[Name=pTableName]}[Content]

else

if pTableName is table

then pTableName

else #table({},{}),



ToStrEval="each ([" & pColPivot & "] <> null)",

Eval=Expression.Evaluate(ToStrEval,[Source=Source]),

DelNull = Table.SelectRows(Source, Eval),

ToPivot = if Table.IsEmpty(Source) then

Source

else Table.Pivot(DelNull, List.Distinct(DelNull[CODE_APPLI]), pColPivot, pColValue, List.Sum)

in

ToPivot

in

fnPivotFromTable