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

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}))

)

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

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

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

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

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

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

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

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

let

Source = fnGetEXCEL (

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

"VENTES_AA.xlsx",

null,

"Sheet"



)

in

Source

let

Source = fnGetEXCEL (

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

"VENTES_A.xlsx",

"V-A",

"Sheet"



)

in

Source

let

Source = fnGetEXCEL (

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

"STRAT_GAMMA.xlsx",

"Technique",

"Sheet",

3

)

in

Source

let

Source = fnGetEXCEL (

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

"STRAT_GAMMA.xlsx",

"TB_COMPOSANTS",

"Table"

)

in

Source

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

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

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