Обработка ошибок в Excel Power Query при получении данных из Интернета

Обработка ошибок в Excel Power Query при получении данных из Интернета
Обработка ошибок в Excel Power Query при получении данных из Интернета
Alice Dupont
четверг, 18 июля 2024 г., 15:39:26

Управление ошибками получения данных в Excel Power Query

При работе с Excel Power Query для получения данных с внутренних URL-адресов компании часто встречаются разные коды ответа. Обычно эти коды ответа указывают, был ли поиск данных успешным (200) или не найден (404). Обеспечение правильной обработки этих кодов ответа важно для точного представления данных в Excel.

В этой статье мы рассмотрим, как использовать функцию Power Query для получения и отображения данных с внутреннего URL-адреса. Основное внимание будет уделено управлению сценариями, в которых код ответа на получение данных равен 404, предотвращению ошибок и обеспечению бесперебойной обработки данных. Мы рассмотрим необходимые шаги и предоставим решения для эффективной обработки этих ошибок.

Команда Описание
Json.Document Анализирует данные JSON, полученные из веб-службы.
Web.Contents Извлекает данные с указанного URL-адреса.
try ... otherwise Пытается выполнить операцию и предоставляет альтернативный результат в случае возникновения ошибки.
Record.ToTable Преобразует запись в формат таблицы.
Table.SelectRows Фильтрует таблицу на основе указанного условия.
Table.Pivot Преобразует строки в столбцы на основе различных значений.

Понимание обработки ошибок в Power Query

В предоставленных сценариях мы начинаем с использования Web.Contents функция для получения данных с указанного 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 затем используется для добавления этих результатов в новый столбец существующей таблицы, используя Table.AddColumn. Этот метод обеспечивает надежную обработку ошибок и гарантирует сохранение целостности данных, даже если некоторые точки данных отсутствуют или веб-запрос не выполнен. В целом эти сценарии демонстрируют эффективные методы обработки ошибок получения веб-данных в 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

Использование языка 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 Функция используется для получения данных по указанному URL-адресу в Power Query.
  7. Как я могу обработать недостающие данные в Power Query?
  8. Отсутствующие данные можно обработать, проверив код ответа и установив значения по умолчанию (например, пустые строки), когда данные недоступны, используя метод if...then...else построить.
  9. Что Json.Document используется для?
  10. Json.Document Функция используется для анализа данных JSON, полученных из веб-службы.
  11. Может ли Power Query обрабатывать несколько источников данных?
  12. Да, Power Query может объединять несколько источников данных, создавая главный запрос, который объединяет результаты из различных конечных точек, повышая эффективность интеграции данных.
  13. Как я могу автоматизировать получение данных в Power Query?
  14. Извлечение данных можно автоматизировать, реализовав структуру цикла, которая обрабатывает несколько идентификаторов или данные с разбивкой на страницы, что сокращает необходимость ручного вмешательства.
  15. Что Table.Pivot используется для?
  16. Table.Pivot Функция используется для преобразования строк в столбцы на основе различных значений, что помогает в организации данных.
  17. Как обеспечить целостность данных при использовании Power Query?
  18. Целостность данных можно обеспечить путем проверки кодов ответов и соответствующей обработки ошибок, гарантируя обработку только точных и полных данных.

Подведение итогов:

Эффективная обработка ошибок в Excel Power Query при получении данных из Интернета имеет решающее значение для обеспечения целостности данных и предотвращения сбоев в их обработке. Используя соответствующие команды и конструкции, такие как try...иначе и Json.Document, вы можете корректно управлять сценариями, в которых данные отсутствуют или ответы не соответствуют ожиданиям. Этот подход не только помогает поддерживать точность, но и повышает надежность рабочих процессов с данными в Excel.