Создание формул Power Query в Excel

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

1. Лента редактора Power Query, используемая для формировании данных

2. The Queries pane that you use to locate data sources and tables

3. Контекстные меню, удобные для быстрого доступа к командам на ленте

4. Предварительный просмотр данных с результатами действий, примененных к данным

5. В области Параметры запроса со списком свойств и каждого шага запроса

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

Иногда нужно изменить или создать формулу. В формулах используется язык формул Power Query, который можно использовать для создания как простых, так и сложных выражений. Дополнительные сведения о синтаксисе, аргументах, замечаниях, функциях и примерах см. в power Query M formula language.

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

Последовательность действий

1. Чтобы импортировать данные, выберите данные > из Интернета,введите "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" в поле URL-адрес, а затем выберите ОК.

2. В диалоговом окне Навигатор выберите таблицу Результаты [Изменить] слева, а затем выберите Преобразовать данные в нижней части. Появится редактор Power Query.

3. Чтобы изменить имя запроса по умолчанию, в области запроса Параметры в области Свойства удалите "Результаты [Изменить]", а затем введите "ЫК".

4. Чтобы удалить ненужные столбцы, выберите первый, четвертый и пятый столбцы, а затем выберите главная> удалить столбец > удалить другие столбцы.

5. Чтобы удалить ненужные значения, выберите Столбец1, выберите Главная> Заменитьзначения , введите "сведения" в поле Значения для поиска, а затем выберите ОК.

6. Чтобы удалить строки со словом "Год", выберите стрелку фильтра в столбце "Столбец1",снимите его рядом с полем "Год", а затем выберите ОК.

7. Чтобы переименовать столбцы, дважды щелкните каждый из них, а затем измените "Столбец1" на "Год", "Столбец4" на "Winner" и "Столбец5" на "Итоговая оценка".

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

Результат

В следующей таблице содержится сводка каждого примененного шага и соответствующей формулы.

Шаг запроса и задача	Формула
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

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

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

3. В области формул найдите и измените значения параметров, а затем нажмите ввод. Например, измените эту формулу, чтобы сохранить столбец 2:
   
   До: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   после:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

Создание формулы в панели формул

Для простого примера формулы преобразуем текстовое значение в нужный с помощью функции Text.Proper.

1. Чтобы открыть пустой запрос, в Excel выберите Данные> Получить данные > из других источников > Пустой запрос. Дополнительные сведения см. в этойExcel.

2. Введите в формулу=Text.Proper("text value"), а затем нажмите ввод.
   
   Результаты отображаются в режиме предварительного просмотра данных.

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

Результат:

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

Важно    Будьте внимательны при редактировании действий"Источник","Навигация" и "Измененный тип", поскольку они создаются Power Query для определения и создания источника   данных.

Редактирование формулы в диалоговом окне

Этот способ позволяет использовать диалоговое окно, которое зависит от шага. Синтаксис формулы знать не нужно.

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

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

3. В диалоговом окне внести изменения и нажмем ОК.

Вставка шага

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

1. В области Параметры запроса в области Примененныешаги выберите шаг, который должен предшествовать новому шагу и соответствующей формуле.

2. Выберите значок Добавить слева от панели формул. Можно также щелкнуть правой кнопкой мыши шаг и выбрать Вставить шаг после.Новая формула будет создана в формате := <nameOfTheStepToReference>
   
   , например =Production.WorkOrder.

3. Введите новую формулу в формате:
   
   =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. Открытие расширенных редакторов

1. В Excel выберите Данные> Получить данные > другие источники > пустой запрос. Дополнительные сведения см. в этойExcel.

2. В редакторе Power Query выберите главная> Расширенный редактор, который откроется с шаблоном выражения let.

Этап 2. Определение источника данных

1. Создайте выражение let с помощью Excel. Функция CurrentWorkbook:let
   
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   
   in
       Source
   
   

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

Результат:

Этап 3. Продвижение первой строки до заглавных

1. Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в статьи Создание, загрузка и изменение запроса в Excel (Power Query).

2. В редакторе Power Query выберите Главная > Расширенный редактор, который откроется с помощью выписки, созданной на этапе 2:Определение источника данных.

3. В выражении 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. Чтобы загрузить запрос на таблицу, выберите Готово ,а затем выберите Главная> Закрыть & Загрузка > Закрыть & Загрузить.

Результат:

Этап 4. Изменение каждого значения в столбце на правильное

1. Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в этойExcel.

2. В редакторе Power Query выберите Главная> Расширенный редактор, который откроется с заявлением, созданным на этапе 3: Повысить первую строку до заглавных.

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"

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

Результат:

Вы можете управлять поведением панели формул в редакторе Power Query для всех книг.

Отображение или скрытие панели формул

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

2. В левой области в области GLOBALвыберите Редактор Power Query.

3. В области справа в области Макет выберитеили отобразить формулу.

Включить или отключить M Intellisense

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

2. В левой области в области GLOBALвыберите Редактор Power Query.

3. В области справа в области Формула выберитеили отберите включить M Intellisenseв панели формул, в расширенный редактор и в диалоговом окке настраиваемого столбца .

Примечание    Изменение этого параметра вступает в силу при следующем переходе в окно редактора Power Query.