Создание, загрузка и изменение запроса в 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 для их обработки. Это происходит только при загрузке данных на таблицу или модель данных из Power Query.
Вы можете создать запрос из импортируемых данных или пустой запрос.
Создание запроса из импортируемых данных
Это самый распространенный способ создания запроса.
Выберем ячейку в данных и выберите запрос> Изменить.
Создание пустого запроса
Вы можете начать с нуля. Это можно сделать двумя способами.
Выберите Данные> Получить данные > из других источников > пустой запрос.
Выберите Данные> получить данные > запустить редактор Power Query.
На этом этапе вы можете вручную добавлять шаги и формулы, если язык формул Power Query M хорошо разный.
Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.
Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> «Получить данные» на Excel ленте.
Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.
Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.
Предположим, что запрос действителен и не имеет ошибок, его можно загрузить обратно на таблицу или в модель данных.
Загрузка запроса из редактора Power Query
В редакторе Power Query сделайте следующее:
Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & загрузить.
Чтобы загрузить данные в модель данных, выберите Главная > закрыть & Загрузить > Закрыть & Загрузить в.
В диалоговом окне Импорт данных выберите добавить эти данные в модель данных.
Загрузка запроса из области «Запросы и подключения»
В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).
Существует несколько способов редактирования запроса, загруженного на таблицу.
Изменение запроса на Excel данных
Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить.
Изменение запроса в области «Запросы & подключения»
Возможно, вы & области «Запросы и подключения» удобнее всего использовать, если в одной книге много запросов и вы хотите быстро найти их.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.
Изменение запроса в диалоговом окне «Свойства запроса»
Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.
Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.
Чтобы открыть модель данных, выберите Power Pivot > Управление.
В нижней части окна Power Pivot выберите вкладку нужной таблицы.
Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.
Обратите внимание на имя таблицы.
Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.
Выберите > подключения & свойства > Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.
Завершив внесение изменений в редакторе 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 не меняет результаты запроса на месте или в данных модели данных и примечаниях.
Вы также можете динамически переопределять стандартные параметры запроса с помощью диалогового окна Импорт, которое отображается после выбора параметра Закрыть & Загрузка в.
Глобальные параметры, которые применяются во всех книгах
В редакторе Power Query выберите Параметры > параметры и параметры > запроса.
В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
В разделе Загрузка запроса Параметры по умолчанию сделайте следующее:
Выберите Использовать стандартные параметры загрузки.
Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.
Параметры книги, которые применяются только к текущей книге
В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.
Выполните одно или несколько из указанных ниже действий:
В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.
По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.
В области Связивыберите или сберем создать связи между таблицами при первом добавлении в модель данных.
По умолчанию перед загрузкой в модель данных можно найти существующие связи между таблицами, например внешние ключи в реляционной базе данных, и импортировать их вместе с данными. Если вы предпочитаете делать это самостоятельно, сделайте это самостоятельно.
В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.
В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.
По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.
См. также
Excel для Интернета включает в себя технологию Power Query (также называемую функцией & преобразования), которая обеспечивает больше возможностей при работе с импортируемыми источниками данных. Вы можете просматривать запросы и связанные с ними сведения в области задач Запросы.
Примечание: Следите за дальнейшими объявлениями об улучшениях Excel интеграции с Веб-сайтом и Power Query.
Просмотр области задач «Запросы»
Убедитесь, что вы в режиме правки (выберите Просмотр> редактирование).
Выберите Запросы > данных.
Excel отображает области задач Запросы и все запросы в текущей книге, а также сведения о запросе, такие как количество строк, дата последнего обновления, расположение и состояние загрузки. Если запрос загружается в модель данных, а не в сетку, Excel отображается «Только подключение».
Управление запросами (Power Query)
Управлять запросами можно в области «Запросы» редактора Power Query или на вкладке Запросы в области «Запросы & подключения» Excel. Это особенно полезно, если у вас много запросов и вы хотите быстро найти их. После того как вы найдете запрос, можно сделать ряд дополнительных действий, таких как дублирование, ссылка, слияние, приложение, совместное действие и удаление запроса.
При создании запроса сведения о подмыве создаются за кадром. Роль запроса заключается в формировании данных. Роль сведений о под соединении заключается в сохранении сведений об источнике данных и их обновлении в соответствии с настройками обновления, такими как частота.
Ниже перечислены команды, которые можно использовать в том порядке, в котором они отображаются в соответствующих контекстных меню на вкладке Запросы в области «Запросы & Подключение» в Excel или в области Запросы в редакторе Power Query. Некоторые команды доступны только в одной области. Многие из этих команд также доступны на контекстной вкладке Запрос на ленте. Все запросы в книге отображаются и сортироваться по дате и времени последнего изменения, начиная с последнего.
Сделайте одно из следующего: В Excel Выберите данные> запросы &
В редакторе Power Query Выберите данные > получить данные > запустить редактор Power Queryи просмотреть области Запросы слева.
Выберите команду управления запросами: Изменение запроса
в редакторе Power Query. Доступно только на вкладке Запросы в области & подключений.
Удалить Удаляет запрос.
Переименование Изменяет имя запроса.
Обновление Позволяет получить данные из внешних источников. Доступно только на вкладке Запросы в области & подключений. Дополнительные сведения см. в этойExcel.
Загрузить в Выводит диалоговое окно Импорт данных, в котором можно выбрать, как вы хотите просмотреть данные, куда их поместить, и добавить их в модель данных. Доступно только на вкладке Запросы в области & подключений.
Дублирование Создает копию выбранного запроса с тем же именем, что и у исходного запроса (2). Вы можете переименовать запрос, чтобы его было проще идентифицировать. Последующие изменения исходного запроса не влияют на новый запрос.
Справка Создает новый запрос, который использует шаги предыдущего запроса, не повторяя его. Новый запрос будет следовать тому же соглашению об именовании, что и команда Дублировать. Вы можете переименовать новый запрос, чтобы его было проще идентифицировать. Последующие изменения исходного запроса, которые приводят к изменению выходных данных, влияют на новый запрос.
Слияние Объединение столбцов в запросе с совпадающих столбцов в других запросах. Дополнительные сведения о слиянии см. в этой теме. Доступно только на вкладке Запросы в области & подключений.
«Приложение» В запрос можно также вовсю вть столбцы. Дополнительные сведения о приложении см. в этой теме. Доступно только на вкладке Запросы в области & подключений.
Переместить в группу Перемещает запрос в группу в списке или, если групп нет, выберите Создать группу, чтобы создать ее. Придай ему осмысленное имя, например Бюджет или Маркетинг. Группы используются для у упорядотки множества запросов по категориям. Вы даже можете создавать группы в группах.
Вверх Перемещает запрос вверх в списке запросов.
Переместить вниз Перемещает запрос вниз в списке запросов.
Функция Create (Создать функцию) выводит диалоговое окно Создание функции. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в справке по функции Power Query M.
Преобразовать в параметр Преобразует запрос в параметр и отображает его в области Предварительный просмотр данных. Если текущий дисплей является параметром, команда перенастроит параметр Преобразовать в запрос. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в странице Создание запроса с параметрами (Power Query).
Расширенный редактор Открывает окно Расширенный редактор. Дополнительные сведения см. в этойExcel. Доступно только в области Запросы редактора Power Query.
Экспорт файла подключения Сохраняет запрос в качестве ODC-файла подключения. ODC-файл содержит определение запроса, сведения об подключениях к источнику данных и все этапы преобразования. Это полезно, если вы хотите поделиться запросами с другими пользователями и книгами. Кроме того, можно воспользоваться командой Свойства на контекстной вкладке Запрос ленты Power Query. Дополнительные сведения см. в статьи Создание, изменение и управление подключениями к внешним данным. Доступно только на вкладке Запросы в области & подключений.
Показать обзор Выводит вылет запроса с предварительным просмотром данных и другими полезными сведениями. Доступно только на вкладке Запросы в области & подключений.
Свойства Эта команда используется для управления запросом и сведениями об источниках данных. На вкладке Запросы в области & подключенияоткрывается диалоговое окно Свойства запроса. На области Запросы в редакторе Power Query откроется Параметры запроса.
Вы можете включить или отключить отображение вылетного запроса при наведении курсором мыши на имя запроса на вкладке Запрос в области Запросы & подключения. Этот параметр управляет отображением вылетов только при наведении курсором. Вы по-прежнему можете отобразить вылет запроса, щелкнув правой кнопкой мыши имя запроса и выбрав показать обзор.
Выберите Параметры запроса> получить > данных.
Слева в области GLOBALвыберите Общие.
Справа в области Предварительный просмотр запроса сведите с нее поле Показать обзор при наведении курсоров. По умолчанию при наведении курсором на экран отображается вылет.
Создание формул Power Query в Excel
Просто с помощью редактора Power Query вы создавали формулы Power Query. Давайте посмотрим, как работает Power Query, посмотрев на него. Чтобы узнать, как обновлять или добавлять формулы, просто посмотрите редактор Power Query в действии. Вы даже можете скатить собственные формулы с помощью расширенных редакторов.
Редактор Power Query предоставляет запросы и возможности формирования данных для Excel, которые можно использовать для повторного формирования данных из различных источников. Чтобы отобразить окно редактора Power Query,импортировать данные из внешних источников на Excel, выбрать ячейку в данных, а затем выбрать запрос > Изменить. Ниже следующую сводку основных компонентов.
Лента редактора Power Query, используемая для формировании данных
The Queries pane that you use to locate data sources and tables
Контекстные меню, удобные для быстрого доступа к командам на ленте
Предварительный просмотр данных с результатами действий, примененных к данным
В области Параметры запроса со списком свойств и каждого шага запроса
При этом каждый шаг запроса основан на формуле, которая отображается в панели формул.
Иногда нужно изменить или создать формулу. В формулах используется язык формул Power Query, который можно использовать для создания как простых, так и сложных выражений. Дополнительные сведения о синтаксисе, аргументах, замечаниях, функциях и примерах см. в power Query M formula language.
Используя в качестве примера список футбольного мяча, используйте Power Query, чтобы получить необработанные данные, найденные на веб-сайте, и превратить их в хорошо отформатированную таблицу. Посмотрите, как создаются шаги запроса и соответствующие формулы для каждой задачи в области запроса Параметры в области Примененные шаги и в области формул.
Чтобы импортировать данные, выберите данные > из Интернета,введите «http://en.wikipedia.org/wiki/UEFA_European_Football_Championship» в поле URL-адрес, а затем выберите ОК.
В диалоговом окне Навигатор выберите таблицу Результаты [Изменить] слева, а затем выберите Преобразовать данные в нижней части. Появится редактор Power Query.
Чтобы изменить имя запроса по умолчанию, в области запроса Параметры в области Свойства удалите «Результаты [Изменить]», а затем введите «ЫК».
Чтобы удалить ненужные столбцы, выберите первый, четвертый и пятый столбцы, а затем выберите главная> удалить столбец > удалить другие столбцы.
Чтобы удалить строки со словом «Год», выберите стрелку фильтра в столбце «Столбец1»,снимите его рядом с полем «Год», а затем выберите ОК.
Чтобы переименовать столбцы, дважды щелкните каждый из них, а затем измените «Столбец1» на «Год», «Столбец4» на «Winner» и «Столбец5» на «Итоговая оценка».
Чтобы сохранить запрос, выберите главная > Закрыть & загрузить.
В следующей таблице содержится сводка каждого примененного шага и соответствующей формулы.
Шаг запроса и задача
Подключение к веб-источнику данных
Изменение типов данных (которые Power Query делает автоматически)
Удалены другие столбцы
Удаление ненужных столбцов
Замена значений для очистки значений в выбранном столбце
= Table.ReplaceValue(#»Removed Other Columns»,»Details»,»»,Replacer.ReplaceText,<"Column1">)
Фильтрация значений в столбце
= Table.SelectRows(#»Replaced Value», each ([Column1] <> «Year»))
Измененные заглавные столбцы должны быть осмысленными
Важно Будьте внимательны при редактировании действий«Источник»,»Навигация» и «Измененный тип», поскольку они создаются Power Query для определения и создания источника данных.
Показ или скрытие панели формул
По умолчанию отображается формула, но если она не видна, ее можно отыгрывать.
Выберите Просмотр > макета > формул.
E dit a formula in the formula bar
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
В области Параметры запроса в области Примененныешаги выберите шаг, который вы хотите изменить.
В области формул найдите и измените значения параметров, а затем нажмите 
ввод. Например, измените эту формулу, чтобы сохранить столбец 2:
Нажмите значок или нажмите ввод, чтобы увидеть новые результаты в режиме предварительного просмотра данных.
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
Создание формулы в панели формул
Для простого примера формулы преобразуем текстовое значение в нужный с помощью функции Text.Proper.
Чтобы открыть пустой запрос, в Excel выберите Данные> Получить данные > из других источников > Пустой запрос. Дополнительные сведения см. в этойExcel.
Результаты отображаются в режиме предварительного просмотра данных.
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
При создании формулы Power Query проверяет ее синтаксис. Однако при вставке, переусортовке или удалении промежуточного шага запроса это может привести к разрыву запроса. Всегда проверять результаты в режиме предварительного просмотра данных.
Важно Будьте внимательны при редактировании действий«Источник»,»Навигация» и «Измененный тип», поскольку они создаются Power Query для определения и создания источника данных.
Редактирование формулы в диалоговом окне
Этот способ позволяет использовать диалоговое окно, которое зависит от шага. Синтаксис формулы знать не нужно.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
В диалоговом окне внести изменения и нажмем ОК.
После выполнения шага запроса, в который были добавлены данные, шаг запроса добавляется под текущим этапом запроса. но при вставке шага запроса в середине шагов может возникнуть ошибка в последующих действиях. При попытке вставить новый шаг При попытке вставить новый шаг в Power Query отображается предупреждение Вставка шага, при этом изменяются поля, например имена столбцов, которые используются на всех шагах, которые следуют за ним.
В области Параметры запроса в области Примененныешаги выберите шаг, который должен предшествовать новому шагу и соответствующей формуле.
Выберите значок Добавить 
слева от панели формул. Можно также щелкнуть правой кнопкой мыши шаг и выбрать Вставить шаг после.Новая формула будет создана в формате : =
Введите новую формулу в формате:
Например, предположим, что у вас есть таблица со столбцом Gender и вы хотите добавить столбец со значением «Ms». или «г-н», в зависимости от пола человека. Формула будет:
В области Запросы Параметры в области Примененныешаги щелкните правой кнопкой мыши шаг, а затем выберите Вверх или Вниз.
Щелкните значок 
слева от шага или щелкните его правой кнопкой мыши и выберите удалить или Удалить до конца. Значок удаления также доступен слева от панели формул.
В этом примере мы преобразуем текст в столбце в нужный с помощью сочетания формул в расширенный редактор.
Например, у вас есть Excel «Заказы» со столбцом ProductName, который нужно преобразовать в нужный пример.
При создании расширенных запросов создается ряд шагов формулы запроса на основе выражения let. Используйте выражение let для назначения имен и вычисления значений, на которые затем ссылается предложение in, которое определяет шаг. Этот пример возвращает тот же результат, что и в разделе «Создание формулы в панели формул».
let
Source = Text.Proper(«hello world»)
in
Source
Вы увидите, что каждый шаг строится на основе предыдущего шага, ссылаясь на шаг по имени. Напоминаем, что в языке формул Power Query с чувствительностью к делу.
Этап 1. Открытие расширенных редакторов
В Excel выберите Данные> Получить данные > другие источники > пустой запрос. Дополнительные сведения см. в этойExcel.
В редакторе Power Query выберите главная> Расширенный редактор, который откроется с шаблоном выражения let.
Этап 2. Определение источника данных
Создайте выражение let с помощью Excel. Функция CurrentWorkbook: let
Этап 3. Продвижение первой строки до заглавных
Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в статьи Создание, загрузка и изменение запроса в Excel (Power Query).
В редакторе Power Query выберите Главная > Расширенный редактор, который откроется с помощью выписки, созданной на этапе 2:Определение источника данных.
В выражении let добавьте #»First Row as Header» и Table.PromoteHeaders следующим
образом:
let
Source = Excel.CurrentWorkbook()<[Name="Orders"]>[Content], #»First Row as Header» = Table.PromoteHeaders(Source) #x3
#»First Row as Header»
Этап 4. Изменение каждого значения в столбце на правильное
Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в этойExcel.
В редакторе Power Query выберите Главная> Расширенный редактор, который откроется с заявлением, созданным на этапе 3: Повысить первую строку до заглавных.
В выражении let преобразуйте каждое значение столбца ProductName в правильный текст с помощью функции Table.TransformColumns, ссылаясь на предыдущий шаг формулы запроса «Первая строка в качестве заглавного текста», добавив к источнику данных значение #»Заглавная буква каждого слова», а затем назначив результат «#»Преобразование каждого слова» в заглавную букву.
let
Source = Excel.CurrentWorkbook()<[Name="Orders"]>[Content],
#»First Row as Header» = Table.PromoteHeaders(Source),
#»Capitalized Each Word» = Table.TransformColumns(#»First Row as Header»,<<"ProductName", Text.Proper>>)
in
#»Capitalized Each Word»
Вы можете управлять поведением панели формул в редакторе Power Query для всех книг.
Отображение или скрытие панели формул
Выберите Параметры> файла и Параметры > параметры запроса.
В левой области в области GLOBALвыберите Редактор Power Query.
В области справа в области Макет выберитеили отобразить формулу.
Включить или отключить M Intellisense
В левой области в области GLOBALвыберите Редактор Power Query.
Примечание Изменение этого параметра вступает в силу при следующем переходе в окно редактора Power Query.
