Просто с помощью редактора 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",снимите его рядом с полем "Год", а затем выберите ОК.
-
Чтобы переименовать столбцы, дважды щелкните каждый из них, а затем измените "Столбец1" на "Год", "Столбец4" на "Winner" и "Столбец5" на "Итоговая оценка".
-
Чтобы сохранить запрос, выберите главная >Закрыть & загрузить.
Результат
В следующей таблице содержится сводка каждого примененного шага и соответствующей формулы.
Шаг запроса и задача |
Формула |
---|---|
Source Подключение к веб-источнику данных |
= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship")) |
Navigation Выбор таблицы |
=Source{2}[Data] |
Changed Type Изменение типов данных (которые Power Query делает автоматически) |
= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}) |
Удалены другие столбцы Удаление ненужных столбцов |
= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"}) |
Заменено значение Замена значений для очистки значений в выбранном столбце |
= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"}) |
Отфильтрованные строки Фильтрация значений в столбце |
= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year")) |
Переименованные столбцы Измененные заглавные столбцы должны быть осмысленными |
= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}}) |
Важно Будьте внимательны при редактировании действий"Источник","Навигация" и "Измененный тип", поскольку они создаются Power Query для определения и создания источника данных.
Показ или скрытие панели формул
По умолчанию отображается формула, но если она не видна, ее можно отыгрывать.
-
Выберите Просмотр >макета > формул.
Edit a formula in the formula bar
-
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
-
В области Параметры запроса в области Примененныешаги выберите шаг, который вы хотите изменить.
-
В области формул найдите и измените значения параметров, а затем нажмите
ввод. Например, измените эту формулу, чтобы сохранить столбец 2:
До: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
после:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"}) -
Нажмите значок
или нажмите ввод, чтобы увидеть новые результаты в режиме предварительного просмотра данных.
-
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
Создание формулы в панели формул
Для простого примера формулы преобразуем текстовое значение в нужный с помощью функции Text.Proper.
-
Чтобы открыть пустой запрос, в Excel выберите Данные> Получить данные > из других источников > Пустой запрос. Дополнительные сведения см. в этойExcel.
-
Введите в формулу=Text.Proper("text value"), а затем нажмите
ввод.
Результаты отображаются в режиме предварительного просмотра данных. -
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
Результат:
При создании формулы Power Query проверяет ее синтаксис. Однако при вставке, переусортовке или удалении промежуточного шага запроса это может привести к разрыву запроса. Всегда проверять результаты в режиме предварительного просмотра данных.
Важно Будьте внимательны при редактировании действий"Источник","Навигация" и "Измененный тип", поскольку они создаются Power Query для определения и создания источника данных.
Редактирование формулы в диалоговом окне
Этот способ позволяет использовать диалоговое окно, которое зависит от шага. Синтаксис формулы знать не нужно.
-
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
-
В области Параметры запроса в области Примененные шаги щелкните значок Изменить Параметры
действия, который вы хотите изменить, или щелкните его правой кнопкой мыши и выберите изменить Параметры .
-
В диалоговом окне внести изменения и нажмем ОК.
Вставка шага
После выполнения шага запроса, в который были добавлены данные, шаг запроса добавляется под текущим этапом запроса. но при вставке шага запроса в середине шагов может возникнуть ошибка в последующих действиях. При попытке вставить новый шаг При попытке вставить новый шаг в Power Query отображается предупреждение Вставка шага, при этом изменяются поля, например имена столбцов, которые используются на всех шагах, которые следуют за ним.
-
В области Параметры запроса в области Примененныешаги выберите шаг, который должен предшествовать новому шагу и соответствующей формуле.
-
Выберите значок Добавить
слева от панели формул. Можно также щелкнуть правой кнопкой мыши шаг и выбрать Вставить шаг после.Новая формула будет создана в формате := <nameOfTheStepToReference>
, например =Production.WorkOrder. -
Введите новую формулу в формате:
=Class.Function(ReferenceStep[,otherparameters])
Например, предположим, что у вас есть таблица со столбцом Gender и вы хотите добавить столбец со значением "Ms". или "г-н", в зависимости от пола человека. Формула будет:
=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")
Переусортовка шага
-
В области Запросы Параметры в области Примененныешаги щелкните правой кнопкой мыши шаг, а затем выберите Вверх илиВниз.
Удаление шага
-
Щелкните значок
слева от шага или щелкните его правой кнопкой мыши и выберите удалить или Удалить до конца. Значок
удаления также доступен слева от панели формул.
В этом примере мы преобразуем текст в столбце в нужный с помощью сочетания формул в расширенный редактор.
Например, у вас есть 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
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
in
Source
-
Чтобы загрузить запрос на таблицу, выберите Готово ,а затем выберите Главная> Закрыть & Загрузка > Закрыть & Загрузить.
Результат:

Этап 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.
-
В области справа в области Формула выберитеили отберите включить M Intellisenseв панели формул, в расширенный редактор и в диалоговом окке настраиваемого столбца .
Примечание Изменение этого параметра вступает в силу при следующем переходе в окно редактора Power Query.
См. также
Справка по Power Query для Excel
Создание и вызов настраиваемой функции
Использование списка Примененные шаги (docs.com)
Использование пользовательских функций (docs.com)