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

Дополнение, которое не влезло в объем основного поста:
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

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

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

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

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 – Часть 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.FromList + GroupKind.Local – шустрое подобие регулярок
#АнатомияФункций - Table.FromList, GroupKind.Local

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

let
    ld = List.Buffer({"0".."9"}),
    ldp = List.Buffer(ld&{"(",")"," ","-"}),
    
    f=(x)=>[a = Table.FromList(Text.ToList(x),(x)=>{x,if List.Contains(ldp,x) then 0 else 1},{"y","z"}),
            b = Table.Group(a,"z",{"i",each Text.Combine([y])},GroupKind.Local)[i],
            c = List.Transform(b,(x)=>Text.Select(x,ld)),
            d = List.Select(c,(x)=>x<>"")][d],
       
    lst = { " +7 (495) 617-61-16б доб. 1503",
            "тел: +7-913-728-64-87 Владимир",
            "(4152), доб. 23-81-47 +7962 215 17 17",
            "8 (423) 2 60-84-84, 143(внутренний)",
            "8 (495) 229-49-69, доб. 114",
            "+7 (383) 286-87-08 ext.24120"},
    to = Table.FromList(lst,(x)=>{x}&f(x),4)
in
    to

lst – наш источник, для простоты в виде списка – задача вынуть номера телефонов
to – собрали из списка таблицу с использованием функции f

Ну а теперь о функции.
Для начала заведём два вспомогательных списка – ld и ldp
ld – список цифр (именно символов, кавычки не забываем) - это список символов, которые нас интересуют в конечном результате
ldp – к списку ld добавили символы, которые могут встречаться в номере телефона – скобки, пробелы, дефис - это список символов, которые могут быть объединены в одну группу
Обращаю внимание – списки вынесены в отдельные шаги и помещены в List.Buffer – это важно для ускорения работы

Теперь сами шаги функции:
a – собираем таблицу из списка символов анализируемого значения. На выход получим таблицу из двух столбцов – y это сам символ, а z – будет нулевым, если символ может содержаться в номере и единица – если не может
b – группируем по z, локально, пятый аргумент не пишем (подробно про пятый аргумент разбиралось тут), агрегируем в текст. Таким образом все потенциальные символы из номера телефона будут сгруппированы в отдельные текстовые значения
c – чистим полученные тексты, оставляя только символы из списка ld
d – выбираем не пустые тексты
Собственно, всё. Тонкую настройку работы функции можно осуществить меняя списки ld и ldp, экспериментируйте )))

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

Table.Group – Часть 3.3 Пятый аргумент + GroupKind.Global
#АнатомияФункций – Table.Group

Всем привет! Меня тут уличили в неполноте раскрытия темы, поэтому вынужден дать развернутый комментарий. Итак, мы уже разбирали пятый аргумент у Table.Group и я тогда написал, что его мы используем в сочетании с GroupKind.Local. Но на самом деле при большом желании можно примотать и для GroupKind.Global.
Разберем пример:

let
    tbl = #table({"Date","Num"},{{#date(2021,1,1),1},{#date(2020,1,1),2},{#date(2021,3,1),3},{#date(2020,1,12),4},{#date(2022,1,1),5}}),
    f=(x,y)=>Value.Compare(Date.Year(x),Date.Year(y)),
    to = Table.Group(tbl,"Date",{"Sum",(t)=>List.Sum(t[Num])},GroupKind.Global,f)
in
    to

tbl - имеем таблицу – дата/значение и хотим сделать группировку по годам.
to – собственно делаем группировку, сразу в один шаг, с использованием функции f
f – пятый аргумент как всегда пишем от двух аргументов. В случае GrouKind.Local мы возвращали 0 или 1 в зависимости от того, надо начинать новую группу или нет. Но в том-то и дело, что там строки анализируются последовательно, а вот в случае глобальной группировки функция должна определить именно порядок переданных ей аргументов. Самое простое – используем Value.Compare - данная функция вернёт -1 0 или 1 когда x меньше, равен или больше y соответственно.

Ну и действительно в таком варианте группировка будет корректной. Другое дело, что даты в столбце Date останутся датами, хотя группировка сделана именно по годам. А главное, что мы уже обсуждали в рамках пользовательской сортировки, Value.Compare – медленная функция. И с ростом объема данных «тупить» будет всё сильнее.
Можно ли это исправить? Ну в целом да – перепишем функцию сравнения:

f=(x,y)=>Date.Year(x)-Date.Year(y)

Вместо сравнения мы рассчитываем разницу и она будет меньше, равна или больше нуля в зависимости от соотношения между x и y. В таком варианте получим прирост по скорости процентов так на 20 – 25 (замерял на таблице в 100k строк) – что, конечно, ощутимо.
И кому-то может показаться, что это вот прям круто – группировать в один шаг, но есть маааленькое НО, из-за которого я данный подход не описывал. А состоит оно вот в чём:

let
    tbl = #table({"Date","Num"},{{#date(2021,1,1),1},{#date(2020,1,1),2},{#date(2021,3,1),3},{#date(2020,1,12),4},{#date(2022,1,1),5}}),
    tr = Table.TransformColumns(tbl,{"Date", Date.Year}),
    to = Table.Group(tr,"Date",{"Sum",(t)=>List.Sum(t[Num])})
in
    to

Стандартный подход в два шага
tr – преобразовали даты в годы
to – сгруппировали по преобразованному столбцу
На той же выборке работает в два раза быстрее.

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

Поэтому ещё раз обращаю внимание – сам по себе расширенный редактор сильно развязывает руки и да, у пишущего появляется много «можно». Но можно НЕ значит нужно. Дело всегда не в количестве строк кода, а в реальной вычислительной эффективности. Пробуйте, экспериментируйте, но всегда проверяйте полученный результат. В конце концов и одной строчкой кода, при должном умении, можно вызвать переполнение стека.

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