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

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

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

Table.Max – или многоликий второй аргумент
#АнатомияФункций – Table.Max

Всем привет!
Сегодня в чате всплыл вопрос про Table.Max – и в личке спросили зачем я ее так странно пишу. Ответ прост – многие вещи я по привычке пишу через функции. Но вот как МОЖНО писать, думаю стоит прокомментировать развернуто.
Тестовый стенд:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("fdJJisMwEIXhq4haG+MqyXLiqzS9yNTzvA25e0RZtuu5kSCLYPh4Tv56uNKBRm7oSOO+oZN+P9NI0om4ThwzNXRJDzpu0yc9ZnegW2PcTp1snGTHfnFHdMO0B4Ps2M+D0nKGJ4RRYbDOOw6zC4s7o+vV9RvXZydxedELuqAubt4zZue7Nk2qe0Ln1Q2FvTTGMrlndKJuV9gzIV7Qsbp9Yc+EeJ2dlMLbvbXDG7opvC/9vrXDO7ocfigMmhAfCGN10IT4RNf/Pxj8Q+cQX+hC9WBMiG90vnowJsQPOqkfzBriFx3XD2YN8Ue3xzs="),Compression.Deflate))),
f = "a",
to = Table.Max(from,f)
in
to
from – исходник
f – условие определения максимума
to – результат Table.Max (обращаю внимание – на выход поступает запись – строка таблицы с максимумом по нашему условию)
Далее для простоты буду менять только шаг f .
Итак, для начала обращаю внимание в коде выше, что в отдельные шаги можно выносить не только функции, но и списки или отдельные значения, да вообще всё, что угодно (вынесение шагов обсуждалось тут).
f = "a" – просто название столбца, на выходе получим запись с максимальным значением по данному полю, причём – последнюю запись (в примере таких несколько).
Днём я написал по-другому:
f = (x)=>x[a]
т.е. написал функцию – обращаю внимание – на вход поступает строка таблицы – запись, и мы просто просим взять конкретное поле – т.е. результат будет таким же. Зачем такие сложности? Да посмотрите на столбец "e" – хотим максимальную дату, а она спрятана в текстовой строке, решение разумеется:
f = (x)=> Date.From(Text.BeforeDelimiter(x[e]," "))
т.е. мы уже не просто берем конкретное поле, но и осуществляем с ним дополнительное преобразование – вынимаем дату (можно конечно сначала соорудить допстолбец и потом брать максимум по нему, а можно вот так сразу)
Ну ок, а что если нас не устраивает вынимание последней строки? Хотим максимум по "a", если несколько значений – максимум по "b" и т.д. Да пожалуйста:
f = {"a","b","c"}
т.е. просто передаем список имен столбцов, в том порядке, в котором определяем максимум. Это важно -
f = {"a","c","b"}
даст уже другой результат (поиграйтесь с примером).

Ну и вернемся к функциям – а что если нужно вынуть дату из "e", а из полученных взять максимум по столбцу "c"? Да как бы тоже несложно:
f = {(x)=> Date.From(Text.BeforeDelimiter(x[e]," ")),"c"}
Т.е. список может состоять не только из названий столбцов, но и включать функции преобразования.

Как-то так – если в справке про аргумент написано as any – это стоит ковырнуть и возможно упростить себе программирование или жизнь )))

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

TableToBinaryText, TableFromBinaryText – или туда и обратно
#АнатомияФункций – Json, Binary и т.п.

Всем привет!
В серии постов про группировку появился пример исходника в виде текста странного вида: "i65Wiik1MDFJAZNGINI4VcnKUAcsbJwGFjaACIPZhqgKjbAotACTyWDSFCKuZGUMUQjVCVEINss4CaHJxAxMJilZmdTGAgA="
Вроде что это, как и откуда уже обсудили в чате, но раз вопросы в личку продолжаются закреплю здесь.
Постановка задачи
Необходимо дополнить пост в телеграме примером таблицы в несколько столбцов и хотя бы десятком строк. Писать такое в явном виде (например, через Table.FromRecords) долго, муторно и вообще может не влезть в сообщение, а прикладывать файл-пример также не очень – у нас здесь пользователи не только, да и не столько excel, сколько pbi, плюс скачивать файлы на работе не у всех есть возможность
Логика решения
Чтобы данные не занимали кучу символов их следует сжать. PQ умеет сжимать только бинарники, значит из таблицы нужно получить бинарник. Напрямую это действие осуществить нельзя, поэтому таблицу надо сначала преобразовать в текстовый формат. Из всего имеющегося в арсенале (мы помним, что таблицу можно рассматривать как список записей) лучше всего подходит Json - текстовый формат, который поддерживает записи. После сжатия на выходе будет бинарник, а нам надо его в воткнуть в сообщение – значит последним этапом мы должны будем получить строковое представление бинарника
Реализация
Функция превращения таблицы в сжатый текст:
TableToBinaryText = (tbl as table) as text=>[
json = Json.FromValue(tbl),
bin = Binary.From(json),
compr = Binary.Compress(bin,Compression.Deflate),
txt = Binary.ToText(compr)][txt]
json – преобразовали таблицу в json (размеченный текст)
bin – получили бинарное представление для текста
compr – осуществили сжатие (выбрано Compression.Deflate – просто потому, что так сжимаются ZIP-ы)
txt – получили строковое представление сжатого бинарника

Ну и процедура получения таблицы – это те же действия в обратном порядке:
TableFromBinaryText = (txt as text) as table =>[
bin = Binary.FromText(txt),
dec = Binary.Decompress(bin,Compression.Deflate),
json = Json.Document(dec),
tbl = Table.FromRecords(json)][tbl]
bin – строку обратно в бинарник
dec – бинарник вернули в несжатое состояние
json – получили из бинарника json – он отобразится как список записей
tbl – собрали из записей таблицу

Вот, собственно, и всё.
Какие практические задачи собрались решать вопрошающие не знаю. По мне так это просто пример, как можно решать свои частные задачи, используя имеющийся арсенал конкретного языка. С другой стороны, если начнете копать в сторону Binary.*, функции распаковки документов word или добывания информации о разметке в excel могут перестать быть китайской грамотой – там же всё строится на распаковке zip-архивов, коими и являются .docx и .xlsx. Так что да – частная задача может вести к изучению весьма интересных функций.

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

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

Ну а от себя добавил поисковик - теперь можно искать все сообщения, в которых упоминается конкретная функция, просто написав её название (ну точнее можно искать любой текст, просто искать функции продуктивнее)

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

Источники мудроты.
#ПолезныеСсылки

Всем добра! Выпрыгнул из сумрака и привел к общему знаменателю старый пост (который удалил) чтобы источники лучше искались нашим ботом, а также добавил ссылочку на Бена нашего Грибаудо.
PQ:
01. Спецификация языка PowerQuery M - это фундамент, букварь, без него дальше тяжело;
01a. M language specification - оригинал этого фундамента на английском (@buchlotnik прям настаивает на чтении этой версии);
02. Справочник по языку формул Power Query M;
03. finalytics.pro простым языком для начинающих;
04. blog by Maxim Zelensky простым языком, но не для начинающих;
05. Товарищ Excel кратко, емко;
06. planetaexcel.ru обстоятельно и с примерами;
07. Книга "Скульптор данных в Excel с Power Query";
08. Кен Пульс и Мигель Эскобар. "Язык М для Power Query" русский перевод отличной книги;
09. Приручи данные с помощью Power Query в Excel и Power BI второе издание шикарной книги на русском от Кен Пульс и Мигель Эскобар.
10. BI блог Максима Уварова перевод статей Криса Вебба и разные коннекторы..;
11. The BIccountant грамотный бух рассказывает про BI;
12. Chris Webb's BI Blog Chris Webb's BI Blog Мега дядька рассказывает обо всем на свете. Просто кладезь!;
13. The Environment concept in M for Power Query and Power BI Desktop, Part 1 Концептуальный и обстоятельный разбор с погружением;
14. Промокоды от переводчика Александр Гинько на отличные книги;
15. Power Query Formatter Красота спасет мир... и мои глаза.

DAX:
01. Основные сведения о DAX в Power BI Desktop опять фундамент;
02. Articles - SQLBI Мега дядьки про все подряд;
03. Patterns – DAX Patterns Мега дядьки про расчеты стандартных показателей в аналитике. ABC и вот это вот все;
04. BI блог Антона Будуева. Разбор формул DAX по-русски;
05. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel;
06. Подробное руководство по DAX;
07. Шаблоны DAX;
08. DAX Formatter by SQLBI Красота спасет мир... и мозг тех, кто читает ваши формулы.