Створення формул Power Query в програмі Excel

Створення простої формули

Для прикладу простої формули перетворімо текстове значення на текст, у якому всі слова починаються з великої букви, використовуючи формулу Text.Proper() .

1. Виберіть на стрічці на вкладку POWER QUERY, натисніть кнопку З інших джерел і виберіть пункт Пустий запит.

   

2. У рядку формул редактора запитів введіть = Text.Proper("text value") і натисніть клавішу Enter або виберіть піктограму "Ввести".

3. У Power Query в області результатів формул відобразяться результати.

4. Щоб побачити результат на аркуші Excel, натисніть кнопку Закрити й завантажити.

На аркуші результат матиме такий вигляд:

У редакторі запитів можна також створювати розширені формули запитів.

Створення розширеної формули

Для прикладу розширеної формули перетворімо текст у стовпці на текст, у якому всі слова починаються з великої букви, використовуючи комбінацію формул. Мова формул Power Query дає змогу об’єднати кілька формул у кроки запиту, результатом яких буде набір даних. Результат можна імпортувати до аркуша Excel.

Припустімо, наприклад, що таблицю Excel із назвами продуктів потрібно перетворити на таблицю, у якій усі слова починаються з великої букви.

Вихідна таблиця має такий вигляд:

Потрібно, щоб вона мала такий вигляд:

Створімо кроки-формули запиту, щоб змінити значення в стовпці ProductName вихідної таблиці на значення, у яких усі слова починаються з великої букви.

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

Щоб очистити вихідну таблицю, потрібно створити кроки-формули запиту, використовуючи розширений редактор. Створімо кожен крок-формулу запиту, щоб побачити, як створюється розширений запит. Повний набір кроків-формул запиту наведено нижче. Щоб створити розширений запит, потрібно дотримуватися цієї процедури:

• Створити послідовність кроків-формул запиту, які починаються з інструкції let. Зверніть увагу, що в мові формул Power Query враховується регістр.

• Створити наступні кроки-формули запиту, кожен із яких базується попередньому кроці й посилається на нього за іменем.

• Вивести результат кроку-формули запиту, використовуючи інструкцію in. Зазвичай результат виконання останнього кроку запиту використовується як остаточний набір даних.

Крок 1. Відкриття розширеного редактора

1. Виберіть на стрічці на вкладку POWER QUERY, натисніть кнопку З інших джерел і виберіть пункт Пустий запит.

2. У редакторі запитів натисніть кнопку Розширений редактор.

   

3. Відкриється розширений редактор.

   

Крок 2. Визначення вихідного джерела

У розширеному редакторі:

1. Використовуйте оператор let, який призначає формулу Source = Excel.CurrentWorkbook(). Як джерело даних використовуватиметься таблиця Excel. Докладні відомості про формулу Excel.CurrentWorkbook() див. в статті Excel.CurrentWorkbook.

2. Призначте Source результату інструкції in.

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

3. Розширений запит у розширеному редакторі виглядатиме так:

   

4. Щоб переглянути результати на аркуші:

   1. Натисніть кнопку Готово.

   2. На стрічці редактора запитів натисніть кнопку Закрити й завантажити.

Результат на аркуші виглядатиме так:

Крок 3. Перетворення першого рядка на рядок заголовків

Щоб перетворити значення в стовпці ProductName на текст, у якому всі слова починаються з великої букви, спочатку потрібно зробити перший рядок заголовками стовпців. Для цього потрібно скористатися розширеним редактором:

1. Додайте формулу #"Перший рядок як заголовок" = Table.PromoteHeaders() до кроків-формул запиту та перейдіть до джерела даних "Джерело". Докладні відомості про формулу Table.PromoteHeaders() див. в статті Table.PromoteHeaders.

2. Призначте #"First Row as Header" результату інструкції in.

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

Результат на аркуші виглядатиме так:

Крок 4. Перетворення всіх слів у кожному значенні на слова з великої букви

Щоб перетворити кожне значення в стовпці ProductName на текст, у якому всі слова починаються з великої букви, використайте функцію Table.TransformColumns() і пошліться на крок-формулу запиту "First Row as Header". Для цього потрібно скористатися розширеним редактором:

1. Додайте формулу #"Кожна Word з великої букви" = Table.TransformColumns() до кроків-формул запиту та введіть #"Перший рядок як заголовок" як джерело даних. Докладні відомості про формулу Table.TransformColumns() див. в статті Table.TransformColumns.

2. Призначте #"Capitalized Each Word" результату інструкції 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.