Создание, загрузка и изменение запроса в Excel (Power Query)

Power Query предлагает несколько способов создания и загрузки power querys в книгу. Вы также можете настроить параметры загрузки запроса по умолчанию в окне Параметры запроса.    

Совет      Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберите ячейку с данными, а если появится вкладка Лента контекстного запроса, данные загружаются из Power Query. 

Выбор ячейки в запросе для вкладки "Запрос"

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

Знакомые Excel, лента и сетка

Лента редактора Power Query и предварительный просмотр данных 

Стандартный Excel Обычное представление редактора Power Query

Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или в модель данных из Power Query.

Переименование я вкладок на таблицах    Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel "Лист1"и запросом, созданным путем импорта таблицы Excel Таблица1,ее легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

Вы можете создать запрос из импортируемых данных или пустой запрос.

Создание запроса из импортируемых данных

Это самый распространенный способ создания запроса.

  1. Импорт некоторых данных. Дополнительные сведения см. в том, как импортировать данные из внешних источников.

  2. Выберем ячейку в данных и выберите запрос> изменить.

Создание пустого запроса

Вы можете начать с нуля. Это можно сделать двумя способами.

  • Выберите Данные> Получить данные >из других источников > пустой запрос.

  • Выберите Данные> получить данные >запуск редактора Power Query.

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

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

  • Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> "Получить данные" на Excel ленте.

  • Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.

  • Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.

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

Загрузка запроса из редактора Power Query

В редакторе Power Query сделайте следующее:

  • Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузить.

  • Чтобы загрузить данные в модель данных, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузка.

    В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.

Совет   Иногда команда "Загрузить в" неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите запрос> Загрузить в.

Загрузка запроса из области "Запросы и подключения" 

В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.

  1. В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

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

  3. Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

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

Изменение запроса на Excel данных

  • Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выйдите ячейку в данных и выберите запрос > Изменить.

Изменение запроса в области "Запросы & подключения"

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

  1. В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

  2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

Изменение запроса в диалоговом окне "Свойства запроса"

  • В Excel выберите Data> Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.

Совет    Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос. 

Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.

  1. Чтобы открыть модель данных, выберите Power Pivot > Управление.

  2. В нижней части окна Power Pivot выберите вкладку нужной таблицы.

    Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.

  3. Обратите внимание на имя таблицы.

  4. Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.

  5. Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.

  6. Завершив внесение изменений в редакторе Power Query, выберите файл > Закрыть & загрузить.

Результат

Запрос на этом и в таблице в модели данных обновляются.

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

Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.

Вы можете загрузить Power Query:

  • На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load.

  • В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.

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

    Кроме того, динамически переопределять стандартные параметры запроса можно в диалоговом окне Импорт, которое отображается после выбора параметра & Загрузитьв. 

Глобальные параметры, которые применяются во всех книгах

  1. В редакторе Power Query выберите Файл > параметры и параметры >параметры запроса.

  2. В диалоговом окне Параметры запроса в левой части в разделе GLOBAL  выберите Загрузка данных.

  3. В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:

    • Выберите Использовать стандартные параметры загрузки

    • Выберите Указать настраиваемые параметры загрузкипо умолчанию, а затем выберите или отобирать параметр Загрузить на таблицу   или Загрузить в модель данных.

Совет    В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.

Параметры книги, которые применяются только к текущей книге

  1. В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.

  2. Выполните одно или несколько из указанных ниже действий.

    • В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.

      По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.

    • В области Связивыберите или отоберем создать связи между таблицами при первом добавлении в модель данных.

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

    • В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.

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

    • В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.

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

См. также

Справка по Power Query для Excel

Управление запросами в Excel

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×