Звісно, це чудово, коли ви нарешті налаштували джерела даних і сформували дані відповідно до свого смаку. Сподіваємося, що після оновлення даних із зовнішнього джерела все пройде без проблем. Але це не завжди так. Зміни в потоці даних можуть спричинити проблеми, які виражаються помилками під час спроби оновлення даних. Деякі помилки легко виправити, інші – тимчасові, а деякі – важко діагностувати. Нижче наведено набір стратегій, які ви можете застосувати, щоб впоратися з помилками, які трапляються на вашому шляху.
Два типи помилок
Під час оновлення даних можуть виникнути два типи помилок.
Локальна база даних. Якщо в книзі Excel сталася помилка, це означає, що ваші зусилля з виправлення неполадок обмежені та керовані. Можливо, оновлення даних спричинило помилку функції або створення неприпустимої умови в розкривному списку. Ці помилки набридливі, але їх досить легко відстежити, визначити та виправити. Крім того, в Excel покращено обробку помилок завдяки чіткішим повідомленням і контекстним посиланням на цільові розділи довідки, які допоможуть з'ясувати й вирішити проблему.
"Віддалений доступ" Однак зовсім інша справа – помилка з віддаленого зовнішнього джерела даних. У системі через дорогу, на півдорозі або в хмарі, виникла проблема. Для таких помилок потрібен інший підхід. До поширених помилок віддаленого доступу належать:
- Не вдалося підключитися до служби або ресурсу. Перевірте підключення.
- Не вдалося знайти файл, до якого ви намагаєтеся отримати доступ.
- Сервер не відповідає і, можливо, перебуває на технічному обслуговуванні.
- Цей вміст недоступний. Можливо, його видалено або він тимчасово недоступний.
- Будь ласка, зачекайте... Триває завантаження даних.
Дослідження помилок
Нижче наведено кілька порад, які допоможуть вам усунути помилки, які можуть виникнути.
Пошук і збереження певної помилки Спочатку перевірте область "Запити & зв'язки" (виберіть "Запити даних>"& "Зв'язки", виберіть підключення, а потім відкрийте спливаюче вікно). Переглядайте помилки доступу до даних і занотовуйте будь-які додаткові надані відомості. Потім відкрийте запит, щоб переглянути конкретні помилки на кожному кроці. Усі помилки відображаються на жовтому тлі для їх легкого виявлення. Запишіть або зробіть знімок екрана з відомостями про повідомлення про помилку, навіть якщо ви не до кінця їх розумієте. Колега, адміністратор або служба підтримки у вашій організації можуть допомогти вам зрозуміти, що сталося, і запропонувати рішення. Докладні відомості див. в статті "Робота з помилками в Power Query".
Отримання довідкової інформації Скористайтеся пошуком на сайті довідки й навчання Office . Він містить не лише докладну довідку, але й відомості для виправлення неполадок. Докладні відомості див. в статті "Виправлення або способи вирішення нещодавніх проблем в Excel для Windows".
Використання можливостей технічної спільноти На сайтах спільноти Microsoft можна шукати обговорення, що стосуються саме вашої проблеми. Цілком імовірно, що ви не перший, хто зіткнувся з проблемою, інші мають справу з нею і, можливо, навіть знайшли рішення. Докладні відомості див. на сторінках спільнот Microsoft Excel і Office Answers.
Пошук в Інтернеті Скористайтеся основною пошуковою системою, щоб знайти додаткові сайти в Інтернеті, які можуть запропонувати доречні обговорення або підказки. Це може зайняти багато часу, але це спосіб закинути ширшу мережу, щоб шукати відповіді на особливо гострі запитання.
Звернення до служби підтримки Office На цьому етапі ви, ймовірно, розумієте проблему набагато краще. Це допоможе вам зосередитися на розмові та мінімізувати час, проведений у службі підтримки Microsoft. Докладні відомості див. в розділі "Підтримка клієнтів Microsoft 365 і Office".
Докладні відомості про помилки, пов'язані із джерелом даних
Хоча вам, можливо, не вдасться вирішити проблему, ви можете точно з'ясувати, у чому проблема, щоб допомогти іншим зрозуміти ситуацію та вирішити її за вас.
Проблеми зі службами та серверами Ймовірним винуватцем цього є періодичні помилки в мережі та зв'язку. Найкраще, що ви можете зробити, це зачекати та спробувати ще раз. Іноді проблема просто зникає.
Зміни розташування або доступності Базу даних або файл переміщено, пошкоджено, переведено в автономний режим для обслуговування, або сталася аварійне завершення роботи. Дискові пристрої можуть бути пошкоджені, і файли можуть бути втрачені. Докладні відомості див. в статті "Відновлення втрачених файлів на Windows 10".
Зміни в автентифікації та конфіденційності Може статися так, що дозвіл більше не працює або параметри конфіденційності буде змінено. Обидві події можуть перешкоджати доступу до зовнішнього джерела даних. Щоб дізнатися, що змінилося, зверніться до адміністратора або адміністратора зовнішнього джерела даних. Докладні відомості див. в статтях "Керування настройками та дозволами джерела даних " і "Установлення рівнів конфіденційності".
Відкриті або заблоковані файли Якщо відкрито текст, CSV-файл або книгу, зміни, внесені до файлу, не вносяться до оновлення, доки файл не буде збережено. Крім того, якщо файл відкрито, його може бути заблоковано, і доступ до нього буде недоступний, доки його не буде закрито. Це може статися, якщо інший користувач використовує версію Excel без передплати. Попросіть її закрити файл або повернути його з редагування. Докладні відомості див. в статті "Розблокування файлу, який було заблоковано для редагування".
Зміни схем на сервері Хтось змінює ім'я таблиці, стовпця або тип даних. Це майже ніколи не буває розумно, може мати величезний вплив і особливо небезпечно з базами даних. Можна сподіватися, що команда управління базами даних встановила належні засоби контролю, щоб запобігти цьому, але помилки трапляються.
Блокуючи помилки у згортанні запитів Power Query ви намагаєтеся за можливості підвищити продуктивність. Часто краще виконувати запит бази даних на сервері, щоб скористатися перевагами продуктивності та ємності. Цей процес називається згортанням запитів. Однак Power QueryНадбудова Power Query блокує запит, якщо існує ймовірність порушення безпеки даних. Наприклад, об'єднання визначається між таблицею книги та таблицею SQL Server. Для даних книги встановлено значення "Конфіденційність", а для даних SQL Server – "Організація". Оскільки параметри конфіденційності мають більші обмеження, ніж корпоративні, Power Query блокує обмін інформацією між джерелами даних. Згортання запитів відбувається у фоновому режимі, тому повідомлення про помилку блокування може здивувати вас. Докладні відомості див. в статтях "Основи згортання запитів", "Згортання запитів" і " Згортання за допомогою діагностики запитів".
Помилки Power Query
Часто за допомогою Power Query можна точно з'ясувати, в чому проблема, і усунути її самостійно.
Перейменування таблиць і стовпців Зміни вихідних імен таблиць і стовпців або заголовків стовпців майже напевно призведуть до проблем під час оновлення даних. Запити використовують імена таблиць і стовпців, щоб формувати дані майже на кожному кроці. Не змінюйте або не видаляйте вихідні імена таблиці та стовпців, крім випадків, коли ви прагнете забезпечити відповідність джерелу даних.
Зміни в типах даних Змінення типу даних іноді може призвести до помилок або неочікуваних результатів, особливо у функціях, які можуть потребувати певного типу даних для аргументів. Наприклад, заміна текстового типу даних у числовій функції або спроба виконати обчислення з нечисловим типом даних. Докладні відомості див. в статті "Додавання або змінення типів даних".
Помилки на рівні клітинки Помилки цих типів не завадять завантажити запит, але відображають помилку в клітинці. Щоб переглянути повідомлення, виділіть пробіл у клітинці таблиці, яка містить слово Error. Помилки можна видалити, замінити або просто залишити. Приклади помилок у клітинках:
- Перетворення Ви намагаєтеся перетворити клітинку, яка містить NA, на ціле число.
- Математичні розрахунки Ви намагаєтеся помножити текстове значення на числове.
- Оператори злиття Ви намагаєтеся поєднати рядки, але один із них числовий.
Безпечні експерименти та повтори Якщо ви не впевнені, що перетворення може мати негативні наслідки, скопіюйте запит, перевірте внесені зміни та повторіть варіанти команди Power Query. Якщо команда не працює, просто видаліть створений крок і повторіть спробу. Щоб швидко створити зразок даних з однаковою схемою та структурою, створіть таблицю Excel із кількох стовпців і рядків, а потім імпортуйте її (виберіть дані>з таблиці або діапазону). Докладні відомості див. в статтях "Створення таблиці та імпорт із таблиці Excel".
Трансформуйте з розумом
Можливо, ви почуватиметеся дитиною в кондитерській, коли вперше зрозумієте, що можна робити з даними в Редактор Power Query. Але не піддавайтеся спокусі з'їсти всі цукерки. Потрібно уникнути перетворень, які можуть ненавмисно спричинити помилки оновлення. Деякі операції прості (наприклад, переміщення стовпців до іншого розташування в таблиці) і не повинні призводити до помилок оновлення, оскільки Power Query відстежує стовпці за їхніми іменами.
Інші дії можуть призвести до помилок оновлення. Одним із загальних емпіричних правил може бути ваш дороговказ. Уникайте внесення значних змін до вихідних стовпців. Щоб перестрахуватися, скопіюйте вихідний стовпець за допомогою команди (додати стовпець, спеціальний стовпець, дублювати стовпець тощо), а потім внесіть потрібні зміни до скопійованої версії вихідного стовпця. Нижче наведено операції, які іноді можуть призводити до помилок оновлення, а також деякі практичні поради, які допоможуть уникнути зайвих проблем.
| Operation | Інструкція |
|---|---|
| Фільтрування | Підвищуйте ефективність, фільтруючи дані якомога раніше в запиті, і видаляйте непотрібні дані, щоб зменшити непотрібну обробку даних. Крім того, використовуйте автофільтр для пошуку або вибору певних значень. Крім того, використовуйте фільтри для типу, доступні в стовпцях дат, дати та часового поясу (наприклад, " Місяць", "Тиждень", " День"). |
| Типи даних і заголовки стовпців | Power QueryНадбудова Power Query автоматично додає два кроки до запиту відразу після першого кроку "Джерело": "Підвищені заголовки", який робить перший рядок таблиці заголовком стовпця, і "Змінений тип", який перетворює значення з будь-якого типу даних на тип даних на дані на основі перевірки значень у кожному стовпці. Це корисна зручність, але іноді може знадобитися явно контролювати цю поведінку, щоб уникнути випадкових помилок оновлення. Докладні відомості див. в статтях "Додавання та змінення типів даних " і "Підвищення та зниження рівня рядків і заголовків стовпців". |
| Перейменування стовпця | Не перейменовуйте вихідні стовпці. Використовуйте команду " Перейменувати " для стовпців, доданих іншими командами або діями. Докладні відомості див. в статті "Перейменування стовпця". |
| Розділити стовпець | Розділення копій вихідного стовпця (а не вихідного). Докладні відомості див. в статті "Розділення текстового стовпця". |
| Об'єднати стовпці | Об'єднувати копії вихідних стовпців, а не вихідні стовпці. Докладні відомості див. в статті "Об'єднання стовпців". |
| Видалення стовпця | Якщо потрібно зберегти невелику кількість стовпців, скористайтеся функцією "Вибрати стовпець ", щоб зберегти потрібні стовпці. Розглянемо різницю між видаленням одного та видалення інших стовпців. Якщо видалити інші стовпці й оновити дані, нові стовпці, додані до джерела даних після останнього оновлення, можуть залишитися непоміченими, тому що після повторного виконання кроку "Видалити стовпець" у запиті вони вважатимуться іншими стовпцями. Ця ситуація не виникне, якщо явно видалити стовпець. Порада Команди для приховання стовпця (на відміну від програми Excel) немає. Однак, якщо у вас багато стовпців і потрібно приховати багато з них, щоб сконцентрувати роботу, можна виконати такі дії: видалити стовпці, запам'ятати крок, який було створено, а потім видалити цей крок, перш ніж завантажувати запит назад до аркуша. Докладні відомості див. в статті "Видалення стовпців". |
| Замінення значення | Заміна значення не змінює джерело даних. Натомість потрібно змінити значення в запиті. Під час наступного оновлення даних шукане значення може дещо змінитися або зникнути, і тому команда "Замінити " може не працювати належним чином. Докладні відомості див . в статті "Замінення значень". |
| Зведення та скасування зведення | Якщо використовується команда зведення стовпця , може статися помилка (не агреговані значення), але буде повернуто більше одного значення. Така ситуація може виникнути після операції оновлення, під час якої дані змінюються непередбачуваним чином. Команду " Скасувати зведення на інших стовпцях " слід використовувати, якщо відомі не всі стовпці, а ви хочете, щоб нові стовпці, додані під час операції оновлення, також було скасовано. Команду "Скасувати зведення лише вибраний стовпець" використовуйте тоді, коли невідома кількість стовпців у джерелі даних і потрібно переконатися, що вибрані стовпці залишаються незведеними після операції оновлення. Докладні відомості див. в статтях "Зведення стовпців " і " Скасування зведення". |
Крок на крок попереду
Запобігання виникненню помилок Якщо зовнішнім джерелом даних керує інша група у вашій організації, вона має усвідомлювати вашу залежність від них і уникати змін у своїх системах, які можуть спричинити проблеми в майбутньому. Записуйте вплив на дані, звіти, діаграми та інші артефакти, які залежать від даних. Налаштуйте лінії зв'язку, щоб переконатися, що вони розуміють вплив і вживають необхідних заходів для забезпечення безперебійної роботи. Дізнайтеся, як створювати елементи керування, які зводять до мінімуму непотрібні зміни та передбачають їх наслідки. Треба визнати, що це легко сказати, а іноді й важко зробити.
Перспективність із параметрами запиту Використовуйте параметри запиту, щоб будь-коли змінювати дані, наприклад у розташуванні даних. Ви можете спроектувати параметр запиту, який замінятиме нове розташування, наприклад шлях до папки, ім'я файлу або URL-адресу. Існують додаткові способи використання параметрів запиту для зменшення кількості проблем. Докладні відомості див. в статті "Створення параметризованого запиту".
Додаткові відомості
Power QueryДовідка з надбудови Power Query для програми Excel
Практичні поради щодо роботи зі службою Power Query (docs.com)