нельзя обновить запрос доступный только для подключения power query
Глава 16. Автоматизация обновления решений в Power Query
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
По мере того, как вы создаете все больше и больше решений на основе Power Query, и начинаете понимать, сколько времени это экономит, вы захотите еще большей автоматизации. Да, вы можете просто щелкнуть правой кнопкой мыши таблицу, которая была импортирована с помощью Power Query, но даже это ощущается как… ручная операции. Нельзя ли запланировать обновление или, возможно, контролировать порядок обновления?
На самом деле вы можете использовать несколько различных методов автоматизации обновления решений Power Query:
Рис. 16.1. Настройка параметров подключения
Скачать заметку в формате Word или pdf, примеры в формате архива
Планирование обновлений без использования кода
Первые два метода работают через пользовательский интерфейс и не требуют кода VBA. Они могут быть настроены на основе подключения, и вы даже можете автоматизировать обновление вплоть до Power Pivot. Откройте файл Automating Refresh.xlsx. Перейдите на вкладку Данные –> Запросы и подключения. В правой части окна Excel откроется область Запросы и подключения. Кликните правой кнопкой мыши имя запроса –> Свойства. Откроется окно Свойства запроса (рис. 1).
Обратите внимание, что флажок Фоновое обновление установлен по умолчанию. Этот параметр позволяет указать, следует ли продолжать работать в Excel во время обновления данных. Если вы снимите этот флажок, вы можете потенциально уменьшить время, необходимое для обновления вашего решения, но вы также заблокируете пользовательский интерфейс, что означает, что вы не сможете делать другие вещи, пока обновление запроса не будет завершено. Желательно оставить этот параметр включенным.
Следующий параметр – Обновлять каждые ХХ минут. Этот параметр очень пригодится, если вы извлекаете данные из веб-источника, который постоянно меняется, или если вы ориентируетесь на базу данных, которая регулярно обновляется. Для того, чтобы это обновление произошло книга Excel должна быть открыта. Допустимые значения для этого параметра – от 1 до 32 767.
Обновлять при открытии книги. Второй параметр в этом разделе – Удалять данные из внешнего диапазона перед сохранением книги. Установите этот флажок, если вы хотите сохранить книгу с определением запроса, но без использования внешних данных. Снимите этот флажок, чтобы сохранить, и определение запроса, и данные. Этот флажок, будет доступен только после установления флажка Обновлять при открытии книги.
Автоматизация обновления запросов с помощью VBA
Приемы, описанные выше, и не использующие макросы, обходятся без каких-либо окон, предупреждающих о безопасности. Кроме того, такие книги легче переносить в Power BI, так как они не вызывают никаких проблем с блокировкой. Однако если вы работаете исключительно в настольном экземпляре Excel, иногда может потребоваться предоставить пользователю удобный и очевидный способ обновления решений Power Query. Это может быть сделано с помощью записи кода VBA.
Можно создать макрос для обновления одного подключения Power Query. В файле Automating Refresh.xlsx и перейдите на лист Transactions. На листе расположена Таблица Transactions, а также сводная таблица. Допустим, вы хотите создать макрос для обновления их обеих. Перейдите на вкладку Разработчик. Если вы не видите вкладку Разработчик, щелкните правой кнопкой мыши любую вкладку на ленте и выберите Настроить ленту. В правом окне установите флажок рядом с вкладкой Разработчик и нажмите Ok. На вкладке Разработчик нажмите кнопку Запись макроса. Также можно нажать иконку в левом нижнем углу окна Excel:
Рис. 16.2. Кнопка Начать запись макроса
Как только вы нажмете эту кнопку, Excel начнет записывать каждый щелчок листа, каждое нажатие клавиши и… каждую ошибку, которую вы делаете. Выполните в точности следующие действия. Дайте макрос Refresh и сохраните его в текущей книге. Пройдите по меню Данные –> Запросы и подключения –> В правой части окна Excel в области Запросы и подключения кликните правой кнопкой мыши на запросе Transactions –> Обновить. Щелкните правой кнопкой мыши любую ячейку сводную таблицу –> Обновить. Повторно кликните кнопку, как на рис. 16.2. Запись макроса будет остановлена. Проверьте код. Пройдите по меню Вид –> Макросы:
Нажмите Изменить. Откроется окно редактора VBA
Рис. 16.4. Код VBA; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Добавьте кнопку для обновления макроса. Перейдите на вкладку Разработчик –> Вставить –> Кнопку:
Рис. 16.5. Вставить элемент управления Кнопку
Выберите место на листе, и удерживая левую кнопку мыши, нарисуйте прямоугольник для Кнопки. Отпустите левую кнопку мыши. Появится окно с предложением поставить в соответствие Кнопке макрос. Выберите Refresh. Нажмите Ok. Щелкните правой кнопкой мыши по Кнопке –> Изменить текст. Введите Обновить. Щелкните любую ячейку на листе, чтобы выйти из режима редактирования названия кнопки.
Рис. 16.6. Кнопка, подключенная к макросу
Обновление нескольких запросов
Теперь можно добавить иные запросы в макрос. Пройдите по меню Разработчик –> Макросы –> Refresh –> Изменить. На данный момент код имеет вид (см. также рис. 16.4):
Обработка ошибок источника данных (Power Query)
Когда вы, наконец, настроили источники данных и формируете данные именно так, как вам нужно, это очень важно. Надеемся, что при обновлении данных из внешнего источника данные будут проходить плавно. Но это не всегда так. Изменение потока данных на этом пути может привести к проблемам, которые при попытке обновить данные могут стать ошибками. Некоторые ошибки может быть легко исправить, некоторые из них могут быть временными, а некоторые трудно диагностировать. Вот что следует сделать, это набор стратегий, которые можно использовать для обработки ошибок, которые вам последуют.
При обновлении данных могут возникать ошибки двух типов.
Местных Если в книге Excel возникает ошибка, по крайней мере, ваши усилия по устранению неполадок ограничены и ими можно управлять. Возможно, обновленные данные вызывали ошибку с функцией или создавали недопустимые условия в списке. Эти ошибки надоедливы, но их довольно просто отслеживать, определять и исправлять. Excel улучшена обработка ошибок с помощью более понятных сообщений и контекстных ссылок на целевые разделы справки, которые помогут вам разобраться и устранить проблему.
Удаленного Однако ошибки, которые приходят из удаленного внешнего источника данных, являются другими. Произошла что-то в системе, которая может быть на улице, на полпути по всему миру или в облаке. Для таких типов ошибок требуется другой подход. Распространенные удаленные ошибки:
Не удалось подключиться к службе или ресурсу. Проверьте подключение.
Не удалось найти файл, к который вы пытаетесь получить доступ.
Сервер не отвечает и, возможно, находится в состоянии обслуживания.
Это содержимое не доступно. Возможно, он был удален или временно недоступен.
Подождите. данные загружаются.
Вот несколько советов, которые помогут вам справиться с ошибками, которые могут возникнуть.
Поиск и сохранение конкретной ошибки Сначала проверьте области Запросы & connections (Выберите Data > Queries & Connections, выберите подключение, а затем отобразите вылет). Узнайте, какие ошибки произошли при доступе к данным, и обратите внимание на дополнительные сведения. Затем откройте запрос, чтобы увидеть все конкретные ошибки в каждом шаге запроса. Все ошибки отображаются на желтом фоне для удобной идентификации. Запишите сообщение об ошибке или запишите ее на экране, даже если вы не полностью понимаете ее. Коллега, администратор или служба поддержки в вашей организации могут помочь вам понять, что произошло, и предложить решение. Дополнительные сведения см. в теме Работа с ошибками в Power Query.
Получить сведения о справке На сайте Office справки и обучения. Она не только содержит большой объем справки, но и сведения об устранении неполадок. Дополнительные сведения см. в устранении и обходных решениях недавних проблем в Excel для Windows.
Использование технического сообщества Используйте веб-Community Майкрософт для поиска обсуждений, относящихся к вашей проблеме. Весьма вероятно, что вы не первый, кто испытывает проблему, другие люди занимаются ее решением и даже могут найти решение. Дополнительные сведения см. в Microsoft Excel Community и Office Answers Community.
Поиск в Интернете Используйте предпочитаемую поисковую система для поиска дополнительных сайтов в Интернете, которые могут предоставлять обсуждения или подсказки. Это может быть отнимает много времени, но это может привести к более широкой сети для того, чтобы найти ответы на наиболее сложные вопросы.
Обратитесь в Office поддержки На этом этапе, скорее всего, вы понимаете проблему гораздо лучше. Это поможет вам сосредоточиться на беседе и сократить время, затраченное на поддержку Майкрософт. Дополнительные сведения см. в Microsoft 365 и Office службе поддержки клиентов.
Возможно, вам не удастся устранить проблему, но вы можете точно определить, в чем заключается проблема, чтобы помочь другим понять ситуацию и решить ее за вас.
Проблемы со службами и серверами Скорее всего, причина — периодические ошибки сети и связи. Лучше всего подождите и попробуйте еще раз. Иногда проблема просто утихает.
Изменения расположения или доступности База данных или файл были перемещены, повреждены, переведены в автономный режим на обслуживание или аварийно сбой базы данных. Дисковые устройства могут быть повреждены, а файлы будут потеряны. Дополнительные сведения см. в этой Windows 10.
Изменения в проверке подлинности и конфиденциальности Неожиданно может произойти, что разрешение больше не работает или в параметр конфиденциальности было внося изменение. Оба события могут препятствовать доступу к внешнему источнику данных. Обратитесь к администратору или администратору внешнего источника данных, чтобы узнать, что изменилось. Дополнительные сведения см. в настройкахи разрешениях источника данных и Настройка уровней конфиденциальности.
Открытые или заблокированные файлы Если открыт текст, CSV или книга, изменения, внесенные в файл, не включаются в обновление до тех пор, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован и к нему нельзя получить доступ, пока он не будет закрыт. Это может произойти, если другой человек использует версию Excel. Попросите их закрыть файл или проверить его. Дополнительные сведения см. в статьи Разблокировкафайла, заблокированного для редактирования.
Изменения схем на заднем Кто-то изменяет имя таблицы, имя столбца или тип данных. Это почти никогда не разумно, может иметь огромное влияние и особенно опасно для баз данных. Одной из них является то, что группа управления базами данных наила правильные средства контроля, чтобы избежать этого, но происходят спапцы.
Блокирование ошибок при сложении запросов Power Query пытается повысить производительность, когда это возможно. Для более производительности и емкости часто бывает лучше выполнить запрос к базе данных на сервере. Этот процесс называется сгибом запроса. Тем не менее Power Query блокирует запрос, если существует вероятность компрометации данных. Например, слияние определено между таблицей книги и SQL Server таблицей. Для конфиденциальности данных книги за установлено SQL Server конфиденциальность данных организации. Поскольку политика конфиденциальности является более строгой, чем в организации, Power Query блокирует обмен информацией между источниками данных. Сгиб запроса происходит за кадром, поэтому вас может удивить, когда возникает ошибка блокировки. Дополнительные сведения см. взадачах Основные сведения о сгибе запросов, Сгибзапросов и Сгиб с помощью диагностики запросов.
Часто с помощью Power Query вы можете точно определить, в чем заключается проблема, и устранить ее самостоятельно.
Переименованные таблицы и столбцы Изменения исходных имен таблиц и столбцов или столбцов почти наверняка приводят к проблемам при обновлении данных. Запросы используют имена таблиц и столбцов для формировать данные практически на каждом этапе. Не изменяйте или удаляйте исходные имена таблиц и столбцов, если только их не нужно использовать в источнике данных.
Изменения типов данных Изменение типа данных иногда может привести к ошибкам или непредвиденным результатам, особенно в функциях, для которых в аргументах требуется определенный тип данных. Примерами могут быть замена текстового типа данных в числовой функции или попытка вычисления с нечисловой типом данных. Дополнительные сведения см. в теме Добавление и изменение типов данных.
Ошибки уровня ячейки Такие типы ошибок не предотвращают загрузку запроса, но отображают в ячейке сообщение Ошибка. Чтобы увидеть сообщение, выберите whitespace в ячейке таблицы, содержащей ошибку. Вы можете удалить, заменить или просто сохранить ошибки. Примеры ошибок в ячейках:
Преобразования Вы попытались преобразовать ячейку, содержащую 0, в целое число.
Математические Вы пытаетесь умножить текстовое значение на числовое значение.
Объединения Вы попытались объединить строки, но одна из них числовая.
Безопасно экспериментируйте и итерации Если вы не уверены, что преобразование может иметь отрицательное влияние, скопируйте запрос, проверьте изменения и итерации с помощью вариантов команды Power Query. Если команда не работает, просто удалите созданное вами шаг и попробуйте еще раз. Чтобы быстро создать образец данных с одной схемой и структурой, создайте Excel таблицу из нескольких столбцов и строк и импортировать их (выберите данные > Из таблицы илидиапазона). Дополнительные сведения см. в таблицах Создание таблицы и Импорт из Excel таблицы.
Когда вы впервые будете понять, что можно делать с данными в редакторе Power Query, вам может показаться, что вы ребенок в конфетном магазине. Но не хочется есть все конфеты. Вы хотите избежать преобразования, которое может непреднамеренно вызывать ошибки обновления. Некоторые операции, например перемещение столбцов в другое место таблицы, не должны приводить к ошибкам в обновлении, так как Power Query отслеживает столбцы по их именам.
Другие операции могут привести к ошибкам обновления. Одним из общих правил может быть ваш световой свет. Не внося существенных изменений в исходные столбцы. Чтобы безопасно воспроизвести столбец, скопируйте исходный столбец с командой (Добавитьстолбец, Настраиваемый столбец, Дублировать столбец и так далее), а затем внести изменения в скопированную версию исходного столбца. Вот операции, которые иногда могут привести к ошибкам обновления, и некоторые из лучших методик, которые помогут ухладить работу.
Повышение эффективности за счет максимально ранней фильтрации данных в запросе и удаления ненужных данных для уменьшения лишней обработки. Кроме того, с помощью автофильтра можно искать или выбирать определенные значения, а также использовать фильтры для определенных типов, доступные в столбцах даты, даты и времени и времени (например, Месяц, Неделя, День).
Типы данных и заглавные колонок столбцов
Power Query автоматически добавляет в запрос два шага сразу после первого шага: «Продвиганые заглавные колонок», которая преобразует первую строку таблицы в заглавный, и Changed Type(Измененный тип), который преобразует значения из типа Данных Any в тип данных на основе проверки значений из каждого столбца. Это удобно, но иногда может потребоваться явно контролировать это поведение, чтобы предотвратить ошибки случайного обновления.
Дополнительные сведения см. в статьях Добавление и изменение типов данных и Повысить или понизить их в строках и столбцах.
Избегайте переименования исходных столбцов. Используйте команду Переименовать для столбцов, добавленных другими командами или действиями.
Разделение копий исходного столбца, а не исходного столбца.
Объединять копии исходных столбцов, а не исходных.
Если нужно сохранить небольшое количество столбцов, используйте выбор столбца, чтобы сохранить нужные.
Рассмотрим разницу между удалением столбцов и удалением других столбцов. Когда вы удаляете другие столбцы и обновляете данные, новые столбцы, добавленные в источник данных после последнего обновления, могут остаться незащищенными, так как они будут считаться другими столбцы при повторном выполнении в запросе шага Удалить столбец. Такая ситуация не возникает при явном удалите столбец.
Наконечник Скрыть столбец (как в Excel) не Excel. Однако если у вас много столбцов и вы хотите скрыть многие из них, чтобы сосредоточиться на своей работе, вы можете сделать следующее: удалить столбцы, запомнить созданный шаг, а затем удалить его перед загрузкой запроса обратно на таблицу.
Дополнительные сведения см. в статье Удаление столбцов.
При замене значения источник данных не редактируется. Вместо этого нужно изменить значения в запросе. При следующем обновлении данных ищемые значения могут немного измениться или перестать быть там, поэтому команда Заменить может не работать так, как планировалось изначально.
Дополнительные сведения см. в области Замена значений.
При использовании команды Столбец сводной сводной столбца при сводном столбце может возникнуть ошибка, при этом не агрегируются значения, но возвращается больше одного значения. Такая ситуация может возникнуть после операции обновления, которая меняет данные несмежным образом.
Используйте команду Открепить другие столбцы, если известны не все столбцы и вы хотите, чтобы новые столбцы, добавленные во время обновления, также были неотвечены.
Используйте команду Открепить только выбранный столбец, если вы не знаете количество столбцов в источнике данных и хотите, чтобы выбранные столбцы оставались неотвеченными после обновления.
Дополнительные сведения см. в статьях Сводные столбцы и Ото всех столбцов.
Предотвращение ошибок Если внешним источником данных управляет другая группа в организации, им необходимо знать о вашей зависимости от них и избегать изменений в их системах, которые могут привести к проблемам ниже. Фиксировать влияние на данные, отчеты, диаграммы и другие артефакты, которые зависят от данных. Настройте линии связи, чтобы убедиться в том, что они понимают последствия, и примите необходимые меры, чтобы обеспечить бесперебойную работу. Находите способы создания элементов управления, которые минимизируют ненужные изменения и предугадать последствия необходимых изменений. Конечно, это легко сказать и иногда сложно сделать.
Future-proof with query parameters Используйте параметры запроса для уменьшения изменений, например расположения данных. Вы можете создать параметр запроса, чтобы заменить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.
Управление запросами (Power Query)
Управлять запросами можно в области «Запросы» редактора Power Query или на вкладке Запросы в области «Запросы & подключения» Excel. Это особенно полезно, если у вас много запросов и вы хотите быстро найти их. После того как вы найдете запрос, можно сделать ряд дополнительных действий, таких как дублирование, ссылка, слияние, приложение, совместное действие и удаление запроса.
При создании запроса сведения о подмыве создаются за кадром. Роль запроса заключается в формировании данных. Роль сведений о под соединении заключается в сохранении сведений об источнике данных и их обновлении в соответствии с настройками обновления, такими как частота.
Ниже перечислены команды, которые можно использовать в том порядке, в котором они отображаются в соответствующих контекстных меню на вкладке Запросы в области «Запросы & Подключение» в Excel или в области Запросы в редакторе Power Query. Некоторые команды доступны только в одной области. Многие из этих команд также доступны на контекстной вкладке Запрос на ленте. Все запросы в книге отображаются и сортироваться по дате и времени последнего изменения, начиная с последнего.
Сделайте одно из следующего: В Excel Выберите данные> запросы &
В редакторе Power Query Выберите данные > получить данные > запустить редактор Power Queryи просмотреть области Запросы слева.
Выберите команду управления запросами: Изменение запроса
в редакторе Power Query. Доступно только на вкладке Запросы в области & подключений.
Удалить Удаляет запрос.
Переименование Изменяет имя запроса.
Обновление Позволяет получить данные из внешних источников. Доступно только на вкладке Запросы в области & подключений. Дополнительные сведения см. в этойExcel.
Загрузить в Выводит диалоговое окно Импорт данных, в котором можно выбрать, как вы хотите просмотреть данные, куда их поместить, и добавить их в модель данных. Доступно только на вкладке Запросы в области & подключений.
Дублирование Создает копию выбранного запроса с тем же именем, что и у исходного запроса (2). Вы можете переименовать запрос, чтобы его было проще идентифицировать. Последующие изменения исходного запроса не влияют на новый запрос.
Справка Создает новый запрос, который использует шаги предыдущего запроса, не повторяя его. Новый запрос будет следовать тому же соглашению об именовании, что и команда Дублировать. Вы можете переименовать новый запрос, чтобы его было проще идентифицировать. Последующие изменения исходного запроса, которые приводят к изменению выходных данных, влияют на новый запрос.
Слияние Объединение столбцов в запросе с совпадающих столбцов в других запросах. Дополнительные сведения о слиянии см. в этой теме. Доступно только на вкладке Запросы в области & подключений.
«Приложение» В запрос можно также вовсю вть столбцы. Дополнительные сведения о приложении см. в этой теме. Доступно только на вкладке Запросы в области & подключений.
Переместить в группу Перемещает запрос в группу в списке или, если групп нет, выберите Создать группу, чтобы создать ее. Придай ему осмысленное имя, например Бюджет или Маркетинг. Группы используются для у упорядотки множества запросов по категориям. Вы даже можете создавать группы в группах.
Вверх Перемещает запрос вверх в списке запросов.
Переместить вниз Перемещает запрос вниз в списке запросов.
Функция Create (Создать функцию) выводит диалоговое окно Создание функции. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в справке по функции Power Query M.
Преобразовать в параметр Преобразует запрос в параметр и отображает его в области Предварительный просмотр данных. Если текущий дисплей является параметром, команда перенастроит параметр Преобразовать в запрос. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в странице Создание запроса с параметрами (Power Query).
Расширенный редактор Открывает окно Расширенный редактор. Дополнительные сведения см. в этойExcel. Доступно только в области Запросы редактора Power Query.
Экспорт файла подключения Сохраняет запрос в качестве ODC-файла подключения. ODC-файл содержит определение запроса, сведения об подключениях к источнику данных и все этапы преобразования. Это полезно, если вы хотите поделиться запросами с другими пользователями и книгами. Кроме того, можно воспользоваться командой Свойства на контекстной вкладке Запрос ленты Power Query. Дополнительные сведения см. в статьи Создание, изменение и управление подключениями к внешним данным. Доступно только на вкладке Запросы в области & подключений.
Показать обзор Выводит вылет запроса с предварительным просмотром данных и другими полезными сведениями. Доступно только на вкладке Запросы в области & подключений.
Свойства Эта команда используется для управления запросом и сведениями об источниках данных. На вкладке Запросы в области & подключенияоткрывается диалоговое окно Свойства запроса. На области Запросы в редакторе Power Query откроется Параметры запроса.
Вы можете включить или отключить отображение вылетного запроса при наведении курсором мыши на имя запроса на вкладке Запрос в области Запросы & подключения. Этот параметр управляет отображением вылетов только при наведении курсором. Вы по-прежнему можете отобразить вылет запроса, щелкнув правой кнопкой мыши имя запроса и выбрав показать обзор.
Выберите Параметры запроса> получить > данных.
Слева в области GLOBALвыберите Общие.
Справа в области Предварительный просмотр запроса сведите с нее поле Показать обзор при наведении курсоров. По умолчанию при наведении курсором на экран отображается вылет.