Обработка ошибок источника данных (Power Query)

Существует два типа ошибок, которые могут возникнуть при обновлении данных.

Местный   Если в книге Excel возникает ошибка, то, по крайней мере, ваши усилия по устранению неполадок ограничены и более управляемы. Возможно, обновленные данные вызвали ошибку с функцией или данные создали недопустимое условие в раскрывающемся списке. Эти ошибки являются беспокойными, но довольно легко отслеживать, идентифицировать и исправлять. В Excel также улучшена обработка ошибок с более четкими сообщениями и контекстно-чувствительными ссылками на целевые разделы справки, которые помогут вам выяснить и устранить проблему.

Удалённый   Однако ошибка, возникающая из удаленного внешнего источника данных, — это совсем другое дело. Что-то произошло в системе, которая может находиться через дорогу, на полпути по всему миру или в облаке. Эти типы ошибок требуют другого подхода. Распространенные удаленные ошибки:

• Не удалось подключиться к службе или ресурсу. Проверьте соединение.

• Не удалось найти файл, к который вы пытаетесь получить доступ.

• Этот сервер не отвечает, возможно на нем проводится обслуживание. 

• Это содержимое недоступно. Возможно, он был удален или временно недоступен.

• Подождите... данные загружаются.

Ниже приведено несколько рекомендаций, которые помогут вам справиться с ошибками, которые могут возникнуть.

Поиск и сохранение конкретной ошибки    Сначала изучите панель Запросы & Connections (выберите & Connections> запросы, выберите подключение, а затем отобразите всплывающее меню). Узнайте, какие ошибки доступа к данным произошли, и запишите все предоставленные дополнительные сведения. Затем откройте запрос, чтобы увидеть конкретные ошибки на каждом шаге запроса. Все ошибки отображаются на желтом фоне для удобства идентификации. Запишите или запишите сведения об ошибке, даже если вы не понимаете их полностью. Коллега, администратор или служба поддержки в вашей организации могут помочь вам понять, что произошло, и предложить решение. Дополнительные сведения см. в статье Работа с ошибками в Power Query.

Вызов справки    Выполните поиск по сайту справки и обучения Office. Здесь содержатся не только подробные справочные материалы, но и сведения об устранении неполадок. Дополнительные сведения см. в статье Исправления или обходные пути для последних проблем в Excel для Windows.

Использование технического сообщества    Используйте сайты сообщества Майкрософт для поиска обсуждений, относящихся конкретно к вашей проблеме. Скорее всего, вы не первый человек, который испытывает проблему, другие имеют дело с ней, и, возможно, даже нашли решение. Дополнительные сведения см. в разделе Сообщество Microsoft Excelи Сообщество ответов Office.

Поиск в Интернете    Используйте предпочитаемую поисковую систему для поиска дополнительных сайтов в Интернете, которые могут предоставить соответствующие обсуждения или подсказки. Это может занять много времени, но это способ лить более широкую сеть, чтобы искать ответы на особенно тернистые вопросы.

Обращение в службу поддержки Office      На этом этапе вы, вероятно, понимаете проблему гораздо лучше. Это поможет сосредоточиться на беседе и свести к минимуму время, затраченное на служба поддержки Майкрософт. Дополнительные сведения см. в статье Microsoft 365 и Служба поддержки клиентов Office.

Хотя вы, возможно, не сможете устранить проблему, вы можете точно узнать, в чем заключается проблема, чтобы помочь другим понять ситуацию и решить ее за вас.

Проблемы со службами и серверами    Вероятным виновником являются периодические ошибки сети и связи. Лучшее, что вы можете сделать, это подождите и повторите попытку. Иногда проблема просто уходит.

Изменения расположения или доступности    База данных или файл были перемещены, повреждены, переведены в автономный режим для обслуживания или база данных аварийно завершает работу. Дисковые устройства могут повредиться, а файлы будут потеряны. Дополнительные сведения см. в статье Восстановление потерянных файлов на Windows 10.

Изменения в проверке подлинности и конфиденциальности   Внезапно может случиться, что разрешение больше не работает, или в параметр конфиденциальности было внесено изменение. Оба события могут препятствовать доступу к внешнему источнику данных. Обратитесь к администратору или администратору внешнего источника данных, чтобы узнать, что изменилось. Дополнительные сведения см. в разделах Управление параметрами и разрешениями источника данных иНастройка уровней конфиденциальности.

Открытые или заблокированные файлы   Если текст, CSV или книга открыты, все изменения файла не включаются в обновление до тех пор, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован и не может быть доступен, пока не будет закрыт. Это может произойти, когда другой пользователь использует версию Excel, не связанную с подпиской. Попросите их закрыть файл или проверка его. Дополнительные сведения см. в разделе Разблокировка файла, заблокированного для редактирования.

Изменения в схемах на серверной части   Кто-то изменяет имя таблицы, имя столбца или тип данных. Это почти никогда не бывает мудрым, может иметь огромное влияние, и особенно опасно для баз данных. Можно надеяться, что команда по управлению базами данных установила надлежащие средства контроля, чтобы предотвратить это, но скольжения все же происходят. 

Блокировка ошибок при свертке запросов    Power Query пытается повысить производительность всякий раз, когда это возможно. Часто лучше выполнить запрос к базе данных на сервере, чтобы воспользоваться преимуществами большей производительности и емкости. Этот процесс называется свертыванием запросов. Однако Power Query блокирует запрос, если есть вероятность компрометации данных. Например, слияние определяется между таблицей книги и SQL Server таблицей. Для конфиденциальности данных книги задано значение Конфиденциальность, а для SQL Server данных — значение Организационный. Так как конфиденциальность является более строгой, чем организационная, Power Query блокирует обмен информацией между источниками данных. Свертывание запросов происходит в фоновом режиме, поэтому может удивить вас при возникновении ошибки блокировки. Дополнительные сведения см. в разделах Основы свертывания запросов, Свертывание запросов и Свертывание с помощью диагностики запросов.

Часто с Power Query, вы можете точно узнать, в чем проблема, и устранить ее самостоятельно.

Переименованные таблицы и столбцы    Изменения исходных имен таблиц и столбцов или заголовков столбцов почти наверняка вызовет проблемы при обновлении данных. Запросы используют имена таблиц и столбцов для формирования данных практически на каждом шаге. Избегайте изменения или удаления исходных имен таблиц и столбцов, если только вы не хотите, чтобы они соответствовали источнику данных. 

Изменения типов данных    Изменения типов данных иногда могут вызывать ошибки или непредвиденные результаты, особенно в функциях, для которых в аргументах может потребоваться определенный тип данных. Например, замена текстового типа данных в числовой функции или попытка выполнить вычисление для нечислового типа данных. Дополнительные сведения см. в статье Добавление или изменение типов данных.

Ошибки на уровне ячеек   Эти типы ошибок не препятствуют загрузке запроса, но они отображают ошибку в ячейке. Чтобы просмотреть сообщение, выберите пробелы в ячейке таблицы, содержащей ошибку. Вы можете удалить, заменить или просто сохранить ошибки. Примеры ошибок ячейки: 

• Превращение    Вы пытаетесь преобразовать ячейку, содержащую na, в целое число.

• Математический    Вы пытаетесь умножить текстовое значение на числовое значение.

• Конкатенация    Вы пытаетесь объединить строки, но одна из них является числовой.

Безопасные эксперименты и итерации    Если вы не уверены, что преобразование может оказать негативное влияние, скопируйте запрос, протестируйте изменения и выполните итерацию по вариантам команды Power Query. Если команда не работает, просто удалите созданный шаг и повторите попытку. Чтобы быстро создать образцы данных с той же схемой и структурой, создайте таблицу Excel из нескольких столбцов и строк, а затем импортируйте ее (выберите данные > из таблицы или диапазона). Дополнительные сведения см. в разделах Создание таблицы и Импорт из таблицы Excel.

Вы можете почувствовать себя ребенком в магазине конфет, когда вы впервые понимаете, что вы можете сделать с данными в Редактор Power Query. Но не поддавайтесь искушению съесть все конфеты. Вы хотите избежать преобразований, которые могут непреднамеренно вызвать ошибки обновления. Некоторые операции просты, например перемещение столбцов в другую позицию в таблице, и не должны приводить к ошибкам обновления в будущем, так как Power Query отслеживает столбцы по имени столбца.

Другие операции могут привести к ошибкам обновления. Одно общее правило большого пальца может быть вашим путеводным светом. Избегайте внесения значительных изменений в исходные столбцы. Чтобы обеспечить безопасность, скопируйте исходный столбец с помощью команды (Add a Column, Custom Column, Duplicate Column и т. д.), а затем внесите изменения в скопированную версию исходного столбца. Ниже приведены операции, которые иногда могут привести к ошибкам обновления, и некоторые рекомендации, которые помогут сделать все более гладко.

Операция	Рекомендация
Фильтрация	Повысьте эффективность путем фильтрации данных как можно раньше в запросе и удаления ненужных данных, чтобы уменьшить ненужную обработку. Кроме того, используйте автофильтр для поиска или выбора определенных значений и использования фильтров для конкретных типов, доступных в столбцах date, datetime и timezone (например, Month, Week, Day).
Типы данных и заголовки столбцов	Power Query автоматически добавляет два шага в запрос сразу после первого шага источника: повышенное повышение заголовков, которое делает первую строку таблицы заголовком столбца, и измененный тип, который преобразует значения из типа данных Any в тип данных на основе проверки значений из каждого столбца. Это удобно, но иногда требуется явно управлять этим поведением, чтобы предотвратить непреднамеренное обновление. Дополнительные сведения см. в разделах Добавление или изменение типов данных и Повышение или понижение значений строк и заголовков столбцов.
Переименование столбца	Избегайте переименования исходных столбцов. Используйте команду Переименовать для столбцов, добавляемых другими командами или действиями. Дополнительные сведения см. в разделе Переименование столбца.
Разделить столбец	Разделение копий исходного столбца, а не исходного столбца. Дополнительные сведения см. в разделе Разделение столбца текста.
Объединение столбцов	Слияние копий исходных столбцов, а не исходных столбцов. Дополнительные сведения см. в статье Объединение столбцов.
Удаление столбца	Если необходимо сохранить небольшое количество столбцов, используйте команду Выбрать столбец , чтобы сохранить нужные столбцы.  Рассмотрим разницу между удалением столбца и удалением других столбцов. При удалении других столбцов и обновлении данных новые столбцы, добавленные в источник данных с момента последнего обновления, могут остаться незамеченными, так как они будут считаться другими столбцами при повторном выполнении шага Удалить столбец в запросе. Эта ситуация не возникнет, если вы явно удалите столбец. Совет      Команда для скрытия столбца (как в Excel) отсутствует. Однако если у вас много столбцов и вы хотите скрыть многие из них, чтобы помочь сосредоточиться на работе, можно сделать следующее: удалить столбцы, запомнить созданный шаг, а затем удалить этот шаг, прежде чем загружать запрос обратно на лист. Дополнительные сведения см. в разделе Удаление столбцов.
Замена значения	При замене значения источник данных не редактируются. Вместо этого вы вносите изменения в значения в запросе. При следующем обновлении данных значение, которое вы искали, может немного измениться или больше не существует, поэтому команда Заменить может работать не так, как предполагалось изначально. Дополнительные сведения см. в разделе Замена значений.
Pivot и Unpivot	При использовании команды Pivot Column может возникнуть ошибка при сворачении столбца, не агрегированном значении, но возвращается несколько значений. Такая ситуация может возникнуть после операции обновления, которая изменяет данные непредвиденным образом. Используйте команду Unpivot Other Columns , если известны не все столбцы, и вы хотите, чтобы новые столбцы, добавленные во время операции обновления, также были отменены. Если вы не знаете количество столбцов в источнике данных и хотите убедиться, что выбранные столбцы остаются без сворачиваемых столбцов после операции обновления, используйте команду Unpivoted Selected Column. Дополнительные сведения см. в разделах Столбцы сводных данных и Отмена сводных столбцов.

Предотвращение возникновения ошибок    Если внешним источником данных управляет другая группа в вашей организации, она должна знать о вашей зависимости от них и избегать изменений в своих системах, которые могут вызвать проблемы ниже. Запись влияния на данные, отчеты, диаграммы и другие артефакты, которые зависят от данных. Настройте линии связи, чтобы убедиться, что они понимают влияние и принимают необходимые меры, чтобы обеспечить бесперебойную работу. Найдите способы создания элементов управления, которые сводят к минимуму ненужные изменения и предвидят последствия необходимых изменений. По общему признанию, это легко сказать, а иногда и трудно сделать.

Проверка на будущее с параметрами запроса    Используйте параметры запроса для устранения изменений, например в расположении данных. Можно создать параметр запроса, чтобы заменить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют дополнительные способы использования параметров запроса для устранения проблем. Дополнительные сведения см. в разделе Создание запроса параметров.