Нормализация таблицы с повторяющимися заголовками
#АнатомияФункций - 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

Всем привет!
С сегодняшнего дня начинаем новую рубрику #ГостевойТанк - здесь будут публиковаться посты наших коллег и друзей на злободневные темы PQ

Проблема ёжиков и Алён в сортировке текста
#ГостевойТанк - дополнение от @MaximZelensky

Дополню примеры из поста про List.Sort вот таким:

    List.Sort(
        {
          "Алена", 
          "АЛЕНА", 
          "АЛЁНА", 
          "Алёна"
        } 
    )

Обычно заглавные буквы старше, чем строчные, а в алфавите Е идёт перед Ё, поэтому мы ожидаем такой результат сортировки:

    {"АЛЕНА", "АЛЁНА", "Алена",  "Алёна"}

Однако PQ считает по-другому:
{
"АЛЁНА",
"АЛЕНА",
"Алена",
"Алёна"
}
Как видим, первые два слова стоят в неожиданном порядке. Причина простая - составители кодовой таблицы (где символу сопоставлен числовой код) для кириллицы сначала забыли про букву Ё, и затем в целях обратной совместимости (чтобы не порушить уже разработанное с помощью этой таблицы ПО) ее пришлось вставлять на свободные места - она должна была быть явно старше, чем строчная буква, но места между заглавными и строчными в кириллице не осталось. Поэтому заглавную "Ё" впихнули до "А", а строчную "ё" - после "я". Этой истории уже десятки лет и никто ничего исправлять не будет.
В указанном примере такими тонкостями можно пренебречь, однако давайте отсортируем ежей, ужей и арбузы:
= List.Sort( {"Арбуз", "Ёж", "ежиха", "ёжик", "ежище", "ужик", "Уж" })
Результат будет уже не таким приятным:
{
"Ёж",
"Арбуз",
"Уж",
"ежиха"
"ежище"
"ужик",
"ёжик"
}
Попытка выполнить сортировку в правильном порядке на основании числовых кодов символов здесь возможна (кто смелый - попробуйте и поделитесь результатом), но весьма громоздка и, подозреваю, медленна (ведь Ё/ё может встретиться в любом месте строки, значит, сверку нужно будет производить посимвольно).

Отчасти спасение лежит в стандартной функции-компараторе из библиотеки PQ: Comparer.FromCulture. Эта функция тоже имеет два аргумента, но совсем не те, что Value.Compare:

* указание локали, в которой должно производиться сравнение в стандарте .Net Framework (например, "ru-ru")
* логический параметр, позволяющий игнорировать регистр символов при сортировке). По умолчанию он равен false

А результат вызова этой функции - неименованная функция-сравниватель, которая уже принимает два аргумента для сравнения (как Value.Compare или ваша кастомная функция сравнения).
Сортировка списка с такой функцией выглядит вот так:

= List.Sort( {"Арбуз", "Ёж", "ежиха", "ёжик", "ежище", "ужик", "Уж" }, Comparer.FromCulture("ru-ru"))

/*
{
"Арбуз",
"Ёж",
"ёжик"
"ежиха",
"ежище",
"Уж",
"ужик"
}
*/

Обратите внимание:

* в русской локали сортировка идёт по такому принципу: А, а, Б, б, .... , Е, е, Ё, ё, , ... Я, я.
* Почему-то Ё старше, чем Е, но хотя бы младше, чем Д. (предположения и догадки по этому поводу - в комментариях)

@MaximZelensky

List.Accumulate – как написать «пустой» второй аргумент
#АнатомияФункций – List.Accumulate

Всем привет!
Давно зрело и вот нашёлся информационный повод.
Итак, возвращаемся к нашему List.Accumulate (помним, что медленный, но иногда удобный).
List.Accumulate(list as list, seed as any, accumulator as function) as any

Второй аргумент – seed - опорное значение, которое может быть любым, но сегодня мы его хотим сделать пустым (или нулевым, но не null чтобы всё работало).
Допустим нужно просуммировать нечётные числа в списке:
List.Accumulate({1,2,3,4,5},0,(s,c)=>if Number.IsOdd(c) then s+c else s)
Тут у нас простая арифметика, поэтому seed - 0 - просто ноль, с которым всё суммируем

Собираем через пробел значения из списка, чья длина больше или равна трем символам
List.Accumulate({"лучшие","запросы","оптом","и","в","розницу"},"",(s,c)=>if Text.Length(c)>2 then Text.Combine({s,c}," ") else s)
На выход подаём строку, поэтому seed – ""- пустая строка, с которой всё конкатенируем

Аналогичная логика со списками – опять выбираем нечётные числа, но теперь списком:
List.Accumulate({1,2,3,4,5},{},(s,c)=>if Number.IsOdd(c) then s&{c} else s)
Здесь опорное значение должно быть списком, к которому мы всё добавляем, поэтому seed – {} – пустой список (также обращаем внимание на синтаксис добавления нового элемента к существующему списку – скобочки там не просто так)

А вот ситуация c записями (с ней сталкиваемся, например, при парсинге, когда значения нескольких полей идут как одно строковое):
List.Accumulate({{"длина",100},{"ширина",50},{"высота",10}},[],(s,c)=>Record.AddField(s,c{0},c{1}))
На вход подается список списков (поле – значение), но на выходе хотим запись – значит seed – [ ] – пустая запись. Также обращаю внимание, что добавление осуществляем не через конкатенацию, а через функцию (проблема аналогично той, которую описывал ранее). Ну и помним, что при аккумулировании списка списков – c – это тоже список и можно обращаться к отдельным его элементам, как в примере.

Ну и всё это вроде и так понятно, осталось только разобраться с таблицами. Вчера в миру был пример. Нужно получить полное декартово произведение – как это делать через джоин уже описывал Пух. Вопрос теперь в другом – а как сджойнить несколько таблиц? Смотрим код:
let
lst={"Город","Год","Продукт"},
f=(x)=> Excel.CurrentWorkbook(){[Name=x]}[Content],
g=(x,y)=>Table.Join(x,{},f(y),{}),
to = List.Accumulate(lst,#table({},{{}}),g)
in
to
По шагам:
lst - список таблиц (можно его и запросом получить, я в курсе, но было бы не так наглядно)
f – вспомогательная функция – подключение к конкретной таблице (оптимизаторам молчать! можно вообще без имён, одним подключением, отфильтровав только таблицу самого запроса... ДА МОЖНО и закрыли тему)
g – функция для аккумулятора (мы же помним, что функции стоит выносить) – тут к таблице x (кому удобнее – пишите s или state – это не принципиально), джойнится таблица f(y) – т.е. таблица из файла с именем y
to – ну и сам аккумулятор – смотрим на seed - #table({},{{}}) – примерно так выглядит пустая таблица – пустой список имён столбцов и пустой список списков значений строк – это не единственный вариант - мне просто нравится, когда много фигурных скобок 😉
Собственно всё – к пустой таблице последовательно джойнятся все необходимые - задачка решена.
Так что аккумулируя что-то с нуля определитесь с типом данных на выходе и выбирайте соответствующий seed - тогда всё получится.

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

LinearTrend – функция, которой нет, но очень хочется
#АнатомияФункций – статистические функции
Всем привет!
Очередной раз в чате был задан вопрос про статистические функции в M.
Они есть, все в категории List: .Average, .Count, .Covariance, .Median, .Min, .Max, .Mode, .Modes и .StandardDeviation. Набор стандартный, вполне достойный, но уж больно часто спрашивают про оценку трендов – а вот её не подвезли. Я не собираюсь никого мучать линейной алгеброй и решением задачи в общем виде в матричной форме, поэтому просто приведу вариант решения для парного линейного случая:

LinearTrend = (x as list, y as list) as record =>//предполагается y = a + b*x
[   
    x2 = List.Transform(x,(y)=>y*y),
    y2 = List.Transform(y,(x)=>x*x),
    xy = List.Transform(List.Zip({x,y}),(x)=>x{0}*x{1}),
        
    n = List.Count(x),
    sumx = List.Sum(x),
    sumy = List.Sum(y),
    sumx2 = List.Sum(x2),
    sumy2 = List.Sum(y2),
    sumxy = List.Sum(xy),
    
    a = if n>2 then (sumx2*sumy-sumx*sumxy)/(n*sumx2- sumx*sumx) else y{0},//ОТРЕЗОК()
    b = if n >1 then (n*sumxy-sumx*sumy)/(n*sumx2- sumx*sumx) else 0,//НАКЛОН()
    syx = if n>2 then (sumy2-a*sumy-b*sumxy) else 0,
    sy = sumy2-sumy*sumy/n,
    s = if n >2 then Number.Sqrt(syx/(n-2)) else 0,//СТОШYX()
    R2 = 1-syx/sy,//R^2 - функции нет, на графиках есть 
    r = Number.Sign(b)*Number.Sqrt(R2)//КОРРЕЛ()
][[n],[a],[b],[s],[R2],[r]]

По шагам:
x,y – абсцисса и ордината соответственно – списками
x2,y2,xy – для решения задачи нам требуются квадраты по обеим координатам и попарные произведения
n – число элементов (взято по абсциссе, в предположении, что списки одинаковой размерности)
sumx, sumy, sumx2, sumy2, sumxy – суммы – величин, их квадратов, попарных произведений
a,b – пересечение и наклон соответственно (в комментах к коду названия соответствующих функций в Excel)
syx – остаточная сумма квадратов для модели
sy – сумма квадратов отклонений ординаты от среднего
s – остаточное стандартное отклонение
R2 – коэффициент детерминации (он фигурирует на графиках, его же вычисляет ЛИНЕЙН())
r – коэффициент корреляции – посчитан из R2, просто чтоб было, если нужен только он – не надо пользоваться этой функцией – посчитайте ковариацию – оно проще и быстрее, поверьте

Ну и всё – параметры получаем в виде записи. Вроде громоздко, но ничего сложного.
Если тема актуальна – пишите в каментах, будем двигать статистику в М.

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

Table.AddColumn и типизация столбцов – Часть 1
#ГостевойТанк - дополнение от @MaximZelensky

По мотивам и в дополнение к челленджу по переписыванию Table.AddColumn

При загрузке данных из PQ в модель данных Power Pivot в Excel или в модель данных Power BI критически важно, чтобы столбцы таблиц имели правильный тип (иначе все нетипизированные столбцы будут восприняты как текстовые, вне зависимости от их содержимого).

Ок, предположим, мы решили создать в таблице столбец с именем "new" и заполнить его единичками. Когда мы создаем новый столбец через интерфейс PQ, автоматически создается код такого вида: Table.AddColumn(TableName, "new", each 1). Новый столбец, созданный таким образом, будет иметь по умолчанию тип данных any - то есть будет нетипизирован. Так происходит потому, что функция each 1 не имеет заранее определенного типа возвращаемого значения, а априори анализировать результат расчёта для каждой строки - дорого и неэффективно.
Но что делать, если нам важен тип данных (мы хотим загрузить этот столбец в модель)? Можно ли сразу указать, какого типа этот новый столбец? Мы же знаем его заранее (1 это явно число, других вариантов результата у нашей функции нет). Для этого нас есть несколько способов.

Способ 0
После создания шага применяем преобразование типа столбца через интерфейс или в коде при помощи Table.TransformColumnTypes - наиболее прямой, но расточительный способ. В этом случае, кроме собственно указания типа для столбца, происходит принудительное преобразование значений в нем к указанному нами типу, и если по какой-то причине преобразовать значение в указанный тип нельзя, появится ошибка ячейки. Замедление производительности мы увидим уже на средних объемах. Однако есть случаи (за рамками этого поста), когда такая операция необходима и чрезвычайно полезна.

Способ 1
Мы можем явно указать тип данных, который возвращает функция-генератор. Например перепишем функцию с единичками вот так (не через диалоговое окно, а в строке формул):
(row) as number => 1
Новый столбец автоматически получит тип number - потому что так сказано в определении функции ( as number). И даже более того - если мы попробуем обхитрить PQ и вместо единичек вернуть текстовое значение, такая попытка вызовет ошибку ячейки:
(row) as number => "1" // Expression.Error: Не удается преобразовать значение "1" в тип Number
Так что, если мы точно знаем, что результат может быть определенного типа, мы можем смело использовать такой подход, и даже отлавливать ошибки типизации.
К сожалению, первый способ имеет ограничения – какие и как с ними бороться будет рассказано во второй части.

@MaximZelensky

Table.AddColumn и типизация столбцов – Часть 2
#ГостевойТанк - дополнение от @MaximZelensky

Продолжение - первая часть

Способ 2
К сожалению, первый способ имеет ограничения - мы можем использовать оператор назначения типа as только с примитивными типами - такими как text, number, date и т.п., либо их nullable версиями: nullable text, nullable number и т.п.
Однако, если мы хотим указать, что единичка - не просто число, а целое число, мы не можем написать в определении функции as Int64.Type - такая попытка вызовет ошибку шага Expression.SyntaxError: Недопустимый идентификатор типа. Проблема в том, что Int64.Type - это не примитивный (базовый) тип, а так называемый фасет (подтип), с которым не хочет работать оператор as.
В таком случае мы можем задействовать 4-й (опциональный) аргумент функции Table.AddColumn - указание типа нового столбца:
Table.AddColumn(TableName, "new", each 1, Int64.Type)
В этом случае вне зависимости от того, указали ли мы, какой тип данных возвращает фукнция-генератор, новый столбец получит тот тип, который указан в 4-м аргументе - целое значение.
При этом очень важно отметить:
* Проверка соответствия указанного типа реальному типу значений не будет производиться на этапе создания столбца (ни на уровне ячейки, ни на уровне шага). Мы можем написать Table.AddColumn(TableName, "new", each 1, type text), и новый столбец будет иметь тип "Текст", но преобразование 1 в "1" не будет произведено.
* При загрузке данных в модель (как минимум в Power BI), тем не менее, будет произведена проверка соответствия типа данных столбца типу данных значений в его ячейках, и мы очень легко можем увидеть сообщение об огромном количестве ошибок при загрузке.
Поэтому использовать 4-й аргумент нужно очень аккуратно - только будучи уверенным, что тип значений в новом столбце не будет противоречить указанному.

У второго способа есть также одно важное преимущество - мы можем задавать там не только примитивные или фасетные типы, но и сложные составные типы.
Например, у нас есть столбец со списком годов и мы хотим для каждого года создать списки "первых чисел месяцев", чтобы затем развернуть эти списки в новые строки:
YearsTable = #table(type table [Year=Int64.Type], List.Zip({{2019, 2020, 2021}})),
AddMonths = Table.AddColumn(YearsTable, "MonthStart", each List.Transform({1..12}, (m) as date =>#date([Year], m, 1)))
ExpandMonths = Table.ExpandListColumn(AddMonths, "MonthStart")
Несмотря на то, что мы попробовали прописать as date во внутренней функции, это нам не особо помогло - после разворачивания списков новый столбец имеет тип any - такой тип у основной функции-генератора each. Если мы попробовали бы вместо each использовать (row)=> (по первому способу выше), то максимум, который нам был бы позволен - (row) as list =>. В этом случае тип нового столбца (до разворачивания) получил бы тип "список" (это будет заметно по иконке в названии столбца), но на этом и всё - после разворота, несмотря на то, что мы пытались задать тип date, новый (развернутый) столбец будет иметь тип any.

Здесь на выручку приходит тот самый Способ 2 - использование 4-го аргумента:
AddMonths = Table.AddColumn(YearsTable, "MonthStart", each List.Transform({1..12}, (m) as date =>#date([Year], m, 1)), type {date})
Использовав сложный составной тип type {date}, мы явно указали программе, что новый столбец содержит список дат. Теперь даже после разворачивания мы увидим, что столбец типизирован как "даты".

@MaximZelensky