Евлампий, С Новым Годом!!! или как объединить текстовые значения
#АнатомияФункций – Text.*

Всем привет! С наступающим!
По этому поводу небольшая лёгкая заметка )))
Возьмем простую задачку - есть имя и есть текст поздравления и надо их как-то объединить.
Сразу накидаем вариантов:

let
name = "Евлампий",
congratulation = "С Новым Годом !!!",
lst = {"Акакий","Евлампий","Глафира"},

out1 = name & ", " & congratulation,
out2 = Text.Combine({name,congratulation},", "),
out3 = Text.Format("#{0}, #{1}",{name,congratulation}),
out3a = Text.Format("Глубокоуважаемый и горячо любимый #{0}, в этот знаменательный день пишу Вам, чтобы сказать - #{1}. Желаю здоровья, счастья и успехов!",{name,congratulation}),
out4 = Text.Insert(congratulation,0,name&", "),
out5 = Text.ReplaceRange(congratulation,0,0,name&", "),

fnComb = (x,y)=> Text.ReplaceRange(y,0,0,x&", "),
out6 = fnComb(name,congratulation),
fnClojureComb=(x)=>(y)=>Text.Insert(x,0,y&", "),
out7 = List.Transform(lst,fnClojureComb(congratulation))
in
out7

И разберем -
out1 - простейший вариант через амперсанд
out2 - то же, но через Text.Combine - полезно, когда соединяем несколько значений из списка (обращаю внимание - в список можно объединять результаты разных шагов запроса)
out3 - не самый очевидный вариант, но бывает полезным, когда у вас есть достаточно большой текст, по которому нужно раскидать переменные - как в варианте out3a (обращаем внимание, что переменные опять же передаем списком, хотя есть и вариант передавать их записью)
out4 - текст не обязательно комбинировать, можно же и запихнуть один в другой
out5 - причем запихивать можно и через замену - посмотрите, просто заменяем текст нулевой длины - зачем так? да незачем - просто не бывает какого-то одного правильного варианта, их всегда несколько и вы можете выбирать наиболее удобный и понятный для себя
fnComb - и раз уж мы про функции - любой такой шаг можно превратить в функцию, всё то же самое, просто конкретные названия шагов меняем на абстрактные переменные
out6 - а при использовании функции передаем то, что необходимо
fnClojureComb - ну и куда ж без замыканий - мало ли текст поздравления будет меняться, да и список может быть разным
out7 - пример использования (особо не распространяюсь - подробности были тут)

К чему это всё? Да просто показать, что на одну и ту же задачу можно смотреть очень по-разному, а какой путь выбрать - решение каждого.
Поэтому в Новом Году желаю каждому найти свою функцию и успешно ее применить 😉

Ещё раз с наступающим! Всех благ! 🥳🥳🥳
@buchlotnik

Всем добра из Краснодара! И с наступающим! Не прошло и полгода, а тут уже столько народу.
Всем вам спасибо хочу сказать я, а особенно
@buchlotnik за науку и помощь в развитии канала
@sboy_ko за помощь и чат PQ
@MaximZelensky фактически мой сенсей
@InkognitoUser, @LebedevDmitry и @kkadikin за помощь и чат PBI
Всем подписчикам развития и побольше практики в новом году!
У меня все. Ушел в салат.😊

(x,y)=>x[y] или как правильно обращаться к полям в функции
#АнатомияФункций - Table.Column, Record.Field
Всем привет!
Продолжим рассмотрения вопроса написания пользовательских функций. Сегодня на повестке обращение к элементам и полям.
Начнём с простого:
let
lst = {1,2,3,4,5},
f = (x,y)=>x{y},
to = f(lst,2)
in
to
Вернёт нам 3. Здесь логика, думаю, понятна – нас интересует y-ый элемент списка x. Нумерация с нуля, поэтому 2 обозначает третий))). Зачем такое пихать в функцию? Ну например, чтобы можно было писать вот так:
f = (x,y)=>x{y-1},
to = f(lst,3)
что также вернёт нам 3, но только в этот раз мы явно попросили третий элемент и не думали как там на самом деле идет нумерация. Так бывает удобно.
Но что же с полями записи или таблицы?
let
rec = [a=1,b=2,c=3],
tbl = Table.FromRecords({rec}),
f = (x,y)=>x[y],
to1 = f(rec,"b"),
to2 = f(tbl,"b")
in
to2
Упс…
to1 - «поле y записи не найдено».
to2 – «столбец y таблицы не найден»
Делаем вывод – так не работает (хотя в спецификации на язык явно используется конструкция x[y], но там под y подразумевается именно название поля, а не некое вычисляемое значение).
Решение есть – использовать функции, причем для записей и таблиц они разные – Record.Field и Table.Column соответственно:
let
rec = [a=1,b=2,c=3],
tbl = Table.FromRecords({rec}),
f = (x,y)=>Record.Field(x,y),
g = (x,y)=>Table.Column(x,y),
to1 = f(rec,"b"),
to2 = g(tbl,"b")
in
to2
И теперь всё корректно.
Закономерный вопрос – а зачем вообще нужно запихивать обращение к полям в функции? Рассмотрим пример с группировкой таблицы:
let
tbl = Table.FromRecords({ [n="Вася",a=1,b=2,c=3,d=4],
[n="Антон",a=1,b=3,c=5,d=7],
[n="Вася",a=5,b=6,c=7,d=8],
[n="Антон",a=2,b=4,c=6,d=8]}),
group = Table.Group(tbl, {"n"}, {{"сумма по a", each List.Sum([a]), type number}})
in
group
Так группировка, разумеется, работает. Но что если задача у нас будет найти суммы по всем столбцам, кроме первого? Ладно когда их 4, а если пара десятков? А если их список динамически меняется?
Для начала обратим внимание на эту конструкцию -
{"сумма по a", each List.Sum([a]), type number}
- для каждого столбца нам нужно получить список из трёх параметров {название,функция,тип} (хотя можно обойтись только парой {название,функция})
Далее внимательно смотрим на
each List.Sum([a])
, вспоминаем, что это то же самое, что и (x)=>List.Sum(x[a]) (кому не понятно - читаем тут )
Осталось только сообразить, что под x в данном случае подразумевается таблица (та самая, которую вы получаете выбрав при группировке "все строки").
Теперь мы готовы воспользоваться вышеуказанным приёмом:
let
tbl = Table.FromRecords({ [n="Вася",a=1,b=2,c=3,d=4],
[n="Антон",a=1,b=3,c=5,d=7],
[n="Вася",a=5,b=6,c=7,d=8],
[n="Антон",a=2,b=4,c=6,d=8]}),
nms = Table.ColumnNames(tbl),
lst = List.Transform(List.Skip(nms),(x)=>{"Сумма по "&x,(y)=>List.Sum(Table.Column(y,x)),Int64.Type}),
group = Table.Group(tbl, nms{0}, lst)
in
group

nms – получили имена всех столбцов
lst – получаем список преобразований - для всех столбцов, кроме первого (List.Skip), превращаем каждое название столбца в тройку {название,функция,тип}. Не запутайтесь – x – это название исходного столбца, от него пишем исходное преобразование, "Сумма по "&x – название столбца после группировки; (y)=>List.Sum(Table.Column(y,x)) – вложенная функция, поэтому использована другая переменная y, запись означает «в таблице y возьми столбец x и найди сумму значений»
group – группируем таблицу по первому столбцу (nms{0}) используя список преобразований lst

Собственно, всё (есть дополнение). Столбцы проанализированы пачкой. И это одна из наиболее простых и очевидных задач, решаемых подобным способом.

Надеюсь, было полезно. Всех благ!
@buchlotnik

Как правильно обращаться к полям в функции (дополнение)
#АнатомияФункций

Дополнение, которое не влезло в объем основного поста:
1. для конструкций record[[ ],[ ],[ ]] и table[[ ],[ ],[ ]] используйте Record.SelectFields и Table.SelectColumns соответственно
2. для большей прозрачности кода хорошо использовать замыкания (подробнее про них тут):

let
    tbl = Table.FromRecords({   [n="Вася",a=1,b=2,c=3,d=4],
                                [n="Антон",a=1,b=3,c=5,d=7],
                                [n="Вася",a=5,b=6,c=7,d=8],
                                [n="Антон",a=2,b=4,c=6,d=8]}),
    nms = Table.ColumnNames(tbl),
    f=(x)=>(y)=>List.Sum(Table.Column(y,x)),
    lst = List.Transform(List.Skip(nms),(x)=>{"Сумма по "&x,f(x),Int64.Type}),
    to = Table.Group(tbl, nms{0}, lst)
in
    to

@buchlotnik

List.Accumulate – а надо ли?
#АнатомияФункций - List.Accumulate
Всем привет!
По просьбам трудящихся разберем функцию List.Accumulate. Читаем справку:

List.Accumulate(list as list, seed as any, accumulator as function)

list – наш список, seed - опорное значение, accumulator – функция.
accumulator – функция от двух переменных- (state,current)=> где state- текущее накопленное значение (на первой итерации это seed), current – текущий элемент списка.
Для чего используется функция? В сети можно найти много вариантов, доказывающих «универсальность» функции. Тут вам и сумма

List.Accumulate({1..10},0,(s,c)=>s+c)

И преобразование списков

List.Accumulate({1..10},{},(s,c)=>s&{“Column”&Text.From(c)})

И даже работа с таблицами

List.Accumulate({1..9},Table.FromColumns({{1..9}},{"n"}),(s,c)=>Table.AddColumn(s,Text.From(c),(r)=>Record.Field(r,"n")*c))

Проблема в том, что сумму гораздо проще найти через List.Sum, список проще (и быстрее) преобразовать через List.Transform. Это к тому, что функцию List.Accumulate примотать можно к большому числу задач, но это не будет самым простым и быстрым решением. По случаю порылся в архивах и предлагаю пару примеров, где реально в первом приближении без Accumulate не обойтись.

let
    f=(x,y)=>[a=Text.Split(y,":"), b= Record.AddField(x,a{0},a{1})][b],
    g=(x)=>List.Accumulate(Text.Split(x,";"),[],f),

    from = {"название:коробка;высота:11;ширина:120;длина:54",
            "название:бак;ширина:50;высота:120",
            "название:ящик;длина:123;ширина:12"},
    tr = List.Transform(from,g),
    to = Table.FromRecords(tr,null,MissingField.UseNull)
in
    to

from – на вход подана некая выгрузка, в которой присутствуют пары «параметр:значение», разделенные через точку с запятой – а надо собрать таблицу
tr – преобразуем список в список записей (нам же нужно сохранить информацию где поле, где значение)
to - ну и собираем таблицу (второй аргумент null – поскольку набор полей нам заранее не известен, третий - MissingField.UseNull – если в отдельной записи поля не будет вместо ошибки выдаст null – удобно, берем на заметку)
Теперь по функциям:
g – функция от одного аргумента, потому что отвечает за преобразование элемента списка; её работа состоит в разделении текста по «;» и агрегации полученного списка в запись, поэтому в качестве seed – [ ] (пустая запись), а аккумулятор – f
f – функция от двух аргументов (это те же state и current – но использованы x и y дабы показать, что название переменной не имеет значения!) – здесь всё просто: a – поделили текст по «:», b – добавляем к записи новое поле (можно было и через let, но я так не люблю, объяснял тут)
Собственно задача решена. Мне проще было через Accumulate, есть альтернативная точка зрения – использовать рекурсию (Poohkrd оцени, эксклюзивно для тебя):

let
   f=(x,y)=>[a=Text.Split(y,":"), b= Record.AddField(x,a{0},a{1})][b],
   g=(x,y)=>if Text.Contains(y,";") then @g(f(x,Text.BeforeDelimiter(y,";")),Text.AfterDelimiter(y,";")) else f(x,y),

    from = {"название:коробка;высота:11;ширина:120;длина:54",
            "название:бак;ширина:50;высота:120",
            "название:ящик;длина:123;ширина:12;высота:1"},
    tr = List.Transform(from,(x)=>g([],x)),
    to = Table.FromRecords(tr,null,MissingField.UseNull)
in
    to

Смотрите кому как удобнее, по скорости одинаково. Просто помните, что рекурсия – это не быстро, Accumulate выигрыша по скорости не даст, а значит использовать этот приём стоит только в тех ситуациях, когда нет прямой альтернативы или альтернативный путь тернист для написания.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Нормализация таблицы с повторяющимися заголовками
#АнатомияФункций - List.Accumulate
Пы.Сы. (основной пост)
там же в архивах нашлась другая, весьма специфическая задачка, также весьма просто решаемая аккумулятором, но на мой вкус это скорее учебный пример – не надо так данные изначально организовывать:

let
    from = Table.FromRecords({
                            [n="Вася",#"a.1"=1,#"b.1"=2,#"c.1"=3,#"a.2"=4,#"b.2"=5,#"c.2"=6,#"a.3"=7,#"b.3"=8,#"c.3"=9],
                            [n="Петя",#"a.1"=1,#"b.1"=3,#"c.1"=5,#"a.2"=7,#"b.2"=9,#"c.2"=11,#"a.3"=13,#"b.3"=15,#"c.3"=17],
                            [n="Коля",#"a.1"=2,#"b.1"=4,#"c.1"=6,#"a.2"=8,#"b.2"=10,#"c.2"=12,#"a.3"=14,#"b.3"=16,#"c.3"=18]}),
    lst = Table.ColumnNames(from),
    nms = List.Distinct(List.Transform(List.Skip(lst),(x)=>Text.BeforeDelimiter(x,"."))),
    cmb = List.Zip({nms,List.Transform(nms, (x)=>List.Select(lst,(y)=>Text.Contains(y,x)))}),
    tbl = Table.TransformColumnTypes(from,List.Transform(List.Skip(lst),(x)=>{x,Text.Type})),
    to = List.Accumulate(cmb,tbl,(s,c)=>Table.CombineColumns(s,c{1},(x)=>Text.Combine(x," "),c{0}))
in
    to

@buchlotnik

List.Accumulate WITH recursion – а что, так можно было?
#АнатомияФункций – List.Accumulate
Всем привет!
Чтобы окончательно закрыть вопрос с аккумулятором и рекурсией (начали тут) давайте разберем ещё один пример. Итак, например, при парсинге сайта, вы получили некий список значений, а вам нужны уникальные. Ну ОК – элементарный код:

let
    lst = {1,2,3,1,2,3,1,2,3},
    to = List.Distinct(lst)
in
    to

Но если б всё было так просто, не было бы статьи. Далеко не всегда значения идут по одному:

let
    lst = {1,2,3,{1,4,5},2,{3,6,7},1,2,3},
    acc = List.Accumulate(lst,{},(s,c)=> s & (if Value.Is(c,List.Type) then c else {c})),
    to = List.Distinct(acc)
in
    to

Здесь на шаге acc мы реализуем следующую логику: «если очередной элемент – список, сделай конкатенацию списков s & c, иначе добавь к списку элемент s &{c}». Тоже вроде просто. Немного усложню код:

let
    lst = {1,2,3,{1,4,5},2,{3,6,7},1,2,3},
    f=(x)=>List.Accumulate(x,{},(s,c)=> s & (if c is list then c else {c})),
    to = List.Distinct(f(lst))
in
    to

обратите внимание, что шаг запроса превращён в функцию (заодно if then записан немножко по-другому - может так кому понятнее или удобнее будет), всё остальное то же самое. Но зачем это надо? А вот зачем – кто сказал, что будут только списки, а не списки списков или списки списков списков… (чувствуете как рекурсией запахло)?

let
    lst = {1,2,3,{1,4,{5,8}},2,{3,{6,9},7},1,2,3},
    f=(x)=>List.Accumulate(x,{},(s,c)=> s & (if c is list then @f(c) else {c})),
    to = List.Distinct(f(lst))
in
    to

А вот это уже гемор. И в этой ситуации, независимо от числа уровней вложенности, мы решаем поставленную задачку с помощью @f(), т.е. рекурсивного вызова нашего аккумулятора. На мой вкус очень просто и элегантно.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.FromList vs Table.AddColumn – кто кого?
#АнатомияФункций – Table.FromList, Table.AddColumn

Всем привет!
Один известный вам медведь отправил меня читать таких же упоротых, как и я. Ну и подсмотрел идейку. Итак, на входе имеем таблицу с несколькими числовыми столбцами и нужно добавить по каждой строке агрегации – сумму, среднее и медиану. Делов-то:

let
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Buffer(Table.Repeat(from,99999)),
    add = Table.AddColumn(tbl,"сумма",(r)=>List.Sum(List.Skip(Record.ToList(r)))),
    add1 = Table.AddColumn(tbl,"среднее",(r)=>List.Average(List.Range(Record.ToList(r),1,6))),  
    to = Table.AddColumn(tbl,"медиана",(r)=>List.Median(List.Range(Record.ToList(r),1,6)))
in
    to

Что смущает в приведенном коде – обилие шагов, необходимость на каждом шаге вычислять требуемый диапазон для расчёта, ну и Table.Buffer - если забудете поставить, вычисляться запрос будет крайне долго, а так на моей машине потребовалось 13 секунд. Мысль с добавлением сразу всех агрегаций в виде записи с последующим Table.ExpandRecordColumn отметайте - это дорогая операция, особенно на больших таблицах – получилось вообще 17 секунд.
Но что же делать? Чуйка подсказывает, что со списками работа шустрее, чем на записях. Что ж, пробуем:

let
    f=(x)=>x&[a=List.Skip(x),b={List.Sum(a),List.Average(a),List.Median(a)}][b],
    
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Repeat(from,99999),
    lst = Table.ToRows(tbl),
    tr = List.Transform(lst,f),
    nms = Table.ColumnNames(from)&{"сумма","среднее","медиана"},
    to = Table.FromRows(tr,nms)
in
    to

Вроде выглядит неплохо: lst – разбили на строки, tr – преобразовали строки списком, f - функция, добавляющая к списку его сумму, среднее и медиану, nms – список столбцов новой таблицы, to- собрали таблицу из строк. Обращаю внимание – буферить не потребовалось, НО скорость та же - 13 секунд. Мдя. Собственно, с этой мыслью я и жил до вчера. Но тут напомнили, что в Table.FromList можно передавать не только Splitter:

let
    f=(x)=>x&[a=List.Skip(x),b={List.Sum(a),List.Average(a),List.Median(a)}][b],
    
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Repeat(from,99999),
    lst = Table.ToRows(tbl),
    nms = Table.ColumnNames(from)&{"сумма","среднее","медиана"},
    to = Table.FromList(lst,f,nms)
in
    to

Что поменялось? Да почти ничего – просто вместо двух шагов (tr и to) остался один; функция f та же, только скорость – 10 секунд! - 25% по производительности на пустом месте. Вот так, мир не рухнул – на списках всё-таки быстрее 😉

Надеюсь, было полезно.
Всех благ!
@buchlotnik

источник вдохновения - в комментариях от Lossev

Splitter.SplitTextByPositions или плач по регуляркам
#АнатомияФункций - Splitter.SplitTextByPositions
Всем привет! Разберем сегодняшний сабж по разделению текста. Имеем:

let
    from = Table.FromRecords({
        [name = "Вася",txt = "1. Текст 1-1. Текст с пробелами 1-2. текст с числами 123 2. ещё текст"],
        [name = "Коля", txt = "3. текст. 4. а текст-то бывает разный 5. например с числами 2.5"],
        [name = "Евлампий", txt ="12. и номеров много 123. очень много 1234. прям совсем"]
    }),
    tr = Table.TransformColumns(from,{"txt",SplitByAnyChars({"0".."9","-"},{"."})}),
    to = Table.ExpandListColumn(tr, "txt")
in
    to

Текст в табличке нужно разделить на пункты, причем: сами номера пунктов нужно сохранить; номер пункта – это набор цифр переменной длины (возможны подпункты через дефис) и обязательно точка в конце; в тексте могут встречаться и цифры, и дефисы, и точки.
Мдя, задачка для регулярок, которых в M толком и не завезли. Но как видим проблема элегантно решается вызовом функции SplitByAnyChars, только есть проблема – такой функции на самом деле нет и её нужно написать )))
Не претендую на оптимальность, но думаю, что логика решения может оказаться небезынтересной. Поехали:

SplitByAnyChars = (chars as list, last as list)=>(txt as text)=>
let
    lst = Text.ToList(txt),
    tbl = Table.FromList(lst,
                        (x)=>[ a = List.Contains(chars,x),b = List.Contains(last,x),c = a or b,d = {a,b,c}][d],
                        {"chars","last","all"}),
    index = Table.AddIndexColumn(tbl, "i", 0, 1),
    group = Table.Group(index, "all", {{"n", each List.Min([i])},
                                        {"l", each List.Last([last])},
                                        {"c", each List.First([chars])}},
                                        GroupKind.Local),
    filtr = Table.SelectRows(group, each ([l] = true) and ([c] = true)),
    to = Splitter.SplitTextByPositions(filtr[n])(txt)
in
    to

Во-первых, аргументы – chars – список символов, которые могут присутствовать в разделителе, last – список символов, на которые должен заканчиваться разделитель, txt – разделяемый текст (обращаю внимание, что аргументы разделены на две группы – так проще вызывать функцию и не нужно каждый раз городить (x)=>Split… Это называется замыкание и описано тут).
lst – преобразовали наш текст в список символов
tbl – первая изюминка решения – вместо списка символов получаем таблицу из трех столбцов: «chars» - относится ли символ к тем, которые должны быть в разделителе; «last»-относится ли к конечным; «all» - относится ли к одной из этих двух категорий
index – добавляем индексный столбец с нумерацией с нуля, как в списках
group – вторая изюминка – таблица нам была нужна, чтобы сделать группировку по столбцу «all», причем с параметром GroupKind.Local – в этой ситуации строки будут группироваться последовательно и каждая группа символов, удовлетворяющих условию, получит свою строку. При этом мы добавляем столбцы: n – минимальный индекс, т.е. с какой позиции начинается группа; l – относится ли последний символ в группе к тем, на которые должен заканчиваться разделитель; с – относится ли первый символ в группе к тем, которые составляют тело разделителя.
filtr – теперь оставляем только те строки, для которых вышеуказанные условия выполняются
to – ну и вишенка – в полученной таблице в индексном столбце содержатся ровно те позиции, по которым нужно разделить текст. Позиции передаем в Splitter.SplitTextByPositions – думаю из названия понятно, что делает данная функция. Вот только у сплиттера нет аргумента, отвечающего за сам разделяемый текст, я даже от кого-то слышал, что его вообще надо использовать только в составе других функций… С другой стороны, мы же знаем про замыкания – берем и насильно передаем текст во вторых скобках… и оно работает!
Как-то так. Скорость не заоблачная, но вполне терпимая.
Надеюсь, было полезно.

Всех благ!
@buchlotnik

Навигация по каналу
#ПолезныеСсылки

Всем привет!
По просьбам читателей приступили к решению проблемы поиска информации на канале. Для этого взяли третьего. Прошу любить и жаловать - @PQfromtankbot!

Он только приступил к работе и знает ещё не всё, но что-то уже сейчас может подсказать.
Надеюсь, будет полезен.
Всех благ!!!
@buchlotnik

Пы.Сы. Прошу новичка не обижать, а если натворит чего - пишите в личку мне. Пуха не трогайте - он и без этого занят 😉

Запрос в одну строку или функции в отдельные шаги?
#АнатомияФункций

Всем привет!
Потянуло пофилософствовать. Итак, возьмем запрос

let
    lst = {1..12},
    trnsf = List.Transform(lst,(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy")),
    to = Text.Combine(trnsf,"#(lf)")
in
    to

который совершенно не обязательно писать как выше, зачем вообще два раза указывать последний шаг:

let
    lst = {1..12},
    trnsf = List.Transform(lst,(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy"))
in
    Text.Combine(trnsf,"#(lf)")

Работает так же, некоторые считают, что так даже нагляднее (автор не разделяет данную точку зрения). В целом можно считать (упрощённо), что отдельные шаги – это просто куски кода, которым дали имя, и когда вы ссылаетесь на конкретный шаг – вместо имени этот кусок кода и подставляется. Поэтому если мы напишем просто:

Text.Combine(List.Transform({1..12},(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy")),"#(lf)")

это будет работать. Почему? Потому что в принципе весь запрос представляет собой одно выражение, его можно не делить на шаги и писать вот так в одну (но очень большую и совершенно нечитаемую) строку (т.е. вы можете использовать PQ как калькулятор – просто напишите 8*(127-13) и он вам выдаст результат).
Но ведь можно задуматься и об обратном – мы для того и делим запрос на шаги, чтобы повысить его читаемость, а значит вместо вкладывания кучи функций друг в друга, можно поделить запрос на шаги, особенно если фрагмент повторяется или сложный и требует вложенных конструкций let in – выносим его в отдельный шаг с определенным именем, потом вызываем по необходимости.
Например, код выше можно переписать так:

let
    lst = {1..12},
    f=(x)=> Date.ToText(#date(2022,x,1),"MMMM yyyy"),
    trnsf = List.Transform(lst,f),
    to = Text.Combine(trnsf,"#(lf)")
in
    to

получилось – f – это функция преобразования номера месяца в дату с определённым форматированием, а на шаге trnsf – мы эту функцию используем для преобразования элементов списка. По скорости оба варианта эквивалентны, но с точки зрения читаемости второй вариант на мой вкус лучше, особенно если мы говорим про сложные боевые примеры:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    group = Table.Group(from, "a", {{"sum b", each List.Sum(List.FirstN(List.Sort([b],Order.Descending),3))},{"sum c",each List.Sum(List.FirstN(List.Sort([c],Order.Descending),3))}})
in
    group

хорошо, если столбцов два, а когда больше? А так мы выносим функцию и получаем счастье:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    f=(y,z)=>(x)=>[ a = Table.Column(x,y),
                    b = List.Sort(a,Order.Descending),
                    c = List.FirstN(b,z),
                    d = List.Sum(c)][d],
    group = Table.Group(from, "a", {{"sum b", f("b",3)},{"sum c",f("c",3)}})
in
    group

Обращаю внимание – на шаге f использовано замыкание – мы просто передаем функции имя столбца и число элементов, которые надо просуммировать – мало ли что поменяется; расписали по шагам – так нагляднее, но согласитесь – больше одного раза вы бы вряд ли стали это делать. Сам синтаксис через записи обсуждался тут, а как ещё сильнее сократить/упростить код – обсуждалось здесь.
В целом этим постом я хотел сказать, что написание функций – это не какая-то запредельная задача – это просто вынесение определённых повторяющихся шагов в шаг с определенным именем для многократного и удобного использования в коде.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.Group – Часть 1. Третий аргумент
#АнатомияФункций – Table.Group
Всем привет!
По запросам страждущих немножко опишу работу с этой замечательной функцией.
Начнём с простого:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    f=(x)=>List.Sum(x[b]),
    group = Table.Group(from, "a", {"sum b",f})
in
    group

Посмотрим на аргументы Table.Group:
Первый аргумент – сама таблица
Второй аргумент – столбец или список столбцов, по которым нужно группировать (обращаю внимание – мышкоклацанием один столбец все равно будет записан как список из одного элемента - {“c”}. Какая разница? – спросите вы – Дойдем до пятого аргумента – объясню – отвечу я )
Третий аргумент – агрегация или список агрегаций, т.е. либо {«агрегирующий столбец»,функция_агрегации}, либо {{«агрегирующий столбец1»,функция_агрегации1 },{«агрегирующий столбец2»,функция_агрегации2}}
И вот тут нужно разобраться. Функция агрегации вычисляет что-то на основании результата группировки, но что является этим результатом? Подставьте в запрос выше вместо функции f такое f=(x)=>x – и вы увидите, что результатом является таблица. И это самое важное знание.
Разберем f=(x)=>List.Sum(x[b]) - x[b] – это обращение к столбцу таблицы, которое дает нам список значений, далее мы этот список суммируем (в варианте each List.Sum([b]) который вы получите мышкоклацанием это не очевидно – обсуждалось тут).
Теперь же мы понимаем, что с таблицей можно творить всякое – допустим вам нужно не просто получить сумму, а найти суммарную разницу по двум столбцам (можно конечно допстолбец добавить или пойти через CombineColumns, но мы же не хотим ничего усложнять):

let
    from = ...//взять из первого запроса,
    f=(x)=>List.Sum(x[b])-List.Sum(x[c]),
    group = Table.Group(from, "a", {"diff b c",f})
in
    group

Обращаю внимание – с первого запроса поменялась только функция f (это к тому, что функцию лучше вынести, чем каждый раз лазать в дебри запроса). Как видим – с несколькими полями всё также прекрасно работает.
Ну и раз уж аргументом является таблица, можем задействовать всю мощь табличных функций. Например, часто спрашивают как добавить нумерацию, но внутри группы:

let
    from = ...//взять из первого запроса,
    f=(x)=>Table.AddIndexColumn(x,"i",1,1),
    group = Table.Group(from, "a", {"tmp",f}),
    to = Table.Combine(group[tmp])
in
    to

Думаю, с работой функции для добавления индексного столбца сложностей не возникнет. А вот шаг to – просто обратите внимание – в столбце tmp у нас таблицы, group[tmp] – список этих таблиц, и этот список мы отдаём Table.Combine – всё, задача решена.
Или такое – нужно получить по каждому сотруднику лучшую продажу или последнюю дату – это решается через Table.Max:

let
    from = ...//взять из первого запроса,
    f=(x)=>Table.Max(x,"b"),
    group = Table.Group(from, "a", {"tmp",f}),
    to = Table.FromRecords(group[tmp])
in
    to

Обращаю внимание – Table.Max возвращает запись, поэтому в шаге to мы используем Table.FromRecords.
Вот, пожалуй, и всё – если вы понимаете, что агрегируете (точнее с чем приходится работать вашей функции) задачи решаются просто и элегантно.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.Group – Часть 1.1 Третий элемент третьего аргумента – тип данных
#АнатомияФункций – Table.Group
Всем привет!
Продолжаем изыскания по Table.Group – надо обсудить третий элемент (это не влезло в предыдущий пост), а именно: полный синтаксис агрегации выглядит как {«агрегирующий столбец1»,функция_агрегации1, тип_данных_агрегации }
Суть в том, что при группировке мы можем сразу определить тип столбца на выходе. Возьмем пример из прошлого поста:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    f=(x)=>List.Sum(x[b]),
    group = Table.Group(from, "a", {"sum b",f,Int64.Type})
    
in
    group

Здесь добавлен целочисленный тип Int64.Type – мне так захотелось, поскольку столбец целочисленный. Но обращаю внимание – мышкоклацанием вы получите type number – т.е. число с плавающей точкой (судя по всему автоматически определяется только примитивный тип, независимо от того, какой столбец агрегируется), поэтому будьте внимательны и проверяйте данный момент, если группируете через интерфейс.
Аналогично для таблицы:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    f=(x)=>Table.AddIndexColumn(x,"i",1,1),
    group = Table.Group(from, "a", {"tmp",f, type table [b = Int64.Type,c = Int64.Type,i = Int64.Type]}),
    to = Table.ExpandTableColumn(group, "tmp", {"b", "c", "i"})
in
    to

Обращаю внимание – тип в данном случае задан не только для исходных столбцов, но и для добавляемого столбца индекса "i", т.е. типизируем мы конечный результат, что удобно и не может не радовать.

Ложка дёгтя состоит в том, что если вместо ExpandTableColumn вам нужно использовать Combine - данные о типах слетают (почему - подробно расписал Максим Зеленский тут), но это дело поправимое - просто переносим типизацию на следующий шаг:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
    f=(x)=>Table.AddIndexColumn(x,"i",1,1),
    group = Table.Group(from, "a", {"tmp",f}),
    to = Table.Combine(group[tmp], type table [a = text, b = Int64.Type,c = Int64.Type,i = Int64.Type])
in
    to

Вуаля - все работает как надо, таблица на выходе типизирована, как мы и хотели.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.Group – Часть 2. Четвертый аргумент
#АнатомияФункций – Table.Group
Всем привет!
Закончили с агрегациями - переходим к четвертому аргументу. Здесь не буду вас долго утомлять:
Во-первых, он есть
Во-вторых, возможны всего лишь два варианта – GroupKind.Global и GroupKind.Local
Если вы никогда не писали четвертый аргумент, имейте в виду, что вы пользовались GroupKind.Global
Разница состоит в том, что Global объединяет все значения с данным ключом в одну группу, а Local – все последовательно идущие значения с ключом в одну группу.
Когда нам это надо? Например, вы группируете по месяцам, но не хотите, чтобы январь 2021 сгруппировался с январём 2022, или идёт сменная/вахтовая работа, и вам важно понять выработку за каждую вахту:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("zZe7agNBDEX/ZWsL9JjZh78jXZwigXyCK+N/t2aNiZ3CXJAKwSC2GC2n2KO9+rxMpzM3a3vlUZven6fjpKxKLH4+mI/7mQ73Bn1q6P/a3l+Ql1e0n73y322Tvf4+9Xw/Xi3XAwRsXATYIGAlXYsAdwjYSLciwAsE3EiXIsAbBNxJ5yLAglk3k7YqxJh2C6klEmuEGPNuJc0cxSFiTLyNJHNShIgh84RJModxhFgh80RIMmdFiBgyT/z0KsSAeTYyhWTOish0U8A8G6FCqsQ2BcyzkSpEixAbYJ6NWCFlkiZgno1cwVWSm2Hmzf6jrkKMmbc4dJHpZph5q3/KRYgbZt7mA64KMWSe54rUNTpEDJnnxKl7dIgYMs+TUOoiHSKGzPMklLqYRoi7XL9u"),Compression.Deflate))),
    f=(x)=>[a=(y)=>Text.From(Date.From(Text.Split(y,"T"){0})),
            b = a(List.Min(x))&" - "&a(List.Max(x))][b],
    to = Table.Group(from, "вахта", {
                                    {"период",(x)=>f(x[дата]), Text.Type},
                                    {"выработка",(x)=>List.Sum(x[выработка]), Int64.Type}
                                    },
                                    GroupKind.Local)
in
    to

Просто сравните выполнение запроса с четвёртым параметром и без.
Ещё обращаю внимание на шаг f – функции можно упаковывать в функции – почему бы и нет, компактно и надеюсь наглядно.
Ну и надо сделать ещё пару замечаний - GroupKind.Global и GroupKind.Local – это НЕ функции, а константы – в чём легко убедиться выполнив Number.From(GroupKind.Global) и Number.From(GroupKind.Local) – выдаст 1 и 0 соответственно. Т. е. если вы относитесь к людям, которые верят, что чем короче код, тем он быстрее – заменяйте громоздкое выражение на числовое (но вообще не рекомендую – значения констант быстро забываются, но помнить о них надо – на просторах интернета встречал использование числовых значений, теперь вы будете знать, что это за нолик).
Ну и самое главное – сам по себе GroupKind.Local не особо интересен, вся его мощь просыпается, когда мы дополнительно пишем пятый аргумент – о нём мы и поговорим в следующей части.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.Group – Часть 3.1 Пятый аргумент - основной пример и описание
#АнатомияФункций – Table.Group, #buchlotnik
Всем привет!
Нам осталось сталось самое интересное. Мы уже разобрались тем, что агрегации при группировке могут быть любыми, их удобно делать с помощью функций, выяснили, что можно идти последовательно по строкам, используя GroupKind.Local. А теперь возникает вопрос – что если в столбце могут быть каки-то метки (разные) и мы хотим группировать по каким-то значениям из списка, или мы хотим сгруппировать записи хронологически по неделям (можно, конечно, сделать допстолбец с номером недели, но мы хотим как-то проще) или надо ориентироваться на разницу между строками – группировать только пока показатель растет и т.п. Думаю вы догадались, что так МОЖНО. Осталось только разобраться как.

Пятый аргумент – comparer – это функция от двух аргументов, возвращающая 0 или 1. В качестве аргументов выступают значение, с которого начинается группа и текущее значение - state и current (s и c, x и y – кому как больше нравится). Соответственно, пока функция возвращает ноль – строки объединяются, как только единицу – начинается следующая группа. И это важный момент – нам нужны именно 0 и 1.
Пример возьмем общий, в дальнейшем шаг from я не буду заново повторять и саму агрегацию возьмем самую простую {"tmp", each _ } - чтобы просто был виден результат группировки:

let
   from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("5ZrLboMwEEX/hXUiecZjXr/SdtG8VlF3WVX99xJDhBsaehtsC7AUWSRge8KRDWfsl8/s9aJEiy3VtRRuj7M6Y8W8VdR8so29TnJ7rrTluy13WU3tSX205cmWxl6unGadqnJqGrdfD/0Feucci9OD6jrv6rLTl+7aEW1LGgRQOr0cb+3YFmR3H6GmvuWul1sAtfqlqnECkayuzMa9l8e+gTY6ve/jaiMS7sInezHZwFltlWpu+NcGQ8OjaD4u5/PTdP64OXPgQgiY3CMYhsHopMFw7BGjYTCSNBiNgBGPYAQGY8KBYW6DWwoAn1OWgQHkSY8MQcCUHsHkMJhiFAyvmYqJTaWAqZRJD5ccAENKeSRTwmSqWZOh+7q6msLBRH6eVCgGUuEwLMYcITzV83haMLrHA6sjjVu9XvPkVSBUCo9UYG+ktIW+DDybDcDA3khpC30FgJn0uB+QgcWRxo1e1oyFVOwBA+skBfT5JZCh2GRgn6S0RZ84NhnYKWnc9Ke9Mu/dfzioeHA6LB42FdtuIFb+NLOt3iBr3AVFFjANsBjLwYbUBM15QAmXnXmnBILz0VGH0RUNbDwcME2wBDQS2EUHZGDl4fEMgVk1FoNgYY9YYN/htFMEBKXUPC4JEKw8PO8cgedXsyLySibBgsMB1/iX804G8fE5TmDN4cRTA2XstTOC1854PDeQr5pLFXvEwGtpHDAxsAAyrCI/axj2f057GwAjWzC9zmUMSz+nLf0cehPmzzyn/Un+tVFDB1T/GW35w0D4XG5unitv3w=="),Compression.Deflate))),
   group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local, (s,c)=>if c="успешно" then 0 else 1)
in
   group

Обращаю внимание – просто логическое выражение, возвращающее true или false, воспринято не будет, поэтому была использована конструкция if then else. Другое дело, что чаще используют другой вариант:

group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local,(s,c)=>Number.From(c<>"успешно"))

Получим то же самое. Обратите внимание, что Number.From() не является обязательной функцией, не важно как именно были получены 0 и 1, просто с ней работает немного быстрее, чем через if, но вы можете писать как вам удобнее.

Table.Group – Часть 3.2 Пятый аргумент - варианты использования
#АнатомияФункций – Table.Group

Освоили базу, теперь пройдёмся по вариантам –
Группировать по любому ненулевому значению
group = Table.Group(from, "цикл", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c<>null))
Группировать по конкретному значению

group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local, (s,c)=>Number.From(c="сбой"))

Группировать по значению из списка

group = Table.Group(from, "операция", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(List.Contains({"наладка","ТО"},c)))

Группировать по условию

group = Table.Group(from, "выход", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c<95))

Смысл, думаю, понятен. Но что же со state-том? Мы его не используем – он, что совсем не нужен? Нужен! Ой как нужен! – группируем, когда отличается первые/последние/любые в середине символы в значении:

group = Table.Group(from, "документ", {"tmp", each _}, GroupKind.Local, (s,c)=>Number.From(Text.Start(s,3)<>Text.Start(c,3)))

Группируем, если разница между первой и последней строкой в группе превысила определенное значение

group = Table.Group(from, "выработка", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c-s>50))

Ещё интересный момент – группировать-то можно и по нескольким столбцам:

group = Table.Group(from, {"дата","цикл","выработка"}, {"tmp", each _},GroupKind.Local, (s,c)=>Number.From(c[цикл]<>null)) 

В этой ситуации при группировке вы сохраните несколько столбцов, причем значения в них будут по первой строке в группе, а проверяется, по сути, только поле цикл.
И вот тут обращаю внимание – если второй аргумент в группировке - "цикл" – то и в формуле мы просто ориентируемся на значение (пишем c<>null), а если вы намышкоклацали {"цикл"} – то (включаем логику), это список полей, а значит на выходе мы получим… запись. И обращаться уже нужно c[цикл]<>null – вот так, и больше не спрашивайте, почему я обычно не пишу фигурные скобки вопреки «стандартному» виду.

Напоследок хочется сказать, что если у вас получилась сложная функция сравнения – не стесняйтесь – дайте ей имя и поместите в отдельный шаг, как мы уже неоднократно делали:

f=(x,y)=> [ a=(z)=> Text.Start(z,3), 
            b =Number.From(a(x)<>a(y))][b], 
group = Table.Group(from, "документ", {"tmp", each _}, GroupKind.Local, f)

На этом на сегодня, думаю, закончим.
Главный вывод: Table.Group – это функция ПЯТИ аргументов, и хоть два последних и не обязательны, но в умелых руках бывают крайне эффективны – так что пользуйте их на благо светлого будущего человечества.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Table.ReverseRows и причём тут группировка?
#АнатомияФункций – Table.ReverseRows
Всем привет!
В продолжение темы группировки – недавно была задачка. Сложность в том, что сгруппировать надо по ключевому слову в ПОСЛЕДНЕЙ строке группы. По ссылке можно посмотреть отличное решение от Пуха – допстолбец с индексом, условный столбец, FillUp, потом группировка – в общем все логично и берите на вооружение - у такого подхода есть масса практических приложений.

Но хочется же сделать финт ушами - иначе бы поста не было. Поэтому:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("5ZXBCoMwDIZfZfS8QZPGVn2VbYc59Q12Gnv3aToxMBA8JBQECY0m+eNHSq5vd3t5Appt8LMlzOcTO8DOwDaxFaGhZ1tz5Ohahx7x4mF63Hm7cGCHOj6PQqQThattEVxEkmhJCv7eTzkcNqxdkBcpSQjkr8+l8iiCc3pcO8twCFwbPmclkGgBMuwE+RAy/R9UIb8bJKiBDBYgqZiJjGogyQJkVQxIVANZWYCMB7ja0QJkKmYi9ZZNsgBZH2AiawuQTTETqbdsGgOQ4IsBqbZs1n/UBAkFXe37Fw=="),Compression.Deflate))),
    typ = Table.TransformColumnTypes(from,{{"Дата выполнения", type date}, {"Дата создания", type date}, {"Процесс", Int64.Type}, {"Задача", type text}}),
    tbl = Table.ReverseRows(typ),
    group = Table.Group(tbl, {"Процесс","Задача"},
                        {{"Дата создания", (x)=>List.Min(x[Дата создания]), Date.Type},
                        {"Дата выполнения", (x)=>List.Max(x[Дата выполнения]), Date.Type}},
                        GroupKind.Local,
                        (s,c)=>Number.From(c[Задача]="Контроль")),
    to = Table.ReverseRows(group)
in 
    to

tbl – используем Table.ReverseRows – строчки меняются с ног на голову – и нужно группировать по ключевому слову… в ПЕРВОЙ строке группы
group – группировка, группируем по «Процесс» и «Задача» - сразу по обоим, чтобы процесс не надо было вынимать из сгруппированной таблицы, GroupKind естественно Local, ну и в функции не забыли указать, что интересует нас только поле «задача»
to – и снова ReverseRows, чтобы вернуть порядок строк в исходный
Вот и всё. Редко пользуюсь реверсами, но по скорости в данной ситуации этот вариант выигрывает кратно.
Курите стандартную библиотеку – там много интересного.

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Чем заменить Table.AddColumn? – эпичный челлендж
#АнатомияФункций – Table.FromList и многие другие
Всем привет!
Мы уже разбирали, что на списках быстрее ))). Так вот, история получила продолжение. Сегодня в чате состоялся челлендж. @MaximZelensky – спасибо за идею и организацию!
Суть задачи – повторить в точности результат выполнения функции Table.AddColumn. В точности означает, что нужно не просто получить таблицу с новым столбцом, это должна быть функция, принимающая те же аргументы, что и штатная, при этом типы столбцов у возвращаемой таблицы в любой ситуации должны получиться такими же, как и при работе штатной функции. По ссылке выше можете посмотреть результаты – все (все, Карл!) участники оказались быстрее, чем штатный вариант почти в два раза! (важное дополнение - в ходе пристальных тестов выяснено, что стандартная функция работает шустрее, если её дополнительно обернуть в Table.Buffer, кастомная таких изысков не требует, что приятно).
Ну а лучшим по скорости и точности оказался, разумеется, гибрид идей – его и разберем:

(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table =>
[
    //кусок от buchlotnik
    rec=Table.ToRecords(table),
    gen=(x)=>Record.FieldValues(x)&{columnGenerator(x)},
    newtyp = if columnType = null then Type.FunctionReturn(Value.Type(columnGenerator)) else columnType,
    //кусок от Maxim Zelensky
    TypeAsRecord = Record.AddField(Type.RecordFields(Type.TableRow(Value.Type(table))), newColumnName, [Type = newtyp, Optional = false]),
    NewTableType = type table Type.ForRecord(TypeAsRecord, false),
    //и итог послечелленджевого обсуждения
    to = Table.FromList(rec,gen,NewTableType)    
][to]

()=> обратите внимание на аргументы – они просто скопированы из справки - мы играем по правилам 🙂
rec – превратили таблицу в список записей - записи важно сохранить, потому что , columnGenerator штатной функции работает с полями записи
gen – функция генерации строки таблицы – получает на вход запись, превращает её в список значений и добавляет к нему результат выполнения columnGenerator над этой записью
newtyp – проверяем, передан ли четвертый аргумент, если да – используем его, если нет – определяем тип данных, возвращаемых columnGenerator

Шаги от Максима:
TypeAsRecord – получаем типы столбцов исходной таблицы в виде записи и добавляем к ней информацию о новом столбце и его типе
NewTableType – создаем новый тип таблицы с информацией уже обо всех столбцах
(Максим, ещё раз спасибо, чего я там нагородил через Table.Schema и #shared – прям стыдно 😳)

Ну и осталось немного:
to – генерация новой таблицы из списка записей с использованием функции Table.FromList: gen - второй аргумент должен возвращать список - наша функция этому условию полностью удовлетворяет, третьим аргументом сразу передаем полученной таблице информацию о типе
(кто забыл как такое читать - [ ][to] - вспоминаем)

Всё! Задачка решена. Просто, элегантно, и чёрт побери, шустро!
Курите решение, читайте спецификацию, надеюсь на следующем челлендже участников будет больше 😉

Надеюсь было полезно.
Всех благ!
@buchlotnik

List.Sort – пользовательская сортировка и её альтернативы
#АнатомияФункций – List.Sort, Value.Compare
Всем привет!
Сегодня обсудим сортировку списков. Читаем справку:

List.Sort(list as list, optional comparisonCriteria as any) as list 

Самое простое – просто сортировать )))

List.Sort({8,3,4,11,6})
List.Sort({"a","f","b","z","m"})

По умолчанию сортировка идёт по возрастанию. Если нужно, порядок можно поменять вторым аргументом

List.Sort({"a","f","b","z","m"},Order.Descending)

Обращаю внимание - Order.Ascending и Order.Descending – это числовые константы, которые могут быть заменены на 0 и 1 соответственно.
Ну, ОК. Второй аргумент ещё может быть функцией от одного аргумента, которая вычисляет ключ для сортировки. Зачем оно надо? Да вот сравните

List.Sort({"8","3","4","11","6"})
List.Sort({"8","3","4","11","6"},(x)=>Number.From(x))

В первом случае «11» окажется на первом месте – оно же начинается с 1, во втором – список будет отсортирован по возрастанию числовых значений.
Функция может быть дополнена порядком сортировки:

List.Sort({"8","3","4","11","6"},{(x)=>Number.From(x), Order.Descending})

Зачем вообще сортировать числа в текстовом формате? Да просто обычно сортируется что-то такое:

List.Sort({"8 abc","3 cde","4 efg","11 ghi","6 ijk"},(x)=>Number.From(Text.BeforeDelimiter(x," ")))

Как видим, функция-то может быть любой сложности.
Пока всё достаточно просто, но теперь давайте обсудим последний вариант – вместо функции одного аргумента и порядка сортировки можно использовать функцию двух переменных, которая возвращает -1, 0 или 1 в зависимости от того, первый аргумент меньше, равен или больше второго. Для этого нам рекомендуют использовать Value.Compare. Прямой порядок сортировки будет выглядеть так

List.Sort({"a","f","b","z","m"},(x,y)=>Value.Compare(x,y))

Но как теперь задать обратный порядок? (я редко критикую справку, но это именно тот случай):

List.Sort({"a","f","b","z","m"},(x,y)=>Value.Compare(y,x))

Надеюсь, разницу не нужно комментировать. Но прокомментировать надо, что на самом деле от нас требуется не -1, 0 и 1; а отрицательное, ноль или положительное целое число.

List.Sort({8,3,4,11,6},(x,y)=>x-y)

Так тоже работает (разница двух чисел собственно и показывает которое из них больше), а для обратного порядка

List.Sort({8,3,4,11,6},(x,y)=>y-x)

С дробными код чуть напряжнее

List.Sort({8.2,3.1,4.6,11.4,6.9},(x,y)=>Number.Sign(y-x))

Тут мы определяем знак полученной разницы - это работает быстрее чем Value.Compare, правда медленнее чем обычный Order.Descending. Тогда зачем оно надо? Ну вот был в чате пример (здесь упрощённый):

let 
f=(x,y)=>[g=(x)=>List.Transform(Text.Split(Text.BeforeDelimiter(x," "),"."),Number.From),
            a=g(x),
            b=g(y),
            c= if a{0}=b{0} then a{1}-b{1} else a{0}-b{0}
         ][c]
in 
List.Sort({"1.6 abc","3.3 cde","3.12 efg","11.2 ghi","1.24 ijk"},f)

Во-первых, как уже говорилось - сложные преобразования выносим в отдельную функцию. Далее, что тут происходит - получили номер пункта, получили отдельно число до точки и после, при сортировке сравниваем числа до точки, а если они равны – числа после точки. Работает, но надо сказать, что не быстро, потому что такой подход порождает кучу вычислений (функция вызывается для сопоставления каждой пары значений). Гораздо лучше превратить всё в таблицу и отсортировать ее по двум столбцам. Но если вам всё же нужен список – сделайте это преобразование внутри функции:

let 
f=(x)=>[g=(y)=>List.Transform(Text.Split(Text.BeforeDelimiter(y," "),"."),Number.From)&{y},//функция получает на вход значение и на выход подает список {число до точки,число после точки, исходное значение}
        a = Table.FromList(x,g,{"a","b","c"}),//собрали таблицу
        b = Table.Sort(a,{"a", "b"})[c]//отсортировали по a и b, а на выход подали c
       ][b]
in 
f({"1.6 abc","3.3 cde","3.12 efg","11.2 ghi","1.24 ijk"})

Как-то так, сортировать можно по-разному, но Value.Compare – не лучший выбор. Есть достойные альтернативы, которые я и продемонстрировал.

Надеюсь, было полезно.
Всех благ!
@buchlotnik