Создание, загрузка и изменение запроса в 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 неактивна или отключена. Это может произойти при первом создании запроса в книге. В этом случае выберите Закрыть & загрузить, на новом листе выберите >запросы & Connections > вкладку Запросы, щелкните запрос правой кнопкой мыши и выберите команду Загрузить в. Кроме того, на ленте Редактор Power Query выберите Запрос > загрузить.

Загрузка запроса из области "Запросы и Connections" 

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

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

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

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

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

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

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

Изменение запроса на панели "Запросы" & Connections

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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