Power Query предлагает несколько способов создания и загрузки запросов Power в книгу. Вы также можете задать параметры загрузки по умолчанию в окне "Параметры запроса ".
Совет Чтобы определить, сформированы ли данные на листе с помощью Power Query, выберите ячейку данных, и если появится вкладка ленты контекста запроса, данные были загружены из Power Query.
Об интеграции Power Query в Excel
Узнайте, в какой среде вы находитесь Power Query хорошо интегрирован в пользовательский интерфейс Excel, особенно при импорте данных, работе с подключениями и редактировании сводных таблиц, таблиц Excel и именованных диапазонов. Чтобы избежать путаницы, важно в любой момент времени знать, в какой среде вы находитесь, Excel или Power Query.
| Знакомый лист Excel, лента и сетка | Лента редактора Power Query и предварительный просмотр данных |
|---|---|
|
|
Например, работа с данными на листе Excel принципиально отличается от Power Query. Кроме того, связанные данные, которые вы видите на листе Excel, могут влиять на работу Power Query для формирования данных. Это происходит только при загрузке данных в лист или модель данных из Power Query.
Переименование ярлычков листов Переименовывайте вкладки листов осмысленно, особенно если их много. Особенно важно прояснить разницу между листом данных и листом, загруженным из редактора Power Query. Даже если у вас всего два листа, один из которых содержит таблицу Excel ( Лист1), а другой — запрос, созданный путем импорта этой таблицы (Таблица1), так легко запутаться. Всегда рекомендуется менять имена вкладок листов по умолчанию на более понятные. Например, переименуйте лист Лист1 в DataTable , а Таблицу Table1 — в QueryTable. Теперь понятно, какая вкладка содержит данные, а какая — запрос.
Создание запроса
Вы можете создать запрос из импортированных данных или создать пустой запрос.
Создание запроса из импортированных данных
Это самый распространенный способ создания запроса.
- Импортируйте некоторые данные. Дополнительные сведения см. в статье Импорт данных из внешних источников.
- Выберите ячейку в данных и выберите команду "Изменить запрос>".
Создание пустого запроса
Возможно, вы захотите просто начать с нуля. Это можно сделать двумя способами.
- Выбор данных>Получение данных>из других источников>Пустой запрос.
- Выберите "Данные>" Получение данных>Запустите редактор Power Query.
На этом этапе вы можете вручную добавить шаги и формулы, если хорошо знаете язык формул Power Query M.
Можно также нажать " Главная " и выбрать команду в группе "Создать запрос ". Выполните одно из указанных ниже действий.
- Выберите "Создать источник", чтобы добавить источник данных. Эта команда аналогична команде "Получить данные>" на ленте Excel.
- Выберите "Недавние источники", чтобы выбрать источник данных, с которым вы работали. Эта команда аналогична> команде"Последние источники" на ленте Excel.
- Выберите "Введите данные", чтобы ввести данные вручную. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.
Отправка запроса
Если запрос обоснован и не содержит ошибок, его можно загрузить обратно на лист или в модель данных.
Загрузка запроса из редактора Power Query
В редакторе Power Query выполните одно из указанных ниже действий.
Для загрузки на лист выберите «Домашняя> страница»,«Закрыть» & «Загрузить>»,«Закрыть» & «Загрузить».
Для загрузки в модель данных выберите "Домашняя> страница"& "Закрыть загрузку>"& "Загрузить в".
В диалоговом окне"Импорт данных " нажмите кнопку "Добавить эти данные в модель данных".
Совет Иногда команда "Load To " неактивна или неактивна. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку "Закрыть& загрузить, откройте на новом листе вкладку "Запросы данных>"& "Запросы подключений>", щелкните запрос правой кнопкой мыши и выберите"Загрузить по". Или на ленте редактора Power Query выберите "Отправить запрос>".
Загрузка запроса из области "Запросы и подключения"
В Excel может потребоваться загрузить запрос на другой лист или в другую модель данных.
- В Excel выберите "Запросы данных>"& "Подключения", а затем откройте вкладку "Запросы".
- В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите "Отправить в". Откроется диалоговое окно Импорт данных.
- Выберите способ импорта данных и нажмите кнопку "ОК". Чтобы узнать больше об использовании этого диалогового окна, выберите вопросительный знак (?).
Изменение запроса на листе
Существует несколько способов редактирования запроса, загруженного на лист.
Изменение запроса на основе данных на листе Excel
- Чтобы отредактировать запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных и нажмите "Изменить запрос>".
Изменение запроса на панели "Запросы & подключения"
Панель "Запросы & подключения" удобнее использовать, когда в одной книге много запросов и нужно быстро найти один из них.
- В Excel выберите "Запросы данных>"& "Подключения", а затем откройте вкладку "Запросы".
- В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите команду Изменить.
Редактирование запроса в диалоговом окне "Свойства запроса"
- В Excel выберите вкладку "Данные>& "Запросы данных>", щелкните запрос правой кнопкой мыши и выберите пункт "Свойства", откройте вкладку "Определение" в диалоговом окне "Свойства" и нажмите кнопку "Изменить запрос".
Совет Если вы используете лист с запросом, выберите"Свойстваданных>", откройте вкладку "Определение" в диалоговом окне "Свойства" и выберите команду "Изменить запрос".
Изменение запроса к таблице в модели данных
Модель данных обычно содержит несколько таблиц, упорядоченных в отношении. Запрос загружается в модель данных с помощью команды "Загрузить к" для отображения диалогового окна "Импорт данных" и выбора пункта "Добавить эти данные впроверку режима данных". Дополнительные сведения о моделях данных см. в статьях "Определение источников данных, используемых в модели данных книги", " Создание модели данных в Excel" и "Использование нескольких таблиц для создания сводной таблицы".
Чтобы открыть модель данных, выберите "Управление Power Pivot>".
В нижней части окна Power Pivot щелкните ярлычок листа нужной таблицы.
Убедитесь, что отображается правильная таблица. В модели данных может быть много таблиц.
Обратите внимание на имя таблицы.
Чтобы закрыть окно Power Pivot, нажмите кнопку "Закрыть файл>". Освобождение памяти может занять несколько секунд.
Выберите вкладку "Подключения к данным>" & "Запросы свойств>", щелкните запрос правой кнопкой мыши и выберите команду "Изменить".
Завершив внесение изменений в Редактор Power Query, нажмите "Закрыть файл>" & "Загрузить".
Результат
Запросы на листе и в таблице в модели данных обновляются.
Загрузка запроса в модель данных занимает необычно много времени
Если вы заметили, что загрузка запроса в модель данных занимает гораздо больше времени, чем загрузка на лист, выполните проверку действий Power Query, чтобы узнать, фильтруете ли вы текстовый столбец или столбец со структурой списка с помощью оператора Contains. Это действие приводит к тому, что Excel заново выполняет перечисление по всему набору данных для каждой строки. Кроме того, Excel не может эффективно использовать многопоточное выполнение. В качестве обходного решения попробуйте использовать другой оператор, например Equals или Starts With.
Корпорация Майкрософт знает об этой проблеме и изучает ее причины.
Настройка параметров загрузки запроса
Вы можете загрузить Power Query:
В рабочий лист. В Редактор Power Query выберите «Домашняя> страница»& «Загрузка>»& «Загрузка».
в модель данных. В Редактор Power Query выберите «Домашняя> страница»& «Закрыть загрузку>» & «Загрузить».
По умолчанию при загрузке одного запроса Power Query загружает запросы на новый лист и одновременно загружает несколько запросов в модель данных. Поведение по умолчанию можно изменить для всех книг или только для текущей книги. При настройке этих параметров Power Query не изменяет результаты запроса ни на листе, ни в данных и заметках модели данных.
Можно также динамически переопределять параметры по умолчанию для запроса с помощью диалогового окна "Импорт ", которое открывается после выбора пункта "Закрыть& LoadTo.
Глобальные параметры, применимые ко всем книгам
В редакторе Power Query выберите «Параметры файла>»и «Параметры>запроса».
В диалоговом окне "Параметры запроса " слева в разделе "GLOBAL " выберите "Загрузка данных".
В разделе Параметры загрузки запроса по умолчанию сделайте следующее:
- Выберите "Использовать стандартные параметры загрузки".
- Установите флажок Укажите пользовательские параметры загрузки по умолчанию, а затем установите или снимите флажок Загрузить на лист или Загрузить в модель данных.
Совет В нижней части диалогового окна вы можете выбрать "Восстановить значения по умолчанию", чтобы удобно вернуться к настройкам по умолчанию.
Параметры книги, которые применяются только к текущей книге
В диалоговом окне "Параметры запроса " слева в разделе "Текущая книга" выберите "Загрузка данных".
Выполните одно или несколько из указанных ниже действий.
В разделе "Обнаружение типов" установите или снимите флажок "Определять типы столбцов и заголовков для неструктурированных источников".
Поведение по умолчанию — их обнаружение. Если вы предпочитаете формировать данные самостоятельно, снимите этот флажок.
В разделе "Связи" установите или снимите флажок Создание связей между таблицами при первом добавлении в модель данных.
Перед загрузкой модели данных по умолчанию выполняется поиск существующих связей между таблицами, таких как внешние ключи в реляционной базе данных, и их импорт с данными. Если вы хотите сделать это самостоятельно, снимите этот флажок.В разделе "Отношения" установите или снимите флажок "Обновлять связи при обновлении запросов, загруженных в модель данных".
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами (например, внешние ключи) в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к появлению новых связей. Однако если вы хотите это сделать, выберите параметр.
В разделе "Фоновые данные" установите или снимите флажок "Разрешить предварительное просмотр данных скачивать в фоновом режиме".
По умолчанию функция скачивает предварительный просмотр данных в фоновом режиме. Снимите этот флажок, если хотите сразу увидеть все данные.