Power Query Adding Columns and Multiple files

Question

So, I'm using PowerQuery to automate weekly reporting. I'm attempting to add a Custom Column, through this tutorial video that I'm following, so that I could use it to see weekly progress. The issue I'm facing is that the column that is added is not named "Week" but instead the name of the file. 

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

I'm curious so as to why is it so. Could anyone tell me?

Answer 1

The following call,

FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)

shall 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

All the Best (: