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

Создание простой формулы

В этом примере мы с помощью формулы Text.Proper() преобразуем текстовое значение, сделав первые буквы слов прописными.

1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.

   

2. В строке формул редактора запросов введите = Text.Proper("text value"), а затем нажмите клавишу ВВОД или щелкните значок "Ввод".

3. Power Query отобразит результаты в области результатов формул.

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

Результат будет выглядеть на листе вот так:

В редакторе запросов можно также создавать сложные формулы.

Создание сложной формулы

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

Предположим, что есть таблица Excel с наименованиями товаров, в которых нужно сделать первые буквы слов прописными.

Исходная таблица выглядит так:

Результирующая таблица должна иметь такой вид:

Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце ProductName прописными.

Пример составления сложного запроса с помощью расширенного редактора

Чтобы очистить исходную таблицу, создайте шаги формулы запроса с помощью расширенного редактора. Создадим каждый шаг формулы запроса, чтобы показать, как можно построить сложный запрос. Все шаги формулы запроса перечислены ниже. При создании сложного запроса сделайте следующее:

• Создайте последовательность шагов формулы запроса, которая начинается с оператора let. Обратите внимание: в языке формул Power Query учитывается регистр.

• Каждый шаг формулы запроса строится на основе одного из предыдущих шагов (ссылается на него по имени).

• Результат шага формулы запроса выводится с помощью оператора in. Обычно последний шаг запроса используется как окончательный результат оператора in для набора данных.

Шаг 1. Открытие расширенного редактора

1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.

2. В редакторе запросов выберите команду Расширенный редактор.

   

3. Откроется окно Расширенный редактор.

   

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

В расширенном редакторе сделайте следующее:

1. Используйте оператор let, определяющий формулу Источник = Excel.CurrentWorkbook(). В результате источником данных станет таблица Excel. Дополнительные сведения о формуле Excel.CurrentWorkbook() см. в статье Excel.CurrentWorkbook.

2. Назначьте имя шага Источник результату оператора in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. Сложный запрос будет выглядеть в расширенном редакторе следующим образом:

   

4. Вот как можно просмотреть результаты на листе:

   1. Нажмите кнопку Готово.

   2. На ленте редактора запросов нажмите кнопку Закрыть и загрузить.

На листе результат будет выглядеть вот так:

Шаг 3. Преобразование первой строки в заголовки

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

1. Добавьте к шагам формулы запроса формулу #"Использовать первую строку в качестве заголовков" = Table.PromoteHeaders() и сошлитесь на "Источник" как на источник данных. Дополнительные сведения о формуле Table.PromoteHeaders() см. в статье Table.PromoteHeaders.

2. Назначьте имя шага #"Использовать первую строку в качестве заголовков" результату оператора in.

   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source)
   in
       #"First Row as Header"

На листе результат будет выглядеть вот так:

Шаг 4. Изменение каждого значения в столбце (замена первых букв в словах прописными)

Чтобы преобразовать каждое значение столбца ProductName, сделав первые буквы слов прописными, воспользуйтесь формулой Table.TransformColumns() и сошлитесь на шаг формулы запроса "Использовать первую строку в качестве заголовков". Для этого воспользуйтесь расширенным редактором.

1. Добавьте к шагам формулы запроса формулу #"Выполнена капитализация каждого слова" = Table.TransformColumns() и сошлитесь на шаг #"Использовать первую строку в качестве заголовков" как на источник данных. Дополнительные сведения о формуле Table.TransformColumns() см. в статье Table.TransformColumns.

2. Назначьте имя шага #"Выполнена капитализация каждого слова" результату оператора in.

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"

В окончательном результате каждое значение столбца ProductName будет изменено (первые буквы слов станут прописными). На листе это будет выглядеть так:

С помощью языка формул Power Query можно создавать простые и сложные запросы для обнаружения, объединения и уточнения данных. Дополнительные сведения о Power Query см. в статье Справка по Microsoft Power Query для Excel.