Перетворення форми подання даних за допомогою Excel+PowerQuery


У нашому менеджерському працю є багато хорошого і не дуже. Але ніщо не псує нам життя так, як численні эксельки, в яких постійно потрібно щось ВВР-ить, СУМ-ировать, СУММПРОИЗВ-одіть і займатися іншими рутинними операціями, забирають купу часу і сил. Є інструмент, що дозволяє якщо не прибрати, то мінімізувати рутину скрізь, де є патерни даних. Отже, вашій увазі пропонується трохи більш глибоке занурення в принципи роботи PowerQuery.

Відразу ж хочу зорієнтувати з приводу того, що вже є на Хабре по даній темі:

  • https://habrahabr.ru/post/271019/ — Відмінний огляд можливостей PowerQuery для новачків. Дає уявлення про інструменті на прикладах. Якщо ви ніколи не використовували PowerQuery, то почати, напевно, варто з цієї статті. Автор введе в курс справи
  • https://habrahabr.ru/post/311600/ — Занурення в функції мови «M» на прикладі локалізації списку місяців. Автор торкнувся найпотужнішої функції List.Accumulate опис якої заслуговує окремого матеріалу на Хабре. В якості тізера до такої публікації, можу сказати, що List.Accumulate приймає на вхід абсолютно будь-який тип аргументів
  • https://habrahabr.ru/post/274615/ — Короткий огляд можливостей PowerQuery з отримання даних з баз SQL. Для новачків


Що таке Power Query (PQ)
Щоб не повторювати вже сказане в матеріалах, посилання на які наведені вище, буду краток: Power Query — це інструмент для перетворення форми подання будь-яких логічно організованих даних. Тобто на вході може бути будь-яка каша, але в якій є хоч якась логіка. А на виході — таблиця. На думку деяких експертів, PQ є ETL інструментом. Тобто служить для Extract – вилучення, Transform – перетворення і Load – вивантаження даних. З досвіду, дана робота займає до 60% робочого часу, наприклад, менеджера по контекстній рекламі і до 90% часу аналітика. Важлива особливість даної роботи полягає в тому, що її, як правило, потрібно повторювати з дня в день, тиждень за тижнем. Нескінченно переносити дані, копіпаст, обчислювати. Для особливо важких випадків програмісти пишуть рішення, в якому все це робиться автоматично. Проблема полягає в тому, що рішення дороги, а програмісти майже завжди зайняті більш нагальними питаннями, ніж автоматизація рутинних операцій менеджера. PQ дозволяє вирішити зазначені труднощі:

  • Автоматично витягнути дані майже звідки завгодно
  • Перетворити дані відповідно до заздалегідь створеною моделлю, без необхідності заново повторювати одні і ті ж дії вручну
  • Представити отримані дані в тому виді (видах), які необхідні


Відразу ж давайте диференціюємо Power Query і дуже схожий на нього інструмент Power BI. Отже, Power Query служить для перетворення даних, як правило, у зручний нам табличний вигляд, з яким ми далі працюємо в Excel. Power BI призначений для перетворення даних та переведення їх у форму графіків і інші узагальнені уявлення (наприклад, зведені таблиці). Power BI і Power Query використовують один і той же мова «M» для перетворення даних. Тобто код мови «M» може бути майже дослівно перенесено копіпастом з PQ в Power BI і назад. Майже, тому що в PQ можуть бути специфічні конструкції для звернення до книги Excel, або іменованим діапазонам комірок, що, ясна річ, не годиться для Power BI.

На сьогоднішній день Power Query доступний тільки для MS Excel, що працює під ОС Windows. До Excel 2013 року включно, поширювався у вигляді модуля. Починаючи з Excel 2016 став вбудованим.

Методи перетворення даних «кнопками» докладно розібрані в статтях зазначених вище, особливо в першій. Далі стаття буде кілька складну вата для новачків, оскільки ми розбираємо таємницю, а конкретно те, що можна побачити при натисканні на кнопку «Розширений редактор» в редакторі запитів.


Кнопка «Розширений редактор»

Як влаштований мову «M»
Важливо завжди мати на увазі, що конструкція let… in… є просто "syntactic sugar". let за фактом представляє з себе запис з безліччю пар «ім'я=значення», а in це просто посилання на деяке значення в цьому записі (причому не обов'язково останнім). Важкувато. Але давайте спробуємо розібратися. У мові «M» є такі типи значень:

  1. Примітивні типи Null, Logical, Number, Text, Date, Time, DateTime, DateTimeZone, Duration): «рядок», 14, false, #date(2013,3,8) і т. д.
  2. Список (List) – послідовність значень: Джерело = {1,2,3, «hi»}
  3. Запис (Record) – пара (або набір пар) виду: [ a = 1, b = {1,2,3}, c = true]
  4. Таблиця (Table) – конструкція, що включає в себе список колонок і список записів: #table( {«Col1», «Col2»}, { {1, 2}, {3, 4} } )
  5. Функція (Function) – конструкція виду:

    MyFunction = (x, y, optional z) => 
     
    if z = null then x+y 
     
    else (x+y) / z
     
    


  6. Тип (Type) – для класифікування інших значень. Детальніше про це типі див. специфікацію мови «M»
  7. Бінарний (Binary): #binary( {0x68, 0x65, 0x6C} )


Таким чином, наступна конструкція:



Абсолютна ідентична цій:

[X=2,Y=X+3][Y]


Обчислення відбуваються на базі значень, отриманих в результаті попередніх дій. Чимось це нагадує ланцюжок формул Excel:

A1=A2+A3
 
A2=A3+2
 
A3=5
 


У випадку з PQ звернення йде не до них, а до значень. Вирази, які входять до запису або список, обчислюються методом «ледачих» обчислень. Тобто поки ми прямо не пошлемося на значення, що отримується в результаті виконання деякого виразу, воно не буде виконано.

Коротко про секціях
PQ використовує концепцію секцій. Секція – створює область видимості. Це чимось схоже на $scope в Angular. Є внутрішня змінна #sections, яка повертає всі доступні секції. Всі дії, які виконуються в документі, належать до однієї секції: Section1. При цьому неможливо створити ще одну секцію, наприклад, Section 2. Однак, ми можемо звернутися до елементів інших секцій за допомогою змінної #shared. Дана змінна адресує до запису містить усі елементи, які ми можемо бачити створені нами, а також доступні нам типи, константи, функції. Таким чином, нам немає необхідності кожен раз лізти в довідник по мові «M», щоб згадати як називається та чи інша функція. Досить звернутися до змінної #shared. При кліці по пустому полю навколо назви кожної функції, ми можемо бачити її опис внизу екрану.


Значення змінної #shared. Можна подивитися експрес-довідку з вбудованої функції

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

Корисні посилання
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.