使用 Power Query 的 Table.Schema 检查列结构 —— 在导入前检测必填列的缺失

关于本文
本文是通过利用生成式 AI 的自动化工作流创建的。我们查阅了 Microsoft Learn 中有关 Table.Schema、Table.ColumnNames、Table.HasColumns 和 Table.FromRows 的规范，并整理了使用虚拟表提取列信息的 Power Query M 示例。

验证状态：📘 已确认微软官方规范，未在 Power Query 实机上验证
以下表格和结果是从官方规范中导出的预期值。尚未在 Excel 或 Power BI 中进行执行验证。

当使用 Power Query 导入每月收到的 CSV 或各部门制作的 Excel 表格时，棘手的问题往往不仅是“数据为空”。上个月存在的列消失了、列名被更改了、金额列被识别为字符串等表格结构的变化也会导致处理失败。

Power Query M 中有一个用于检查表格结构的Table.Schema函数。它不仅可以显示列名，还可以显示列的位置、关于数据类型的信息以及是否允许 null 值等。本文将通过 3 行的虚拟表来观察结构，并介绍在列缺失时停止处理的方法，以及用布尔值判断列是否存在的方法。

Table.Schema 返回什么

基本语法如下。

Table.Schema(table as table) as table

此函数不返回原始数据的每一行，而是返回一个将原表的列逐行进行说明的另一个表。例如，如果原表有“订单ID”、“部门”、“金额”这 3 列，则模式侧原则上会排列对应这 3 列的信息。

此外还包含精度和最大长度等信息，但某些项可能无法根据数据源获取。不能仅凭元数据为空白就断定数据已损坏。

官方规范：Microsoft Learn — Table.Schema

从 Excel 开始尝试的方法

在 Excel 的 Power Query 编辑器中创建一个“空查询”，并将以下 M 代码粘贴到高级编辑器中。既不需要加载外部文件，也不需要连接数据库。

let
    Source = Table.FromRows(
        {
            {1001, "Sales", 1250.5},
            {1002, "Support", 900.0},
            {1003, "Sales", 0.0}
        },
        type table [
            OrderId = Int64.Type,
            Department = text,
            Amount = number
        ]
    ),
    Schema = Table.Schema(Source),
    ImportantFields = Table.SelectColumns(
        Schema,
        {"Name", "Position", "TypeName", "Kind", "IsNullable"}
    )
in
    ImportantFields

这里 Table.FromRows 是由行数据列表创建的表。第二个参数不仅指定了列名，还赋予了名为 type table [...] 的表类型。因此，在示例中我们声明了“OrderId 为整数、Department 为文本、Amount 为数值”的类型。

Table.Schema(Source) 如果只有它，就会输出大量可获取的架构列。为了便于阅读，我们这次使用 Table.SelectColumns 仅保留了 5 个项目。

显示信息的查看方法

预期的原始列顺序如下：

实际的 TypeName 或 Kind 的表现形式取决于 Power Query 引擎和数据源的类型表示。上面的“类型含义”是对阅读方式的说明，并未逐字复现类型名称栏的实际输出。

重要的是 Position 从 0 开始 这一点。请注意不要按照数 Excel 列号的习惯从 1 开始考虑。

修改一处并进行观察

首先，试着将最后的 Amount = number 修改为如下所示。

type table [
    OrderId = Int64.Type,
    Department = text,
    Amount = text
]

如果直接这样运行，即使原始示例中包含数值，也会在架构中确认将表类型声明更改为 text 的影响。这是一个思考“实际数据值的类型检查”与“查看列声明的类型信息”之间差异的实验。

注意: 不能仅仅因为更改了类型声明，就断定所有现有值都已经按照预期安全地转换了。必须另外检查值是否为预期的格式、是否存在转换错误。

当列消失时，停止后续处理

假设月度 CSV 中每次必需的列是 OrderId 和 Amount。如果责任部门发来的数据中没有 Amount，则不应该忽视这一点并继续进行汇总。

以下示例将比较必需的列与实际存在的列。请作为与前一个查询不同的空白查询来运行。

let
    Source = Table.FromRows(
        {
            {1001, "Sales"},
            {1002, "Support"}
        },
        type table [OrderId = Int64.Type, Department = text]
    ),
    Required = {"OrderId", "Amount"},
    Existing = Table.ColumnNames(Source),
    Missing = List.Difference(Required, Existing),
    Checked =
        if List.IsEmpty(Missing)
        then Source
        else error (
            "Required column(s) missing: "
            & Text.Combine(Missing, ", ")
        )
in
    Checked

这里实际的列是 {"OrderId", "Department"}，而必需的列是 {"OrderId", "Amount"}。

List.Difference(Required, Existing) 用于仅提取必需但不存在的列。由于其结果是 {"Amount"}，因此根据条件，处理预计会产生以下错误。

Required column(s) missing: Amount

诸如“如果没有金额列，则仅对剩余列进行汇总”之类的防止无声的数据聚合错误目的。

然而，这种方法无法消除数据源本身的故障或身份验证失败。如果在获取列信息之前连接失败，则需要单独调查该连接错误。

仅获取布尔值时使用 Table.HasColumns

如果您不需要列的详细信息，而只想判断“所需的列是否存在”，则有一个更简短的函数。

Table.HasColumns(table as table, columns as any) as logical

当指定的列全部存在时，此函数返回 true；只要缺少一个，就会返回 false。

let
    Source = #table(
        type table [OrderId = Int64.Type, Amount = number],
        {{1001, 12.5}, {1002, 18.0}}
    ),
    HasRequired = Table.HasColumns(Source, {"OrderId", "Amount"}),
    HasOptional = Table.HasColumns(Source, {"OrderId", "Owner"})
in
    #table(

        type table [Check = text, Exists = logical],
        {
            {"OrderId + Amount", HasRequired},
            {"OrderId + Owner", HasOptional}
        }
    )

期望的结果如下。

官方文档：Microsoft Learn — Table.HasColumns

如何区分使用这三个函数？

Table.Schema 并不总是最优解。如果只需要确认有无，用 Table.HasColumns 就足够了。另一方面，如果要调查 CSV 或数据库的规格变更，则 Table.Schema 非常有效。

应用于实际的 CSV 导入时

在实际工作中，按照以下顺序进行检查可以更容易地排查原因。

1. 能否连接到数据源

2. 是否所有必填列名都存在

3. 各列设置的类型是否符合预期

4. 必要的类型转换是否会发生错误

5. 必要的键列中是否存在 null 或重复值

6. 条数或金额的汇总结果是否合理

Table.Schema 是第三步结构确认的依据，它并不是一个能自动证明第 4 到 6 步的函数。

例如 Amount即便被声明为数值类型，如果外部 CSV 中混入了“未知”、“1,200日元”等字符串，也可能会在某个类型转换环节中失败。请不要仅凭类型信息就断定“所有金额都是正确的”。

此外，如果 CSV 的列名因前后空格、全角与半角、外观相似的字符而发生变化，单纯的列名一致性判断将无法通过。我们首先需要确定什么样的列名才算是正式的，并规范数据交接的规则。

纳入每月处理流程时的判断

好的用法是，在每月导入文件的初始阶段检查是否存在必需的列，不符合条件的数据不予加载，并让负责人能够确认输入规范上的差异。

应避免的用法是，无论什么都用 try ... otherwise 将其掩盖，即使缺少列也让处理在空白状态下继续。除非业务上明确规定“可以没有列”，否则掩盖异常会让人很难察觉汇总错误。

以“即使列顺序改变也能通过列名进行选择”的实现方式为基础，可以减少位置变更带来的影响。不过，也请确认连接器的具体行为，例如对同名列的处理等。

放置在 GitHub 上的示例

• Power Query M 示例：列信息显示、缺失列检查、列存在性判定

check-schema.pq 中，汇总了本文中使用的 3 个独立查询。请将各个查询分别粘贴到高级编辑器中。 这并不是一个用于将 3 个 let ... in 直接连在一起粘贴的脚本。

在实际设备上进行测试时，如果记录下 Excel 或 Power BI 的版本、所显示的 TypeName 以及错误内容，便能形成具有可复现性的验证记录。

参考的 Microsoft 官方信息

• Table.Schema

• Table.ColumnNames

• Table.HasColumns

• Table.FromRows

• List.Difference