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

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

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

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

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

4. The Data Preview that displays the results of the steps applied to the data

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

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

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

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

Процедура

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

2. У діалоговому вікні Навігатор виберіть таблицю Results [Edit] (Результати [Редагувати]) ліворуч, а потім виберіть Transform Data (Перетворити дані) внизу. Відобразиться редактор Power Query.

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

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 для визначення й настроювання джерела   даних.

Відображення або приховання рядка формул

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

• Виберіть подання > макета > рядку формул.

Вихідіз формули в рядку формул

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", яка називається "Замовлення" зі стовпцем "Назва товару", який потрібно перетворити на таблицю з великою регістром. 

До:

Після:

Під час створення розширеного запиту створюється низка кроків-формул запиту на основі виразу let.  Використовуйте вираз let, щоб призначати імена й обчислювати значення, на які посилається речення in, що визначає крок. У цьому прикладі повертається той самий результат, що й у розділі "Створення формули в рядку формул".

let  
    Source = Text.Proper("hello world")
in  
    Source  

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

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

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

2. У редакторі Power Query виберіть основне> Розширений редактор, який відкриється із шаблоном дозволу виразу.

Етап 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, посилаючись на попередній крок-формулу запиту "Перший рядок як заголовок", додайте до джерела даних значення #"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.