Не так давно наткнулся на статью на Хабре о том, как пользоваться 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"
Что делает автор, если переводить код на русский язык
- Создаёт вручную список из названий месяцев
- Преобразовывает список в таблицу
- Добавляет столбец с индексом, начиная с нуля, с шагом в единицу
- Трансформирует столбец «Index», добавляя единицу к каждой ячейке
- Переименовывает дефолтное имя столбца «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 )
В данном примере берётся пустой массив и к нему «приклеиваются» номера месяцев, сконвертированные в текстовую строку. По аналогии — для таблиц:
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 — очень красивый и лаконичный язык. И если вам и так и эдак и по-всякому приходится вертеть данные в повседневной работе, есть смысл изучить его немного поглубже.
Автор: SergeyLossev
