Power Query: як перестати боятися функціонального програмування і почати працювати в розширеному редакторі запитів

Не так давно натрапив на статтю на Хабре про те, як користуватися Power BI і як проводити з допомогою нього Простий план-фактний аналіз. Автору величезний респект за працю — матеріал справді корисний. Більш ніж достатньо для початківця. Однак, наскільки я зрозумів, для багатьох робота з PQ/PBI так і закінчується натисненням на кнопочки в інтерфейсі.

В принципі, більшості користувачів цього цілком достатньо для рішення нескладних задач. Тим більше, що це саме більшість, що називається, в побуті — непрограммистывообщениразу. Та й, як показала практика, далеко не всі знають, що в PQ є режим розширеного редагування запитів. А між тим, боязнь (небажання/невміння) копнути глибше позбавляє можливості задіяти весь закладений функціонал PQ/PBI в повній мірі. Зазначу хоча б той факт, що в інтерфейсі присутні далеко не всі кнопочки, для яких є функції. Думаю, не сильно помилюся, якщо скажу, що функцій, мабуть, рази в два більше, ніж кнопок.

Якщо ж ви відчуваєте, що для вирішення наявних завдань вам недостатньо відведеного в інтерфейсі функціоналу та/або є час задовольнити академічний інтерес, ласкаво просимо під кат…



Преамбула
Гортаючи статтю, я натрапив на фрагмент, де автор пропонує створити таблицю відповідності місяця року його порядковим номером, посилаючись на те, що «мова «М» не дозволяє на поточний момент конвертувати назви місяців до дати».


В принципі, задача елементарна. Але подивившись на те, яким запитом вона була реалізована, зрозумів, що просто так пройти повз я вже не зможу.

let
Source={"січень", "лютий", "березень", "квітень", "травень", "червень", "липень", "серпень", "вересень", "жовтень", "листопад", "грудень"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
#"Added to Column" = Table.TransformColumns(#"Added Index", {{"Index", each List.Sum({_, 1})}}),
#"Renamed Columns" = Table.RenameColumns(#"Added to Column",{{"Column1", "Місяць"}})
in
#"Renamed Columns"

Що робить автор, якщо переводити код на російську мову

  1. Створює вручну список назв місяців
  2. Перетворює список в таблицю
  3. Додає стовпець з індексом, починаючи з нуля, з кроком в одиницю
  4. Трансформує стовпець «Index», додаючи одиницю до кожної комірки
  5. Перейменовує дефолтний ім'я стовпця «Column1» в «Місяць»
Мені стало цікаво, а як ще можна було б вирішити цю задачку, задавши наступні орієнтири:

  • мінімум строчок коду за рахунок використання заставленого функціоналу (наприклад, не перейменовувати стовпець, а задати відразу, не инкрементировать стовпець, а почати з одиниці і т. д.)
  • відмова від ручного введення (легко можна опечататься, набираючи вручну)
  • назви місяців з урахуванням локалі
У підсумку просто інтерес вилився в цілу добірку фрагментів коду і бажання поділитися цим з іншими.

Критика
Дисклеймер. Відразу обмовлюся, претензій до автора і до його підходам складати скрипти у мене немає (можливо, скрипти писалися на швидку руку). Мета даної статті — показати початківцям різні підходи при складанні сценаріїв.

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


Отже, що мене відразу вооззбентежило:

Список місяців набирається вручну
По-перше, як вже було зазначено вище, набираючи текст, можна легко помилитися, по-друге, всі ці лапки/коми… Ну їх — тільки плутанина. Тому, замість того, щоб створювати список, перераховуючи місяці, я б запропонував використовувати звичайну текстовий рядок з природним роздільником. Тобто кому.

Text.Split("січень, лютий, березень, квітень, травень, червень, липень, серпень, вересень, жовтень, листопад, грудень", ", ")

Тут, начебто, все зрозуміло — функція Text.Split перетворює рядок у списку, розбиваючи її роздільником ", "

індекс можна Починати з будь-якого числа, у тому числі з одиниці (якщо прочитати документацію)

Table.AddIndexColumn(tbl, "Index", 1, 1)

Більш того, у функції Table.AddIndexColumn останнім аргументом можна задати крок, відмінний від одиниці. Відповідно, можна пронумерувати комірки і так — 15,20,25,30…

Table.AddIndexColumn(tbl, "Index", 15, 5)

З цього, власне, все й почалося…

У що переросла критика
Далі я наведу приклади коду з коментарями з поступово наростаючою складністю. Почну з того, яким чином можна нагенерить список місяців.

Найпростіше — безпосереднє перерахування, використовуючи синтаксис мови М:

months_list={"січень", "лютий", "березень", "квітень", "травень", "червень", "липень", "серпень", "вересень", "жовтень", "листопад", "грудень"}

Трохи складніше — розбиваючи рядок на частини роздільником:

Text.Split("січень, лютий, березень, квітень, травень, червень, липень, серпень, вересень, жовтень, листопад, грудень", ", ")

Використовуючи функцію генерації списку дат:

let
gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)),
month_name = List.Transform(gen, Date.MonthName)
in
month_name

Тут на першому кроці функція List.Dates генерує список дат, приймаючи першим аргументом стартову дату, другим кількість елементів, а третім — крок инкрементирования. В даному випадку, додаючи 32 дні до місяця, ми з кожним кроком гарантовано потрапимо в наступний місяць. Звичайно ж, будь у календарі 13 і більше місяців, такий прийом не прокатав. Але для навчальних цілей цілком зійде.



Другий крок — перетворення списку дат список назв місяців. Вивчивши документацію, ми знаходимо, що за перетворення дати в місяць відповідає функція Date.MonthName. Що вона робить — бере першим аргументом дату і віддає назва місяця у вигляді рядка. А якщо ми третім пунктом передамо і необов'язковий аргумент culture («ru-RU», «en-US», «uk-UA», «ar-LY»), то отримаємо назва місяця з урахуванням локалі. Ок, тоді, відповідно, нам треба застосувати цю функцію до кожного елементу списку.

Тобто отримати список {Date.MonthName(Дата1), Date.MonthName(Дата2),...,Date.MonthName(ДатаN)}. Ну або ось так {Date.MonthName(Дата1, «ru-RU»), Date.MonthName(Дата2, «ru-RU»),...,Date.MonthName(ДатаN, «ru-RU»)}

Тепер нам потрібен інструмент, який займається перетворенням списків. За перетворення списків відповідає функція List.Transform. Що вона робить — вона бере першим аргументом вхідний список і застосовує до кожного елементу функцію, яка передається другим аргументом.

Ок, говоримо ми, і відправляємо другим аргументом Date.MonthName. Далі слід пояснити, що відбувається — функція List.Transform бере кожен елемент масиву і згодовує його функції Date.MonthName, передаючи їй кожну Дату в якості аргументу неявним чином.

Добре, а якщо ми хочемо отримати назви місяців з урахуванням локалі? Скажімо, «uk-UA». Як нам встановити цей параметр? Як ми пам'ятаємо, List.Transform приймає другим аргументом _функцию_ (і тільки функцію), і ми не можемо явно передати цієї функції ні перший аргумент, ні тим більше другої. Відповідно, нам потрібна функція, що приймає, як в документації, один параметр. А давайте її створимо! Назвемо її просто «fn», зробимо в ній все, що необхідно, і віддамо її List.Transform'у. У javascript'е це називається «замиканням»:

fn = (x)=>Date.MonthName(x, "uk-UA"),

Тоді наш код буде виглядати так:

let
gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)),
fn = (x)=>Date.MonthName(x, "uk-UA"),
month_name = List.Transform(gen, fn)
in
month_name

Взагалі, ставити якесь ім'я функції не обов'язково. Можна вписати безіменну функцію прямо в дужках:

let
gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)),
month_name = List.Transform(gen, (x)=>Date.MonthName(x, "uk-UA"))
in
month_name

А ще ми пам'ятаємо, що у вихідному списку місяці були записані з маленької літери. Зробимо це все в тій же самій функції:

let
gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)),
month_name = List.Transform(gen, (x)=>Text.Lower(Date.MonthName(x, "uk-UA")))
in
month_name


А тепер давайте спробуємо сформувати список місяців дещо іншим способом — використовуючи функцію List.Generate. В документації сказано наступне:



Тобто всі три аргументи функції. Відмінно! Тоді задача зводиться всього лише до однієї рядку:

List.Generate(()=>#date(2016,1,1), (x)=>x<#date(2017,1,1), (x)=>Date.AddMonths(x,1), Date.MonthName)

Ну добре, нехай буде кілька, для наочності:

List.Generate(
()=>#date(2016,1,1),
(x)=>x<#date(2017,1,1),
(x)=>Date.AddMonths(x,1),
Date.MonthName
)

Що тут відбувається. Обчислюється функція ініціалізації стартового значення, задана першим аргументом. Це дата «1 січня 2016». Далі обчислюється функція, задана другим аргументом, якій _неявно_ передається значення (x), обчислена на попередньому кроці. Ця друга функція виконує просту задачу — повертає true/false, що означає, чи можна далі продовжувати обчислення. У нашому випадку «1 січня 2016» < «1 січня 2017», тобто true, тобто можемо продовжувати. В цьому випадку значення попереднього кроку відправляється в третю функцію, яка просто додає місяць до обчисленому значенню. Отримуємо «1 лютого 2016». Далі це значення відправляється в другу функцію, де «1 лютого 2016» порівнюється з «1 січня 2017», і функція, як і раніше віддає true, потім додається ще один місяць і т. д. Так відбувається до тих пір, поки до «1 грудня 2016» не додасться ще місяць, і обчислене значення стане «1 січня 2017». Коли це значення в черговий раз відправиться на перевірку, ми отримаємо false, т. к. «1 січня 2017» _равно_ «1 січня 2017», але ніяк не _меньше_.

Після обчислення накопичених таким чином проміжних значень, до кожного з елементів списку буде застосована остання функція — Date.MonthName. Точно так само, як це було описано вище для функції List.Transform

А що, якщо, як і минулого разу, ми хочемо, щоб місяці генерились з урахуванням локалі, а текст починався з маленької літери? Тоді ми створюємо свою кастомний функцію і робимо в ній все, що нам потрібно:

List.Generate(
()=>#date(2016,1,1),
(x)=>x<#date(2017,1,1),
(x)=>Date.AddMonths(x,1),
(x)=>Text.Lower(Date.MonthName(x, "en-US"))
)

Чим гарний такий спосіб генерації — тим, що нам абсолютно не важливо, скільки місяців у році. Та нехай хоч 2976, як на Плутоні. Ми ставимо перше число першого місяця року, і перше число першого місяця наступного року. Плюс додавання місяці до дати і суворе нерівність.

А що якщо ми точно знаємо, скільки місяців у році. Тоді можемо, маючи масив чисел від 1 до 12, згенерувати масив місяців за номером:

List.Transform({1..12}, (x)=>Text.Lower(Date.MonthName(#date(2016,x,1), "ar-LY")))


Це вже відомий нам List.Transform, який першим аргументом передається масив чисел, а другим — функція перетворення числа в місяць. Тут все досить примітивно, що теж повинно бути зрозуміло.

Набагато цікавіше функція List.TransformMany. В принципі, тут все те ж саме, що і в List.Transform, але, на відміну від List.Transform, у List.TransformMany додається ще один аргумент — це функція, в яку неявним чином відправляються вже два аргументи — _первоначальное_ і _вычисленное_ значення, що дозволяє нам в один прохід використовувати і номер місяця, і його обчислене назву. Нам залишиться лише зчепити їх в один рядок:

List.TransformMany({1..12}, (x)=>{#date(2016,x,1)}, (x,y)=>Number.ToText(x)&" - "&Text.Lower(Date.MonthName(y)))


Трохи пізніше я покажу, як це використовувати для формування таблиці.

Переходимо до таблиць
Тут поки все просто — використовується Text.Split, Table.AddIndexColumn зі стартом від одиниці і Table.FromList з одночасним ім'ям стовпця (не потрібно перейменування дефолтного «Custom1»).

let
src=Text.Split("январь|февраль|март|апрель|май|июнь|июль|август|сентябрь|октябрь|ноябрь|декабрь","|"),
convert = Table.FromList(src, Splitter.SplitByNothing(), {"Місяць"}),
add_index = Table.AddIndexColumn(convert, "Index", 1, 1)
in
add_index

Table.FromList + List.Positions
Тут — нетипове застосування функції Table.FromList. Другим аргументом їй передається функція, що повертає масив комірок в будівництв. Послідовність наступна — створюється масив рядків src, до нього створюється масив індексів pos, далі масив індексів перетвориться в таблицю, використовуючи кастомний функцію. Ця кастомний функція перебирає масив індексів і звертається до масиву рядків по обраному індексу.

Поясню простіше — наприклад, береться, скажімо, елемент №0 з списку «pos» і передається у функцію, де 0 формується масив {src{0}, 0+1}, тобто {«січень», 1}. Далі функція Table.FromList розкладає їх на два стовпчика «Місяць» і «Index»

let
src=Text.Split("январь|февраль|март|апрель|май|июнь|июль|август|сентябрь|октябрь|ноябрь|декабрь","|"),
pos = List.Positions(src), // обчислюємо масив індексів списку
t = Table.FromList(pos, (x)=>{src{x},x+1}, {"Місяць","Index"})
in
t

Якщо ж користуватися тільки з інтерфейсом, то Table.FromList за замовчуванням передається фейковая функція Splitter.SplitByNothing(), яка, по суті — заглушка для роботи з Table.FromList

Зливаємо два паралельних списку в один за допомогою функції Table.FromColumns. Тут ми формуємо два паралельних списку — список місяців і список його індексів. Якщо спробувати візуалізувати цей процес, то виглядає це приблизно так само, як застібання блискавки на одязі ;) Перед «застегиванием» трансформуємо список індексів, додаючи до кожного елементу одиницю:

let
src=Text.Split("январь|февраль|март|апрель|май|июнь|июль|август|сентябрь|октябрь|ноябрь|декабрь","|"),
pos = List.Positions(src),
transform = List.Transform(pos, (x)=>x+1),
t = Table.FromColumns({src, transform},{"Місяць","Index"})
in
t

Обіцяний приклад з List.TransformMany розпишу детально. Припустимо, у нас є список чисел від 1 до 12:



А також є функція перетворення числа в місяць:

fn = (x)=>Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))}

Єдине, про що слід подбати про те, щоб функція повертала не просто значення, а значення, яке є елементом масиву:

fn = (x)=>{Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))}

Також створимо фінальну функцію, яка на кожному етапі, використовуючи стартове і обчислене значення, створює список з стартового і обчисленого. Типу такого — {«march», «3»}:

final = (start_element, calculated_element)=>{start_element, calculated_element},

Тепер передамо функції List.TransformMany функцію перетворення кожного елемента і фінальну функцію в якості аргументів, і натравим їх на наш масив від 1 до 12:



Після чого створимо таблицю з отриманого масиву масивів

:
let
start_list = {1..12},
fn = (x)=>{Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))},
final = (start_element, calculated_element)=>{start_element, calculated_element},
transform = List.TransformMany(start_list, fn, final),
t = #table({"Index", "Місяць"}, transform)
in
t

В результаті отримаємо таку таблицю:


Далі ми можемо формувати кінцеву таблицю так, як нам захочеться. Наприклад, помножити номер місяця на 100, до назви місяця додати текст і додати стовпець з назвою дня тижня першого числа цього місяця:

let
start_list = {1..12},
fn = (x)=>{Date.MonthName(#date(2016,x,1),"en-US")},
final = (x,y)=>{
x*100,
y&" has "&Number.ToText(Date.DaysInMonth(#date(2016,x,1)))&" days",
"First day of "&y&" is "&Date.DayOfWeekName(#date(2016,x,1), "en-US")
},
transform = List.TransformMany(start_list, fn, final),
t = #table({"Index", "Month", "FirstDayOfWeek"}, transform)
in
t



А для тих, хто збочень воліє аскетизм і дзен, пропоную рішення вихідної задачі цим же прийомом в один рядок:

#table({"Месяц","Index"},List.TransformMany({1..12},(x)=>{Text.Lower(Date.MonthName(#date(2016,x,1)))},(x,y)=>{y,x}))

Ну і, мабуть, останній спосіб створити вихідну табличку — методом List.Accumulate. Ось, що пише на цю тему help:



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

let
fn_accum = (accum,x)=> accum & Text.Upper(x)&".",
result = List.Accumulate({"січень", "лютий", "березень", "квітень"}, "", fn_accum)
in
result

СІЧЕНЬ.ЛЮТИЙ.БЕРЕЗЕНЬ.КВІТЕНЬ.

Тут функції fn_accum неявно передаються два параметри — накопичений на даному кроці результат і поточне значення зі списку. Крім чисел і тексту, можна також використовувати списки і таблиці:

List.Accumulate({1..12}, {}, (accum,x)=> accum & {Date.MonthName(#date(2016,x,1))})

В даному прикладі береться порожній масив і до нього «приклеюється» номери місяців, сконвертовані в текстовий рядок. По аналогії — для таблиць:

let
lst = {1..12},
start_table = #table({},{}),
fn= (accum,x)=>accum & #table({"Місяць","Index"},{{Text.Lower(Date.MonthName(#date(2016,x,1))),x}}),
result=List.Accumulate(lst, start_table, fn)
in
result

Тут ми задаємо початково порожню таблицю і «приклеюємо» до неї обчислені на кожній ітерації рядка.

Сподіваюся, роз'яснити деякі тонкощі роботи з кодом безпосередньо у мене вийшло краще, ніж заплутати в ньому. Насправді, M — дуже красивий і лаконічний мову. І якщо вам і так і сяк і по-всякому доводиться вертіти дані в повсякденній роботі, є сенс вивчити його трохи глибше.
Джерело: Хабрахабр

0 коментарів

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