POWERQUERY :: Combiner un dossier Excel avec table de renommage des colonnes

[oguruma]

Suite du post Combiner des TS
Les sources restent les mêmes

Rappel des paramètres

Le plus : renommage des colonnes à l'issue de l'importation. Une autre manière par table de transposition (mapping)

Table de mapping : TB_CombineMappingColumns

et les paramètres nécessaires

//ListRenames
let
    pTable=fnGetParameter("TB_PARAMS","TABLE_RENAME"),
    pFrom=fnGetParameter("TB_PARAMS","COL_FROM"),
    pTo=fnGetParameter("TB_PARAMS","COL_TO"),
    List = fnGetListRenames (pTable,pFrom,pTo)
in
    List
 
//TB_Mapping_Columns
let
    pFilter=fnGetParameter("TB_PARAMS","FILTRE"),
    ToList = if pFilter is null then fnMappingColumns () else fnMappingColumns ("Ventes")
in
    ToList

// TB_COMBINE_RENAME_1
let
    pFilter=fnGetParameter("TB_PARAMS","FILTRE"),
    Source = if pFilter is null then fnCombineRename() else fnCombineRename("Ventes")
in
    Source

let
    fnGetListRenames = (pTblName as text, pFromColumns as text, pToColumns as text) as list =>
    let
        Source = Excel.CurrentWorkbook(){[Name=pTblName]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{pFromColumns, type text}, {pToColumns, type text}}),
        Transpose = Table.ToColumns(Table.Transpose(ChangedType))
    in
        Transpose
in
    fnGetListRenames
 
let
    fnMappingColumns = (optional pFilter as text) as list =>
    let
        pFolder=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),
        Source = Folder.Files(pFolder),
        Filter = if pFilter is null then Source else Table.SelectRows(Source, each Text.StartsWith([Name], pFilter)),
        AddCustom = Table.AddColumn(Filter, "Custom", each Excel.Workbook([Content], true)),
        Expand = Table.ExpandTableColumn(AddCustom, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
        ToList = Table.ColumnNames(Table.Combine(Expand[Data]))
    in
        ToList
in
    fnMappingColumns
 
let
    fnCombineRename = (optional pFilter as text) as table =>
    let
        pFolder=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),
        Source = Folder.Files(pFolder),
        Filter01 = if pFilter is null then Source else Table.SelectRows(Source, each Text.StartsWith([Name], "Ventes")),
        AddCustomFieldsContent = Table.AddColumn(Filter01, "CustomFieldsContent", each Excel.Workbook([Content], true)),
        ExpandCustomFieldsContent = Table.ExpandTableColumn(AddCustomFieldsContent, "CustomFieldsContent", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
        Filter02 = Table.SelectRows(ExpandCustomFieldsContent, each [Kind] = "Sheet"),
        AddCustomField = Table.AddColumn(Filter02, "CustomFieldsContent", each Table.RenameColumns([Data], ListRenames, MissingField.Ignore)),
        ToTable = Table.Combine(AddCustomField[CustomFieldsContent])
    in
        ToTable
in
    fnCombineRename