PowerQuery multiple files and add column

问题:

I have the inline PowerQuery to automate my weekly reporting. Since I am new to power query I followed up this tutorial and try to add a custom column so I can use it to see week over week improvements, the thing is that the column that is added is not named "Week" but instead it is called the name of the file. From this webpage the second parameter is column name. I do not find why column name is filename instead of the name "week".

let ExcelFile = (FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
    ImportedExcel = Excel.Workbook(File),
    Sheet1 = ImportedExcel{[Name="Page1_1"]}[Data],
    TableWithWeek = Table.AddColumn(Sheet1,"Week", each FileName),
    TableWithoutHeader = Table.Skip(TableWithWeek,3),
    FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)
in
    FirstRowAsHeader
in
    ExcelFile

回答1:

This call:

FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)

will replace the column names you have with the values from the first row. Since the first value under the column "Week" is the filename, then your table will now use that filename as the column name.

You can fix this by adding the custom column after you use PromoteHeaders:

let ExcelFile = (FilePath, FileName) =>
    let
        Source = Folder.Files(FilePath),
        File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
        ImportedExcel = Excel.Workbook(File),
        Sheet1 = ImportedExcel{[Name="Page1_1"]}[Data],
        TableWithoutHeader = Table.Skip(Sheet1, 3),
        FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader),
        TableWithWeek = Table.AddColumn(FirstRowAsHeader,"Week", each FileName),
    in
        TableWithWeek
in
    ExcelFile