Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

oguruma

XLDnaute Occasionnel
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





PARAMETREVALEUR
DOSSIER_CSVD:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION
DELIMITEUR_CSV;
ENCODE_CSV65001
NBCOLS_CSV4
FICHIER_CSV_1TECHNIC_ARPEGE.csv
FICHIER_CSV_2TECHNIC_BLIND.csv
LISTE_FICHIERS_CSVTECHNIC_SOLO.csv;TECHNIC_IRON.csv;TECHNIC_GAMMA.csv
FILTRE_CSVTECHNIC
EXTENSION.csv


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

TBL_TYPES_CSV


COLONNETYPE
PHASE_CERTIFICATIONtype text
CODE_APPLItype text
INSTANCES_SERVEURtype number
PUISSANCE_MACHINEtype number
TBL_RENOM_CSV
ANCIENNOUVEAUTBL_RENOM_CSV2
PHASE_CERTIFICATIONPhaseANCIENNOUVEAU
CODE_APPLIApplicationPHASE_CERTIFICATIONPhase
INSTANCES_SERVEURInstanceCODE_APPLIApplication
PUISSANCE_MACHINEPuissanceTOT_INSTANCESTotal instances


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

RQ_COMBINE_CSV_BY_STEP
PHASE_CERTIFICATIONCODE_APPLIINSTANCES_SERVEURPUISSANCE_MACHINE
CD0BC
10​
1969​
CD0AB
7​
3056​
CD0SK
5​
4991​
CD1WA
7​
2374​
CD1OR
6​
2445​
CD1ID
6​
1812​


PowerQuery:
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

PowerQuery:
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)


PowerQuery:
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)
PowerQuery:
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")


PowerQuery:
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")
PowerQuery:
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



PowerQuery:
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

PowerQuery:
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

PowerQuery:
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



PARAMETREVALEUR
DOSSIER_EXCELD:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION
FILTRE_SOUS_DOSSIER_EXCELD:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021\
FILTRE_FICHIERS
FILTRE_EXTENSION.xlsx
FILTRE_TABLE_EXCELTBP_
COLONNES_TECHNIQUESOUI
FICHIER_EXCEL_1STRAT_GAMME.xlsx
FICHIER_EXCEL_2STRAT_GAMMA.xlsx
LISTE_FICHIERS_EXCELSTRAT_GAMME.xlsx;STRAT_GAMMA.xlsx
COLONNES_TRIDossierExcel
OFFSET_CUMUL13
TITRE_COLONNE
TITRE_LIGNE
DOSSIER_FICHIERD:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021
PHASE_CERTIFICATIONNOM_CERTIFNOM_DU_CPPLATEAU_TEST
CD0Rodage TechniqueCP_NOM1Plateau1
CD1Rodage réseauCP_NOM2Plateau2
CD2Rodage disqueCP_NOM3Plateau3
CD3Rodage fluxCP_NOM4Plateau4
CD4Rodage AlphaCP_NOM5Plateau5
CD5Rodage GoldenCP_NOM6Plateau6


CODE_APPLILIB_APPLIMOE
BCComptabilitéNOM_MOE1
ABPaieNOM_MOE2
SKCommercialNOM_MOE3
WAMarketingNOM_MOE4
ORFrais générauxNOM_MOE5
IDAbonnementsNOM_MOE6
MOAgences commercialesNOM_MOE7
CAStratégieNOM_MOE8


TB_RENOM_CERT_DYNAM
ANCIENNOUVEAU
PHASE_CERTIFICATIONPhase
CODE_APPLICode application
COMMENTCommentaires
NB_TRAITEMENENTSNbr de traitements
DEVELOPPEMENTEnv. Développement
INTEGRATIONEnv. Intégration
QUALIFICATIONEnv. Qualification
HOMOL1Env. Homol Niv. 1
HOMOL2Env. Homol Niv. 2
RECETTEEnv. Recette métiers


TB_RENAME_TB_COMPOTB_TYPE_TB_COMPO
ANCIENNOUVEAUANCIENNOUVEAU
PHASE_CERTIFICATIONPhase de certif.PHASE_CERTIFICATIONtype text
CODE_APPLIApplicationCODE_APPLItype text
VOLUMETRIE_GOVolumétrie en GoVOLUMETRIE_GOtype number


TB_TYPE_CERT_DYNAM
COLONNETYPE
PHASE_CERTIFICATIONtype text
CODE_APPLItype text
COMMENTtype text
NB_TRAITEMENENTStype number
DEVELOPPEMENTtype number
INTEGRATIONtype number
QUALIFICATIONtype number
HOMOL1type number
HOMOL2type number
RECETTEtype number


ANCIENNOUVEAUTYPE
PHASE_CERTIFICATIONPhase de CDtype text
CODE_APPLICode applicationtype text
VOLUMETRIE_GOVolumétrie en Gotype number


TB_MAPPING_RENAME_TYPE2
ANCIENNOUVEAUTYPE
PHASE_CERTIFICATIONPhasetype text
CODE_APPLICode applicationtype text
COMMENTCommentairestype text
NB_TRAITEMENENTSNbr de traitementstype number
DEVELOPPEMENTEnv. Développementtype number
INTEGRATIONEnv. Intégrationtype number
QUALIFICATIONEnv. Qualificationtype number
HOMOL1Env. Homol Niv. 1type number
HOMOL2Env. Homol Niv. 2type number
RECETTEEnv. Recette métierstype number


Importer un fichier EXCEL

PowerQuery:
let

Source = fnGetEXCEL (

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

"VENTES_AA.xlsx",

null,

"Sheet"



)

in

Source



Importer un fichier Excel


PowerQuery:
let

Source = fnGetEXCEL (

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

"VENTES_A.xlsx",

"V-A",

"Sheet"



)

in

Source



Importer un fichier Excel

PowerQuery:
let

Source = fnGetEXCEL (

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

"STRAT_GAMMA.xlsx",

"Technique",

"Sheet",

3

)

in

Source



Importer un fichier Excel


PowerQuery:
let

Source = fnGetEXCEL (

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

"STRAT_GAMMA.xlsx",

"TB_COMPOSANTS",

"Table"

)

in

Source



Importer un fichier Excel

PowerQuery:
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

PowerQuery:
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



PowerQuery:
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)





PowerQuery:
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







PowerQuery:
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



PowerQuery:
let

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

in

Source



PowerQuery:
let



Source = fnCombineMultiTablesEXCEL_H_Sheet(

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

null,

"PROJ",

"Technique"

)

in

Source







PowerQuery:
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



PowerQuery:
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







PowerQuery:
let

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

in

Source



PowerQuery:
let

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

in

Source



PowerQuery:
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



PowerQuery:
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





PowerQuery:
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



PowerQuery:
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



PowerQuery:
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



PowerQuery:
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







PowerQuery:
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



PowerQuery:
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



PowerQuery:
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



PowerQuery:
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



PowerQuery:
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







PowerQuery:
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



PowerQuery:
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



PowerQuery:
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



PowerQuery:
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





PowerQuery:
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
 

Pièces jointes

  • CERTIFICATION.zip
    351.5 KB · Affichages: 2
  • $__ComBineSynthese.zip
    179.7 KB · Affichages: 2
Dernière édition:

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…