Не так давно наткнулся на статью на Хабре о том, как пользоваться Power BI и как проводить с помощью него Простой план-фактный анализ ^[1]. Автору огромный респект за труд — материал, действительно, полезный. Более чем достаточно для начинающего. Однако, насколько я понял, для многих работа с 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 )

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

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

Источник ^[2]