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

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

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

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

Переименование вкладок листа    Рекомендуется осмысленно переименовать вкладки листа, особенно если их много. Особенно важно прояснить разницу между листом данных и листом, загруженным из редактора Power Query. Даже если у вас есть только два листа, один из которых содержит таблицу Excel с именем Sheet1, а другой — запрос, созданный путем импорта таблицы Excel с именем Table1, легко запутаться. Рекомендуется всегда изменять имена вкладок по умолчанию на имена, которые вам нужны. Например, переименуйте Лист1 в DataTable , а Table1 — в QueryTable. Теперь ясно, на какой вкладке есть данные, а на какой — запрос.

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

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

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

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

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

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

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

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

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

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

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

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

• Выберите Последние источники, чтобы выбрать источник данных, с которым вы работали. Эта команда похожа на команду Data > Recent Sources на ленте Excel.

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

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

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

В редакторе Power Query выполните одно из следующих действий.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. В нижней части окна Power Pivot выберите вкладку листа нужной таблицы.
   
   Убедитесь, что отображается правильная таблица. Модель данных может содержать много таблиц.

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

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

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

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

Result (Результат)

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

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

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

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

• На лист. В редакторе Power Query выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить.

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

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

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

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

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

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

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

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

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

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

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

   • В разделе Обнаружение типов выберите или снимите флажок Обнаружение типов столбцов и заголовков для неструктурированных источников.
     
     Поведение по умолчанию — их обнаружение. Снимите этот параметр, если вы предпочитаете самостоятельно формировать данные.

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

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

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

   • В разделе Фоновые данные выберите или снимите флажок Разрешить предварительную загрузку данных в фоновом режиме.
     
     Поведение по умолчанию — скачивание предварительных просмотров данных в фоновом режиме. Снимите этот параметр, если вы хотите сразу просмотреть все данные.