Power Query从文件夹取数后图表不更新，替换文件报错求助

核心问题分析 #

报错主要源于以下几点：

• 原查询绑定了旧文件的特定列结构/元数据，新替换文件的列顺序、数据类型存在细微差异，导致Table.TransformColumnTypes步骤执行失败
• 文件名拼写错误：你输入的filetoreplace.xlxs应为filetoreplace.xlsx，后缀错误会导致Power Query无法匹配到有效数据源
• 从文件夹取数时未做精准过滤，可能存在多余文件干扰查询逻辑

分步修复方案 #

1. 重构从文件夹取数的基础查询 #

打开Power Query编辑器，删除原有查询步骤，按以下流程重新操作：

• 点击「数据」>「从文件夹」，选择存放filetoreplace.xlsx的目标文件夹
• 在文件夹内容界面，给名称列添加筛选器，仅保留等于filetoreplace.xlsx的条目
• 选择「合并&加载」>「仅创建连接」，右键该连接选择「编辑查询」

2. 调整数据加载与类型转换逻辑 #

在查询编辑器中，修改加载逻辑以适配文件替换场景，避免硬编码列结构：

1. 展开Content列时，选择「从Excel工作簿导入」，而非直接展开表格（避免绑定旧文件的固定列结构）
2. 替换原有硬编码的类型转换公式，改用动态适配+错误处理的逻辑：

let
    源 = Folder.Files("你的目标文件夹绝对路径"),
    筛选目标文件 = Table.SelectRows(源, each [Name] = "filetoreplace.xlsx"),
    加载Excel内容 = Table.AddColumn(筛选目标文件, "Excel数据", each Excel.Workbook([Content])),
    提取工作表 = Table.AddColumn(加载Excel内容, "工作表列表", each Table.SelectRows([Excel数据], each [Kind] = "Table")),
    展开工作表 = Table.ExpandTableColumn(提取工作表, "工作表列表", {"Name", "Data"}, {"工作表名称", "原始数据"}),
    选择目标工作表 = Table.SelectRows(展开工作表, each [工作表名称] = "你的数据源工作表名"), // 替换为实际工作表名称
    展开数据列 = Table.ExpandTableColumn(选择目标工作表, "原始数据", Table.ColumnNames(Table.First(选择目标工作表[原始数据]))),
    清理错误行 = Table.RemoveRowsWithErrors(展开数据列),
    适配类型转换 = Table.TransformColumnTypes(清理错误行, {
        {"Response ID", Int64.Type},
        {"Date submitted", type datetime},
        {"Last page", Int64.Type},
        {"Start language", type text},
        {"Seed", Int64.Type},
        {"Access code", type text},
        {"Date started", type datetime},
        {"Date last action", type datetime},
        {"1. What best describes your facility/hospital?", type text},
        {"1. What best describes your facility/hospital? [Other]", type text},
        {"2. Is your facility/hospital public or private?", type text},
        {"3. Is your facility/hospital a university teaching facility/hospital?", type text},
        {"4. Does your facility/hospital have a formal research programme for cancer?", type text},
        {"5. Does your facility/hospital have ongoing collaboration(s) for research in cancer care?", type text},
        {"5.1 Please list the names of your national university/educational or research partners [University / Partner 1]", type text},
        {"5.1 Please list the names of your national university/educational or research partners [University / Partner 2]", type text},
        {"5.1 Please list the names of your national university/educational or research partners [University / Partner 3]", type text},
        {"6. Does your facility/hospital have a partnership with any other local/national health facilities?", type text},
        {"6.1 Please list the names of the local/national health facilities that your facility/hospital has partnerships with", type text},
        {"7. Does your facility/hospital have a partnership with international organisations on cancer? (e.g. counterpart for international research, technical cooperation project, receiving teaching projects)", type text},
        {"8. Do you have a functional Ethics Committee for cancer care at your facility/hospital? ", type text},
        {"8.1 How often does the Ethics Committee meet?", type text}
    })
in
    适配类型转换

3. 替换文件后的注意事项 #

• 新替换的文件必须保证列名与原文件完全一致，若有列新增/删除，需同步调整上述公式中的类型转换列表
• 严格使用.xlsx后缀，避免拼写错误
• 设置自动刷新：点击「数据」>「刷新全部」，可右键查询选择「属性」，开启「打开文件时刷新」或设置定时刷新

内容的提问来源于stack exchange，提问作者user16239103