Настройка Power Query
В данной статье описан порядок подключения надстройки Power Query и необходимые действия по ее настройке.
Разбор возможных ошибок при обновлении запросов и порядок их устранения см. в разделе «Возможные ошибки» статей к соответствующим кнопкам:
1. На компьютере пользователя должна быть установлена надстройка для Microsoft Excel – Power Query версии не ниже 2.49 (доступна в качестве отдельной надстройки в Microsoft Excel 2013, скачать которую можно на сайте Microsoft; в версиях 2016 и 2019 присутствует в качестве встроенной функции на вкладке «Данные»).
2. Для корректного обновления запросов установите соответствующие требования к уровням конфиденциальности Power Query в файле.
a. Для Microsoft Excel 2013
| Перейдите на ленте на вкладку Power Query -> Настройки -> Параметры: |
b. Для Microsoft Excel 2016/2019
| Перейдите на вкладку Данные -> откройте выпадающий список под кнопкой Получить данные -> Параметры запроса: |
c. В появившемся окне в разделе Глобальные откройте пункт меню Конфиденциальность и выберите опцию « Всегда игнорировать параметры уровней конфиденциальности »:
3. Проверьте, разрешены ли подключения к внешним данным.
Чтобы разрешить подключения к внешним данным, перейдите на вкладку Файл -> Параметры -> Центр управления безопасностью -> Параметры центра управления безопасностью , а затем в группе Параметры безопасности для подключения к данным выберите опцию « включить все подключения к данным », в группе Параметры безопасности для связей в книге — « включить автоматическое обновление для всех связей в книге ».
Обновление набора данных, созданного из книги Excel на локальном диске
Следующие возможности устарели и больше не будут доступны начиная с 29 сентября 2023 г.
- Отправка локальных книг в рабочие области Power BI больше не будет разрешена.
- Настройка планирования обновления и обновления теперь для файлов Excel, которые еще не настроены на запланированное обновление, больше не будут разрешены.
Следующие возможности устарели и больше не будут доступны начиная с 31 октября 2023 г.
- Запланированное обновление и обновление теперь для существующих файлов Excel, настроенных ранее для запланированного обновления, больше не будет разрешено.
- Локальные книги, отправленные в рабочие области Power BI, больше не будут открываться в Power BI.
После 31 октября 2023 г.:
- Вы можете скачать существующие локальные книги из рабочей области Power BI.
- Модель данных Excel можно опубликовать как набор данных Power BI и запланировать обновление.
- Книги Excel можно импортировать из библиотек документов OneDrive и SharePoint, чтобы просмотреть их в Power BI.
Если ваша организация использует эти возможности, см. дополнительные сведения о переносе книг Excel.
В Power BI обновление теперь и расписание обновления поддерживается для наборов данных, созданных из книг Excel, импортированных с локального диска, где Power Query или Power Pivot используется для подключения к любому из следующих источников данных и загрузки данных в модель данных Excel. Power Query — получение & данных преобразования в Excel 2016.
Шлюз Power BI — персональный
- Все источники данных в Интернете, показанные в Power Query.
- Все локальные источники данных, отображаемые в Power Query, за исключением файлов Hadoop (HDFS) и Microsoft Exchange.
- Все источники данных в Интернете, показанные в Power Pivot.
- Все локальные источники данных, отображаемые в Power Pivot, за исключением файлов Hadoop (HDFS) и Microsoft Exchange.
Локальный шлюз данных
Локальный шлюз данных поддерживает обновление для следующих источников данных:
- Табличный режим служб Analysis Services
- Многомерные службы Analysis Services
- SQL Server
- SAP HANA
- Oracle;
- Teradata
- Файл
- Папка
- Список SharePoint (локальная среда)
- Интернет
- OData
- IBM DB2
- MySQL
- Sybase
- SAP BW
- База данных IBM Informix
- ODBC
Помните следующие заметки:
- Шлюз должен быть установлен и запущен, чтобы служба Power BI подключиться к локальным источникам данных и обновить набор данных.
- При использовании Excel 2013 убедитесь, что вы обновили Power Query до последней версии.
- Обновление не поддерживается для книг Excel, импортированных с локального диска, где данные существуют только в листах или связанных таблицах. Обновление поддерживается для данных листа, если он хранится и импортируется из OneDrive. Дополнительные сведения см. в статье Об обновлении набора данных, созданного из книги Excel в OneDrive или SharePoint Online.
- При обновлении набора данных, созданного из книги Excel, импортированной с локального диска, обновляется только данные, запрашиваемые из источников данных. Если изменить структуру модели данных в Excel или Power Pivot, например создать новую меру или изменить имя столбца, эти изменения не копируются в набор данных. При внесении таких изменений перезагрузите или повторно опубликуйте книгу. Если вы планируете внести регулярные изменения в структуру книги и хотите, чтобы эти изменения отображались в наборе данных в служба Power BI без необходимости повторной загрузки, рекомендуется поместить книгу в OneDrive. Служба Power BI автоматически обновляет данные структуры и листа из книг, хранящихся и импортированных из OneDrive.
Разделы справки убедитесь, что данные загружаются в модель данных Excel?
При использовании Power Query для подключения к источнику данных существует несколько вариантов загрузки данных. Power Query — получение & данных преобразования в Excel 2016. Чтобы убедиться, что данные загружались в модель данных, необходимо выбрать параметр «Добавить эти данные в модель данных» в диалоговом окне «Загрузка в «.
На рисунках ниже показан Excel 2016.
В навигаторе выберите «Загрузить в.
Или, если выбрать «Изменить» в навигаторе, откройте Редактор запросов. Там можно выбрать «Закрыть & загрузку».
Затем в разделе «Загрузка» убедитесь, что вы выбрали » Добавить эти данные» в модель данных.
Что делать, если вы используете получение внешних данных в Power Pivot?
Нет проблем. При использовании Power Pivot для подключения к данным и запроса данных из локального или сетевого источника данных данные автоматически загружаются в модель данных.
Разделы справки расписание обновления?
При настройке расписания обновления Power BI подключается непосредственно к источникам данных, используя сведения о подключении и учетные данные в наборе данных для запроса обновленных данных, а затем загружает обновленные данные в набор данных. Все визуализации в отчетах и панелях мониторинга на основе этого набора данных в служба Power BI также обновляются.
Дополнительные сведения о настройке расписания обновления см. в разделе «Настройка расписания обновления».
Когда что-то пошло не так
Когда все происходит неправильно, обычно это связано с тем, что Power BI не может войти в источники данных или если набор данных подключается к локальному источнику данных, шлюз находится в автономном режиме. Убедитесь, что Power BI может войти в источники данных. Если пароль, используемый для входа в источник данных, изменяется или Power BI выходит из источника данных, обязательно попробуйте войти в источники данных еще раз в учетных данных источника данных.
Не забудьте оставить сообщение электронной почты об ошибке отправки обновления для меня выбранным. Вы хотите знать сразу, если запланированное обновление завершается ошибкой.
Обновление не поддерживается для веб-каналов OData, подключенных к и запрашиваемых из Power Pivot. При использовании веб-канала OData в качестве источника данных используйте Power Query.
Устранение неполадок
Иногда обновление данных может идти не так, как ожидалось. Обычно проблемы возникают из-за проблемы, связанной с шлюзом. Ознакомьтесь со статьями по устранению неполадок шлюза для средств и известных проблем.
- Устранение неполадок локального шлюза данных
- Устранение неполадок с Power BI Gateway — персональный
Источники данных Power Query в версиях Excel
Ниже приведены сведения о том, какие версии Excel включают в себя интерфейс Power Query (известный как Получение & преобразования в Excel).
Excel 2016 или более поздней версии для Windows и Microsoft 365
Интерфейс Power Query доступен во всех автономных версиях Windows Excel 2016 или более поздних версиях и планах подписки Microsoft 365 на вкладке Данные в группе Получение преобразования &. В следующей таблице показаны базовые и расширенные функции, доступные по версии.
Имя соединителя
Office 2016
Домашняя & студент
Домашняя & Бизнес Стандарт
Профессиональный
Office 2016
Профессиональный плюс
Автономный
Office 2019
Домашняя & студент
Домашняя & Бизнес Стандарт
Профессиональный
Office 2019
Профессиональный плюс
Автономный
Microsoft 365
Дома
Личные
Бизнес
Бизнес Премиум
Microsoft 365
Приложения для предприятий
SQL Server Analysis Services (импорт)
база данных Azure SQL
Azure SQL Data Warehouse
Azure HDInsight (HDFS)
Хранилище BLOB-объектов Azure
Табличное хранилище Azure
Azure Data Lake Storage
Azure Data Explorer
Список SharePoint Online
Microsoft Exchange Online
Dynamics 365 (интернет-версия)
Важно! Уведомление о прекращении использования соединителя данных Facebook Импорт и обновление данных из Facebook в Excel перестанет работать в апреле 2020 г. До этого момента вы по-прежнему сможете использовать соединитель facebook Power Query. Но начиная с апреля 2020 года вы не сможете подключиться к Facebook, и вы получите сообщение об ошибке при попытке использовать соединитель Facebook. Мы рекомендуем как можно скорее пересмотреть или удалить все существующие запросы Power Query, использующие соединитель Facebook, чтобы избежать непредвиденных результатов.
Таблица или диапазон
Файл Hadoop (HDFS)
Выбор между Автономной версией Microsoft 365 и Professional Plus
Так как это версия на основе подписки, Microsoft 365 в любой момент времени содержит все доступные Power Query функции. Его можно представить как главный набор. Версии Professional Plus Standalone (также называемые бессрочными) имеют большое подмножество функций, но не все из них. Например, нечеткое слияние поддерживается в Microsoft 365, но не в 2019 году с бессрочной лицензией. В 2019 году было добавлено больше функций бессрочного действия, чем было доступно в 2016 году, и еще больше будет добавлено в следующую бессрочную версию под названием 2021. Учитывая эту ситуацию, у вас есть два варианта. Первый вариант — дождаться следующего обновления до бессрочной версии, если требуется определенная функция. Этот выбор может сработать, но по правде говоря, он не может. Мы не можем гарантировать, что определенная функция будет добавлена в следующую бессрочную версию. Второй вариант — подписка на Microsoft 365. Мы рекомендуем вам сделать этот выбор, чтобы все Power Query функции были доступны вам. Дополнительные сведения см. в статье Microsoft 365 с приложениями Office.
Excel 2010 и Excel 2013 для Windows
Power Query — это бесплатная надстройка, которую можно скачать здесь: Скачайте надстройку Power Query. Хотя надстройка больше не поддерживается официально, ее все равно можно использовать. После включения функции Power Query доступны на вкладке Power Query на ленте Excel.
Excel в Интернете
В следующей таблице перечислены поддерживаемые источники данных и возможности их выполнения.
Примечание:
- Обновление запросов, загруженных в модель данных, пока не поддерживается.
- Обновление запросов в книгах, сохраненных в стороннем облачном расположении, пока не поддерживается.
Источник данных
Как обновить power query в excel 2016
Кнопка «Обновить текущий запрос»
В данной статье описан функционал кнопки Обновить текущий запрос , которая используется для запуска обновления запроса Power Query, выводимого на лист в расчетном файле.
Кнопка Обновить текущий запрос располагается в группе Работа с выгрузками – Power Query на вкладке FIN.by Tools Free на ленте Excel.
Кнопка предназначена для запуска автоматического обновления одного запроса Power Query, выводимого на лист в расчетном файле.
Перед запуском обновления необходимо сделать активной любую ячейку «умной» таблицы, данные которой необходимо обновить с помощью запроса Power Query.
После нажатия кнопки Обновить текущий запрос будет запущен процесс обновления запроса. Одновременно появится окно с просьбой не совершать никаких действий с файлом во время обновления запроса.
ВНИМАНИЕ! Импорт данных из внешних источников может занять некоторое время.
При попытке обновления запроса на защищенном листе возникнет диалоговое окно с просьбой ввести пароль:
В случае, если лист защищен без пароля, оставьте поле ввода пустым и нажмите « ОК » .
В случае нажатия кнопки «Отмена» обновление запроса будет остановлено и появится окно с соответствующим сообщением:
При успешном обновлении запроса появится окно с соответствующим уведомлением:
Прежде чем приступать к импорту данных из внешних источников с помощью надстройки, необходимо убедиться, что верно указаны адреса источников данных.
В случае, если к какому-либо из источников данных указан неверный или несуществующий путь, обновить данные нельзя (см. раздел «Возможные ошибки»).
Ниже приведены возможные ошибки при использовании кнопки:
Возможная причина — встроенные в Power Query проверки конфиденциальности данных при обновлении запросов. Решение — измените параметры конфиденциальности в Power Query:
• для Excel 2010/2013 : вкладка Power Query -> группа Настройки -> кнопка Параметры;
• для Excel 2016/2019 : вкладка Данные -> выпадающий список под кнопкой Получить данные -> Параметры запроса;
• в появившемся окне в разделе ‘Глобальные’ в пункте меню ‘Конфиденциальность’ выберите опцию «Всегда игнорировать параметры уровней конфиденциальности».
Данное сообщение об ошибке появляется в связи с некорректной настройкой Power Query, а именно в результате встроенных в Power Query проверок конфиденциальности данных при обновлении запросов (см. п.2 статьи Настройка Power Query).
Сообщение об ошибке может содержать следующий текст:
«В ходе вычислений была обнаружена циклическая ссылка.»
«Для объединения данных необходимо указать сведения. «
«Запрос ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных.»
Для устранения данной ошибки измените параметры конфиденциальности на опцию «Всегда игнорировать параметры уровней конфиденциальности» по инструкции в диалоговом окне.
Данное сообщение об ошибке появляется в связи с некорректной настройкой Power Query, а именно в результате встроенных в Power Query проверок конфиденциальности данных при обновлении запросов (см. п.2 статьи Настройка Power Query).
Для устранения данной ошибки измените параметры конфиденциальности на опцию «Всегда игнорировать параметры уровней конфиденциальности» по инструкции в диалоговом окне.
Данное сообщение об ошибке появляется в связи с некорректной настройкой Power Query, а именно в результате встроенных в Power Query проверок конфиденциальности данных при обновлении запросов (см. п.2 статьи Настройка Power Query).
Для устранения данной ошибки измените параметры конфиденциальности на опцию «Всегда игнорировать параметры уровней конфиденциальности» по инструкции в диалоговом окне.
Данное сообщение об ошибке появляется, если папка или файл с внешними данными, предназначенными для обработки и импорта с помощью Power Query, не существует по указанному адресу (переименован или перемещен).
Данное сообщение об ошибке появляется, если в рабочем файле отсутствует Справочник запросов или присвоено неправильное имя «умной» таблицы (должно быть «Запросы» ).
Данное сообщение об ошибке появляется, если выбрана любая ячейка вне таблиц, обновляющихся с помощью PQ-запросов. Перед запуском обновления необходимо выбрать любую ячейку «умной» таблицы, данные которой необходимо обновить с помощью запроса Power Query.
Данное сообщение об ошибке появляется в случае ввода неверного пароля в диалоговом окне « Введите пароль защиты », которое появляется при попытке обновления запроса на защищенном листе .
Данное сообщение об ошибке появляется в случае, если надстройка Power Query не установлена или не подключена.