read xlsx by power query-CSDN博客

本文链接：https://blog.csdn.net/nowcici/article/details/131874192

该脚本读取指定文件夹中的非隐藏.xlsx文件，合并所有工作表的数据，并根据需要删除头部行。如果提供了列名，则选择这些列；否则，自动检测列名。最后，根据参考值决定使用哪种结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(path as text, colnames as list, n_delrows as number, ref as number)=>let
    resource = Folder.Files(path),
    seldata = Table.SelectRows(resource, each ([Attributes][Hidden] = false and [Extension] = ".xlsx")),
    a = 
    let
        readxlsx = Table.SelectColumns(
            Table.TransformColumns(seldata, {"Content", each 
                Table.SelectRows(Excel.Workbook(_), each [Kind]="Sheet")
            }),
        {"Name", "Content"}),
        content = Table.SelectRows(
            Table.ExpandTableColumn(readxlsx, "Content", {"Name", "Data"}, {"SheetName", "Data"}),
        each Table.IsEmpty([Data]) = false),
        selcontent = Table.TransformColumns(content, {"Data", each 
            Table.PromoteHeaders(
                Table.RemoveFirstN(_, n_delrows)
            )
        }),
        namelist = if List.IsEmpty(colnames) = true 
            then List.Distinct(
                List.Combine(
                    List.Transform(selcontent[Data], each Table.ColumnNames(_))
                )
            )
            else colnames,
        expdata = Table.ExpandTableColumn(selcontent, "Data", namelist, namelist)
    in
        expdata,
    b =
    let
        readxlsx = List.Select(
            List.Combine(
                List.Transform(seldata[Content], each 
                    Table.SelectRows(Excel.Workbook(_), each [Kind]="Sheet")[Data]
                )
            ), (x)=> Table.IsEmpty(x)=false
        ),
        selcols = List.Transform(readxlsx, each
            if List.IsEmpty(colnames) = true
            then Table.PromoteHeaders(
                Table.RemoveFirstN(_, n_delrows)
            )
            else
                try Table.SelectColumns(
                    Table.PromoteHeaders(
                        Table.RemoveFirstN(_, n_delrows)
                    ),
                colnames)
                otherwise Table.PromoteHeaders(
                    Table.RemoveFirstN(_, n_delrows)
                )
        ),
        comfiles = Table.Combine(selcols)
    in 
        comfiles,
    result = if ref = 0 then b else a
in
    result