从 Web 检索数据时处理 Excel Power Query 中的错误

从 Web 检索数据时处理 Excel Power Query 中的错误
从 Web 检索数据时处理 Excel Power Query 中的错误
Alice Dupont
2024年7月18日星期四 下午2:29:58

管理 Excel Power Query 中的数据检索错误

使用 Excel Power Query 从公司内部 URL 获取数据时，经常会遇到不同的响应代码。通常，这些响应代码指示数据检索是否成功（200）或未找到（404）。确保正确处理这些响应代码对于在 Excel 中准确表示数据至关重要。

本文将探讨如何使用 Power Query 函数从内部 URL 获取和显示数据。重点是管理数据检索响应代码为 404 的场景，防止错误并确保数据处理顺利。我们将逐步完成必要的步骤并提供有效处理这些错误的解决方案。

命令 描述
Json.Document 解析从 Web 服务检索的 JSON 数据。
Web.Contents 从指定的 URL 获取数据。
try ... otherwise 尝试执行操作并在发生错误时提供替代结果。
Record.ToTable 将记录转换为表格格式。
Table.SelectRows 根据指定条件过滤表。
Table.Pivot 根据不同的值将行转换为列。

了解 Power Query 中的错误处理

在提供的脚本中，我们首先使用 Web.Contents 函数从指定的 URL 获取数据，该 URL 是使用动态构建的 id 范围。该数据的解析使用 Json.Document，将 JSON 响应转换为 Power Query 可以处理的格式。响应包含一个 Instrument 记录，我们使用索引访问它（Instrument{0}）。从这个记录中，我们提取 Data_Flow 检查 Data_Response_Code，表示数据检索的成功或失败。

如果 Data_Response_Code 是 200，我们继续提取所需的数据字段 - Instrument_Full_Name 和 CFI_Code - 来自 Instrument_Common 记录。然后使用这些字段将其转换为表格格式 Table.Pivot。如果响应代码为 404，表示未找到数据，我们通过显式设置输出字段来确保它们为空。这种方法通过使用 try...otherwise 构造，它捕获潜在的问题并默认为安全状态。

Power Query M语言脚本详细分解

第二个脚本在第一个脚本的基础上进行了扩展，合并了 try...otherwise 构造，为数据检索期间遇到的任何错误提供后备机制。解析 JSON 响应后 Json.Document 并访问 Instrument 记录，我们尝试检索 Data_Response_Code。如果此操作失败，脚本默认为 404，确保进程的其余部分继续进行而不会中断。

一旦响应代码被确认，脚本要么从 Instrument_Common 或者如果响应代码为 404，则将它们设置为空白。该函数 FetchData 然后用于将这些结果添加到现有表中的新列中，利用 19 号。此方法允许进行稳健的错误处理，并确保维护数据完整性，即使某些数据点丢失或 Web 请求失败也是如此。总的来说，这些脚本演示了在 Power Query 中处理 Web 数据检索错误的有效技术。

处理 Power Query 中的数据检索错误

使用 Power Query M 语言

(id as text)=>
let
    Source = Json.Document(Web.Contents("https://example.com/data?Identifier=" & id)),
    Instrument = Source[Instrument]{0},
    DataFlow = Instrument[Data_Flow],
    ResponseCode = DataFlow[Data_Response_Code],
    Output = if ResponseCode = 200 then
        let
            InstrumentCommon = Instrument[Instrument_Common],
            FullName = InstrumentCommon[Instrument_Full_Name],
            CFI = InstrumentCommon[CFI_Code]
        in
            [FullName = FullName, CFI_Code = CFI]
    else
        [FullName = "", CFI_Code = ""]
in
    Output

使用 Power Query 确保数据完整性

使用 Excel Power Query M 语言

let
    FetchData = (id as text) =>
    let
        Source = Json.Document(Web.Contents("https://example.com/data?Identifier=" & id)),
        Instrument = Source[Instrument]{0}?
        ResponseCode = try Instrument[Data_Flow][Data_Response_Code] otherwise 404,
        Output = if ResponseCode = 200 then
            let
                InstrumentCommon = Instrument[Instrument_Common],
                FullName = InstrumentCommon[Instrument_Full_Name],
                CFI = InstrumentCommon[CFI_Code]
            in
                [FullName = FullName, CFI_Code = CFI]
        else
            [FullName = "", CFI_Code = ""]
    in
        Output,
    Result = Table.AddColumn(YourTableName, "FetchData", each FetchData([Id]))
in
    Result

了解 Power Query 命令

处理 Power Query 中的数据检索错误

使用 Power Query M 语言

(id as text)=>
let
    Source = Json.Document(Web.Contents("https://example.com/data?Identifier=" & id)),
    Instrument = Source[Instrument]{0},
    DataFlow = Instrument[Data_Flow],
    ResponseCode = DataFlow[Data_Response_Code],
    Output = if ResponseCode = 200 then
        let
            InstrumentCommon = Instrument[Instrument_Common],
            FullName = InstrumentCommon[Instrument_Full_Name],
            CFI = InstrumentCommon[CFI_Code]
        in
            [FullName = FullName, CFI_Code = CFI]
    else
        [FullName = "", CFI_Code = ""]
in
    Output

使用 Power Query 确保数据完整性

使用 Excel Power Query M 语言

let
    FetchData = (id as text) =>
    let
        Source = Json.Document(Web.Contents("https://example.com/data?Identifier=" & id)),
        Instrument = Source[Instrument]{0}?
        ResponseCode = try Instrument[Data_Flow][Data_Response_Code] otherwise 404,
        Output = if ResponseCode = 200 then
            let
                InstrumentCommon = Instrument[Instrument_Common],
                FullName = InstrumentCommon[Instrument_Full_Name],
                CFI = InstrumentCommon[CFI_Code]
            in
                [FullName = FullName, CFI_Code = CFI]
        else
            [FullName = "", CFI_Code = ""]
    in
        Output,
    Result = Table.AddColumn(YourTableName, "FetchData", each FetchData([Id]))
in
    Result

Power Query 中错误处理的高级技术

Power Query 中处理错误的一方面是能够妥善管理预期数据丢失或服务器响应不符合预期的情况。当处理来自可能出现间歇性问题的网络来源的大型数据集时，这尤其有用。利用 try...otherwise 构造不仅确保查询不会失败，而且还提供记录这些错误以供进一步分析的机会。可以通过创建捕获错误消息的单独列来记录错误，从而允许用户有效地识别和解决根本原因。

Power Query 的另一个强大功能是能够组合多个查询和数据源。通过创建合并来自各个端点的结果的主查询，用户可以简化其数据处理工作流程。在处理需要分页或多个标识符来获取完整数据集的 API 时，此方法特别有用。在 Power Query 中实现循环结构可以自动执行这些任务，减少人工干预并提高数据准确性。这不仅提高了生产力，还确保了更稳健的数据集成过程。

Power Query 错误处理的常见问题和解决方案

  1. 是什么 try...otherwise 在 Power Query 中构造？
  2. 这 try...otherwise 构造用于通过尝试操作并在操作失败时提供替代结果来优雅地处理错误。
  3. 如何在 Power Query 中记录错误？
  4. 可以通过创建一个单独的列来记录错误，该列使用以下命令捕获错误消息 try...otherwise 构造，以便更轻松地识别和故障排除。
  5. 目的是什么 Web.Contents 功能？
  6. 这 Web.Contents 函数用于从 Power Query 中的指定 URL 获取数据。
  7. 如何处理 Power Query 中丢失的数据？
  8. 当数据不可用时，可以通过检查响应代码并设置默认值（例如空字符串）来处理丢失的数据，使用 if...then...else 构造。
  9. 什么是 Json.Document 用于？
  10. 这 Json.Document 函数用于解析从 Web 服务检索的 JSON 数据。
  11. Power Query 可以处理多个数据源吗？
  12. 是的，Power Query 可以通过创建主查询来合并多个数据源，该主查询整合来自各个端点的结果，从而提高数据集成效率。
  13. 如何在 Power Query 中自动获取数据？
  14. 通过实现处理多个标识符或分页数据的循环结构，可以自动化数据获取，从而减少手动干预。
  15. 什么是 Table.Pivot 用于？
  16. 这 Table.Pivot 函数用于根据不同的值将行转换为列，从而有助于数据组织。
  17. 使用 Power Query 时如何确保数据完整性？
  18. 可以通过验证响应代码并适当处理错误来确保数据完整性，从而确保仅处理准确且完整的数据。

包起来：

从 Web 检索数据时有效处理 Excel Power Query 中的错误对于确保数据完整性和避免数据处理中断至关重要。通过使用适当的命令和结构（例如 try...otherwise 和 Json.Document），您可以优雅地管理数据丢失或响应不符合预期的场景。这种方法不仅有助于保持准确性，还增强了 Excel 中数据工作流的稳健性。