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

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

1. Стрічка Редактор Power Query, яка використовується для формування даних

2. Область запитів, яка використовується для пошуку джерел даних і таблиць

3. Контекстні меню, які є зручними сполученнями клавіш для команд на стрічці

4. Попередній перегляд даних, у якому відображаються результати кроків, застосованих до даних

5. Область параметрів запиту зі списком властивостей і кожним кроком у запиті

У фоновому режимі кожен крок у запиті базується на формулі, яка відображається в рядку формул.

Іноді може знадобитися змінити або створити формулу. У формулах використовується Power Query мова формул, за допомогою якої можна створювати прості та складні вирази. Докладні відомості про синтаксис, аргументи, примітки, функції та приклади див. в статті мова формул Power Query M.

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

Процедура

1. Щоб імпортувати дані, виберіть пункт Дані > з Інтернету, введіть "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" в полі URL-адреса , а потім натисніть кнопку OK.

2. У діалоговому вікні Навігатор виберіть таблицю Результати [Редагувати] ліворуч, а потім натисніть кнопку Перетворити дані внизу. З'явиться редактор Power Query.

3. Щоб змінити ім'я запиту за замовчуванням, в області Параметри запиту в розділі Властивості видаліть "Результати [Редагувати]", а потім введіть "UEFA champs".

4. Щоб видалити непотрібні стовпці, виділіть перший, четвертий і п'ятий стовпці, а потім виберіть Основне > Видалити стовпець > Видалити інші стовпці.

5. Щоб видалити непотрібні значення, виберіть стовпець1, натисніть кнопку Основне > Замінити значення, введіть "відомості" в полі Значення для пошуку, а потім натисніть кнопку OK.

6. Щоб видалити рядки зі словом "Рік", клацніть стрілку фільтра в стовпці1, зніміть прапорець поруч із пунктом "Рік", а потім натисніть кнопку OK.

7. Щоб перейменувати заголовки стовпців, двічі клацніть кожен із них, а потім змініть значення "Стовпець1" на "Рік", "Стовпець4" на "Переможець" і "Стовпець5" на "Остаточний результат".

8. Щоб зберегти запит, натисніть кнопку Основне > Закрити & Завантажити.

Результат

У таблиці нижче наведено зведення кожного застосованого кроку та відповідної формули.

Крок і завдання запиту	Формула
Джерело Підключення до джерела веб-даних	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Навігація Вибір таблиці	=Source{2}[Data]
Змінений тип Змінення типів даних (які 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. У рядку формул знайдіть і змініть значення параметрів, а потім клацніть піктограму Enter або натисніть клавішу Enter. Наприклад, змініть цю формулу, щоб зберегти стовпець2:
   
   Перед: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Після:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Клацніть піктограму Enter або натисніть клавішу Enter, щоб переглянути нові результати в попередньому перегляді даних.

5. Щоб переглянути результат на аркуші Excel, натисніть кнопку Основне > Закрити & Завантажити.

Створення формули в рядку формул

У прикладі простої формули перетворімо текстове значення на змішаний за допомогою функції Text.Proper.

1. Щоб відкрити пустий запит, у програмі Excel виберіть дані > Отримати > данихз інших джерел > пустий запит. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

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

3. Щоб переглянути результат на аркуші Excel, натисніть кнопку Основне > Закрити & Завантажити.

Результат

 Під час створення формули Power Query перевіряє синтаксис формули. Однак під час вставлення, перевпорядкування або видалення проміжного кроку в запиті може бути потенційно розірвано запит.  Завжди перевіряти результати в поданні попереднього перегляду даних.

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

Редагування формули за допомогою діалогового вікна

Цей метод дає змогу використовувати діалогові вікна, які залежать від кроку. Не потрібно знати синтаксис формули.

1. Щоб відкрити запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних, а потім натисніть кнопку Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

2. В області Параметри запиту в розділі Застосовані кроки виберіть піктограму Змінити настройки на кроці, який потрібно змінити, або клацніть його правою кнопкою миші, а потім виберіть Редагувати настройки.

3. У діалоговому вікні внесіть зміни та натисніть кнопку OK.

Вставлення кроку

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

1. В області Параметри запиту в розділі Застосовані кроки виберіть крок, перед яким потрібно виконати новий крок і відповідну формулу.

2. Клацніть піктограму Додати крок ліворуч від рядка формул. Крім того, клацніть крок правою кнопкою миші та виберіть вставити крок після. Буде створено нову формулу у форматі:
   
   = <nameOfTheStepToReference>, наприклад =Production.WorkOrder.

3. Введіть нову формулу у форматі:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Наприклад, припустімо, що у вас є таблиця зі стовпцем Стать і потрібно додати стовпець зі значенням "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#x4

2. Щоб завантажити запит на аркуш, натисніть кнопку Готово, а потім – Основне > Закрити & Завантажити > Закрити & Завантажити.

Результат

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

1. Щоб відкрити запит, на аркуші виберіть клітинку в даних, а потім натисніть кнопку Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel (Power Query).

2. У Редактор Power Query виберіть елемент Домашня > Розширений редактор, який відкриється з інструкцією, створеною на етапі 2. Визначте джерело даних.

3. У виразі let додайте #"Перший рядок як заголовок" і Table.PromoteHeaders, як показано нижче:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3
   

4. Щоб завантажити запит на аркуш, натисніть кнопку Готово, а потім – Основне > Закрити & Завантажити > Закрити & Завантажити.

Результат

Етап 4. Змінення кожного значення в стовпці на змішаний

1. Щоб відкрити запит, на аркуші виберіть клітинку в даних, а потім натисніть кнопку Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

2. У Редактор Power Query виберіть Елемент Основне > Розширений редактор, який відкриється з інструкцією, створеною на етапі 3: перетворення першого рядка на заголовки.

3. У виразі let перетворіть кожне значення стовпця ProductName на правильний текст за допомогою функції Table.TransformColumns, посилаючись на попередній крок формули запиту "Перший рядок як заголовок", додавши #"Capitalized Each Word" до джерела даних, а потім призначивши результату #"Capitalized Each Word".
   
   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. В області ліворуч у розділі ГЛОБАЛЬНИЙ виберіть Редактор Power Query.

3. В області праворуч у розділі Макет установіть або зніміть прапорець Відображати рядок формул.

Увімкнення або вимкнення функції M Intellisense

1. Виберіть Параметри файлу > та Настройки > Параметри запиту .

2. В області ліворуч у розділі ГЛОБАЛЬНИЙ виберіть Редактор Power Query.

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

Примітка    Змінення цього параметра набуде сили під час наступного відкриття вікна Редактор Power Query.