📖 目录

• 一、业务场景与痛点
• 二、技术选型与方案设计
• 三、核心代码实现详解
• 四、代码框架

---

一、业务场景与痛点

典型场景

业务场景：每月产生大量结构相同的Excel报表，分散存储在不同月份的文件夹中：

\\nas\虚拟\202501\202501人员费用表.xlsx
\\nas\虚拟\202502\202502人员费用表.xlsx
\\nas\虚拟\202503\202503人员费用表.xlsx
...

---

二、技术选型与方案设计

为什么选择Power Query？

• 原生集成：Power BI内置，无需额外工具
• 自动化能力强：一次设置，永久使用
• 内存管理优秀：大数据量处理稳定
• 刷新机制完善：支持定时/手动刷新

方案设计思路

文件筛选 → 批量读取 → 数据清洗 → 合并输出
     ↓          ↓          ↓          ↓
精确匹配   异常处理   结构统一   最终整理

---

三、核心代码实现详解

3.1 文件路径智能识别

// 关键技巧：从完整路径提取年月信息
addFolderName = Table.AddColumn(filteredFiles, "folderName", each 
    let
        pathParts = Text.Split([Folder Path], "\"),
        // 路径结构示例：\\nas\虚拟\202501\202501人员费用表.xlsx
        // pathParts[0]: "" | [1]: "" | [2]: "nas" | [3]: "虚拟" | [4]: "202501"
        folderName = if List.Count(pathParts) >= 2 then 
            pathParts{List.Count(pathParts)-2}  // 获取"202501"
        else null
    in folderName
)

技术要点：

• Text.Split：将路径分割为数组
• List.Count(pathParts)-2：动态获取父文件夹名
• 兼容性设计：支持任意层级的路径结构

3.2 文件名双重验证机制

// 验证1：文件名以"20"开头（年份文件）
filteredFiles = Table.SelectRows(excelFiles, each 
    Text.EndsWith([Name], "人员费用表.xlsx")
    and Text.StartsWith([Name], "20")
),

// 验证2：文件夹名也以"20"开头
filter20Folders = Table.SelectRows(addFolderName, each 
    Text.StartsWith([folderName], "20")
)

为什么需要双重验证？

• 防止误读：test202501人员费用表.xlsx
• 提高准确性：确保文件在正确的年月文件夹中
• 未来扩展：方便调整年份范围（如改为"21"开头）

3.3 安全的批量文件读取

processFiles = Table.AddColumn(renameColumns, "ProcessedData", each 
    try 
        let
            source = Excel.Workbook([Content], null, true),
            sheetData = try 
                source{[Item="Sheet1", Kind="Sheet"]}[Data] 
            otherwise null,
            
            processed = if sheetData <> null then
                // 数据清洗流程...
            else null
        in processed
    otherwise null  // 捕获所有异常
)

三层保护机制：

1. 外层try：捕获文件无法读取的异常
2. 内层try：捕获工作表不存在的异常
3. 空值检查：确保数据有效才继续处理

3.4 高效的数据清洗流程

// 1. 提升表头（自动识别列名）
promotedHeaders = Table.PromoteHeaders(sheetData, [PromoteAllScalars=true]),

// 2. 强制类型转换（保证计算正确）
typedColumns = Table.TransformColumnTypes(promotedHeaders, {
    {"月份", type date}, 
    {"模式", type text}, 
    {"合柜名称", type text}, 
    {"合柜编码", type text}, 
    {"单柜编码", type text}, 
    {"固定", type number}, 
    {"变动", type number}
}),

// 3. 数据质量过滤
filteredData = Table.SelectRows(typedColumns, each 
    [单柜编码] <> null and [单柜编码] <> ""
)

清洗逻辑说明：

• PromoteAllScalars=true：自动处理多层表头
• 明确的类型转换：避免后续计算错误
• 必要的空值过滤：提高数据质量

3.5 内存优化技巧

// 关键：及时释放大对象
removeContent = Table.RemoveColumns(removeErrors, {"Content"})

// 直接展开处理后的数据
expandData = Table.ExpandTableColumn(removeContent, "ProcessedData", 
    {"月份", "取数报表", "财务模式", "合柜名称", "合柜编码", "单柜编码", "固定", "变动"}
)

优化原则：

• 二进制数据（Content）用后立即删除
• 避免中间表过多占用内存
• 一次性展开，减少迭代次数

---

四、代码框架

let
    // ========================
    // 1. 基础配置
    // ========================
    folderPath = "填写你的文件夹路径",  // 例如：\\nas\虚拟
 
    // ========================
    // 2. 文件筛选与识别
    // ========================
    allFiles = Folder.Files(folderPath),
    
    excelFiles = Table.SelectRows(allFiles, each 
        Text.EndsWith([Name], ".xlsx", Comparer.OrdinalIgnoreCase)
    ),
    
    filteredFiles = Table.SelectRows(excelFiles, each 
        Text.EndsWith([Name], "人员费用表.xlsx")
        and Text.StartsWith([Name], "20")
    ),
    
    // 3. 提取文件夹信息
    addFolderName = Table.AddColumn(filteredFiles, "folderName", each 
        let
            pathParts = Text.Split([Folder Path], "\"),
            folderName = if List.Count(pathParts) >= 2 then 
                pathParts{List.Count(pathParts)-2} 
            else null
        in folderName
    ),
    
    filter20Folders = Table.SelectRows(addFolderName, each 
        Text.StartsWith([folderName], "20")
    ),
    
    renameColumns = Table.RenameColumns(filter20Folders, 
        {{"Name", "FileName"}, {"folderName", "Name"}}
    ),
    
    // 4. 批量处理文件
    processFiles = Table.AddColumn(renameColumns, "ProcessedData", each 
        try 
            let
                source = Excel.Workbook([Content], null, true),
                sheetData = try source{[Item="Sheet1", Kind="Sheet"]}[Data] otherwise null,
                
                processed = if sheetData <> null then
                    let
                        promotedHeaders = Table.PromoteHeaders(sheetData, [PromoteAllScalars=true]),
                        
                        typedColumns = Table.TransformColumnTypes(promotedHeaders, {
                            {"月份", type date}, 
                            {"模式", type text}, 
                            {"合柜名称", type text}, 
                            {"合柜编码", type text}, 
                            {"单柜编码", type text}, 
                            {"固定", type number}, 
                            {"变动", type number}
                        }),
                        
                        filteredData = Table.SelectRows(typedColumns, each 
                            [#"单柜编码"] <> null and [#"单柜编码"] <> ""
                        ),
                        
                        addedYearMonth = Table.AddColumn(filteredData, "年月", each 
                            Text.From(Date.Year([月份])) & Text.PadStart(Text.From(Date.Month([月份])), 2, "0")
                        )
                    in
                        addedYearMonth
                else null
            in
                processed
        otherwise null
    ),
    
    // 5. 过滤并合并数据
    removeErrors = Table.SelectRows(processFiles, each 
        [ProcessedData] <> null and [ProcessedData] <> ""
    ),
    
    removeContent = Table.RemoveColumns(removeErrors, {"Content"}),
    
    // 简化的展开方式
    expandData = Table.ExpandTableColumn(removeContent, "ProcessedData", 
        {"月份", "模式", "合柜名称", "合柜编码", "单柜编码", "固定", "变动", "年月"}
    ),
    
    // 6. 最终类型转换
    changedTypes = Table.TransformColumnTypes(expandData, {
        {"月份", type date}, 
        {"模式", type text}, 
        {"合柜名称", type text}, 
        {"合柜编码", type text}, 
        {"单柜编码", type text}, 
        {"固定", type number}, 
        {"变动", type number},
        {"年月", type text}
    }),
    
    // 7. 添加索引（可选）
    已添加索引 = Table.AddIndexColumn(changedTypes, "索引", 0, 1, Int64.Type)
    
in
    已添加索引

转载请注明出处，欢迎交流讨论