PowerQuery 多个文件并添加列 [英] PowerQuery multiple files and add column

问题描述

我有内联 PowerQuery 来自动化我的每周报告.由于我是电源查询的新手，因此我跟进了这个教程 并尝试添加一个自定义列，以便我可以使用它来查看每周的改进，问题是添加的列未命名为Week"，而是称为文件名.从这个 网页 第二个参数是列名.我不明白为什么列名是文件名而不是名称周".

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

推荐答案

此调用:

FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)

将用第一行的值替换您拥有的列名.由于Week"列下的第一个值是文件名，那么您的表现在将使用该文件名作为列名.

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.

您可以通过在使用PromoteHeaders 后添加自定义列来解决此问题:

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

这篇关于PowerQuery 多个文件并添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！