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 Красота спасет мир... и мозг тех, кто читает ваши формулы.

Как подключиться к файлам на Яндекс.Диске
#ВсякиеКоннекторы
И снова здрасьте. На хайпе по теме импортозамещения все чаще граждане перетаскивают свои данные на отечественные облака. И далее встаёт вопрос, а как их оттуда затащить в Power Query? Решением поделился добрый человек @IlyaNazarov.
Пока что сам я не тестировал. По результатам дополню пост собственными впечатлениями.

Table.FromList – пять аргументов счастья
#АнатомияФункций - Table.FromList

Всем привет!
Всё руки не доходили написать про одну из моих любимых функций.
Сначала читаем справку:

Table.FromList(list as list, optional splitter as nullable function, optional columns as any, optional default as any, optional extraValues as nullable number) as table

Обращаем внимание, что обязательным является только первый аргумент – list. Проверяем:

Table.FromList({"a,b,c,1","d,e,f,2","g,h,i,3"})

Работает, заодно узнаем, что по дефолту она ещё и текст по запятой делит, т.е. следующий код выдаст то же самое:

Table.FromList({"a,b,c,1","d,e,f,2","g,h,i,3"},Splitter.SplitTextByDelimiter(","))

Тогда зачем вообще писать splitter? Ну во-первых разделитель не всегда запятая:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3"},Splitter.SplitTextByDelimiter(";"))

А главное вторым аргументом может быть любая другая функция, возвращающая список:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3"},(x)=>Text.Split(x,";"))

или например

Table.FromList({"abc1","def2","ghi3"},Text.ToList)

Ну ОК, а вот такая ситуация

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"))

Даст ошибку в третьей строке. Причина - таблица собирается на основе первой строки, а в третьей столбцов оказалось больше.
Решение - добавить третий аргумент (columns):

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),6)

Т.е. в третий аргумент поместили целевое количество столбцов (в общем виде это не обязательно константа – его можно и вычислить).

Также обращаем внимание, что отсутствующие значения были заменены на null, но если нужно что-то другое - привлекаем четвертый аргумент (default):

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),6,"-")

Обратная ситуация – нам нужны только первые три столбца:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),3,"-",ExtraValues.Ignore)

Т.е. пятый аргумент (extraValues) говорит, что делать с дополнительными значениями - в ситуации выше они проигнорированы, а вот в ситуации ниже – собираются в список:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),4,"-",ExtraValues.List)

Это бывает удобно.

Но что, если мы не хотим дефолтные имена столбцов - да пожалуйста:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),{"а","б","в","г","д","e"})

Т.е. можно передать список имен.

Более того, можно передать названия и типы:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),type table [а=text,б=text,в=text,г=number,д=number,е=number])

Внимательные читатели обратят внимание, что типы столбцов поменялись, а вот сами значения остались текстовыми.
Об этом нужно помнить, но поскольку функция разделения реально может быть любой, можно чутка усложнить:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>List.Transform(Text.Split(x,";"),(y)=>try Number.From(y) otherwise y),type table [а=text,б=text,в=text,г=number,д=number,е=number])

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

Всех благ!
@buchlotnik

List.Select и все все все
#АнатомияФункций – List.Select

Всем привет! Последнее время было несколько вопросов про отбор данных по условию. По этому поводу хочу разобрать List.Select, поскольку к пониманию работы этой функции всё в общем и сводится.
Читаем справку

List.Select(list as list, selection as function) as list

Всё как обычно – список (list) и функция выбора (selection). Пример даётся простой:

List.Select({1..100}, each _>90)

Сразу вспоминаем, что это можно переписать как функцию

List.Select({1..100}, (x)=>x>90)

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

Например, выводим числа, кратные пяти:

List.Select({1..100}, (x)=>Number.Mod(x,5)=0)

или выводим даты только рабочих дней (с понедельника по пятницу):

List.Select(List.Dates(#date(2022,1,1),365,#duration(1,0,0,0)),(x)=>Date.DayOfWeek(x,1)<5)

Также можно комбинировать условия.
Например, выводим числа, кратные трём ИЛИ пяти:

List.Select({1..100}, (x)=>Number.Mod(x,3)=0 or Number.Mod(x,5)=0)

А так кратные трём И кратные пяти :

List.Select({1..100}, (x)=>Number.Mod(x,3)=0 and Number.Mod(x,5)

(я знаком с математикой, можно было просто проверить делимость на пятнадцать, но это просто пример)

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

List.Select({{1,1},{1,2},{2,1},{2,2}},(x)=>x{0}>1)

В данном случае x – это список и мы проверяем его первый элемент x{0}

Аналогично можно обращаться к списку записей:

List.Select({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]},(x)=>x[a]>1)

В этом случае x – это запись, и мы проверяем конкретное её поле – x[a].

Остальное всё то же самое, например,комбинируем условия:

List.Select({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]},(x)=>x[a]>1 or x[b]<2)

Ничего не напоминает? Ровно так и работает Table.SelectRows:

Table.SelectRows(Table.FromRecords({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]}),(x)=>x[a]>1 or x[b]<2)

При выборе строк проверяется каждая из них, но строка – это запись, фактически это и есть работа со списком записей.

Да и в случае Table.SelectColumns без List.Select бывает не обойтись:
let
from = #table({"имя","a1","a2","итого a","b1","b2","b3","итого b"},{{"вася",1,2,3,4,5,6,15},{"петя",1,3,4,5,7,9,21},{"коля",2,4,6,8,10,12,30}}),
lst = List.Select(Table.ColumnNames(from),(x)=>x="имя" or Text.Contains(x,"итого")),
to = Table.SelectColumns(from,lst)
in
to
т.е. lst - из таблицы мы получаем полный список названий столбцов и выбираем «имя» или те, которые содержат «итого».
to - полученный список использовали в SelectColumns.

Как-то так – определяем анализируемую структуру, чтобы понимать, от какого аргумента следует писать функцию, прописываем условие (или комбинацию условий) и получаем нужный результат. List.Select редко используется сам по себе, а вот в связке с другими функциями - весьма полезная штука.

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

Text.Remove, Text.Select, Text.Trim на страже чистоты текста
#АнатомияФункций – всякое про текст
Всем привет!
Разберем задачку, частенько возникающую при парсинге, а именно – имеется текст, а нам надо почистить его от всякого лишнего.
Для этого в арсенале мы имеем Text.Remove, Text.Select, Text.Trim, Text.TrimStart, Text.TrimEnd и даже Text.Clean. Что ж, поехали!
Сначала простенькое:

Text.Remove("<мама <мыла <раму","<")

Здесь мы наблюдаем явно лишний символ <, передаем его вторым аргументом и радуемся жизни

Text.Remove("<мама> <мыла> <раму>",{"<",">"})

символов уже два, поэтому второй аргумент представляет собой список.
Список может расти:

Text.Remove("<мама/> <мыла/> <раму/>",{"<",">","/"})
Text.Remove("<-ма9ма098/> 67-.908<мыABCла/> <ра123qwerму/>",{"<",">","/",".","-","0".."9","A".."Z","a".."z"})

тут вторым аргументом уже сущее безумие - отдельные символы, диапазоны символов - можно ли это как-то упростить?
Конструкция “a”..”z” даёт нам все символы с номерами от 97 (код английской строчной a) до 122 (код строчной z). Вооружившись этим знанием можем переписать код:

Text.Remove("<-ма9ма098/> 67-.908<мыABCла/> <ра123qwerму/>",List.Transform({33..127},Character.FromNumber))

Здесь мы во втором аргументе генерим все числа от 33 до 127 и превращаем их в символы (32 не трогаем – это пробел и он нам нужен). Аналогично можно бороться и с непечатаемыми символами (разрыв строки, перевод каретки, табуляция и т.п.):

Text.Remove("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",List.Transform({1..31,33..127},Character.FromNumber))

Вообще для непечатаемых символов (с 1 по 31) есть своя функция - Text.Clean, но раз уж у нас тут и так список всякого – мы их просто добавили.

Ок, но неоднократно говорилось, что чем длиннее анализируемый список, тем медленнее всё работает. Соответственно можно поменять стратегию – не пытаться удалить всё лишнее, а только оставить всё нужное:

Text.Select("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",{"А".."я"," "})

Вполне симпатично, но поборники чистоты кода попросят переписать:

Text.Select("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",{"А".."я","Ё","ё"," "})

Здесь фишка в том, что Ё и ё в силу исторической несправедливости имеют свои коды, поэтому их указываем дополнительно, через запятую.

Но вы не думайте, что с латиницей проще:

Text.Select("<[mama\] [my\la\] [ra/-mu]/>",{"A".."z"," "})

Неожиданный результат, не так ли? Ну просто так вышло, что [, ], \ ещё несколько символов оказались между прописными и строчными буквами латиницы, поэтому корректно писать так:

Text.Select("<[mama\] [my\la\] [ra/-mu]/>",{"A".."Z","a".."z"," "})

Это всё прекрасно, конечно, но что, если нужно удалить не все символы, а только что-то до или после нужного текста? Тут нам не обойтись без Trim:

Text.TrimStart("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))
Text.TrimEnd("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))
Text.Trim("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))

Идея точно такая же – передаем список символов, и функция их удаляет. Только
TrimStart - удаляет все символы из списка, идущие подряд с начала строки
TrimEnd - то же самое, но с конца строки
Trim - объединяет в себе работу обеих
Только будьте аккуратны при формировании списков:

Text.Trim("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber)&{"М","и","н"})

на выходе получится "хаил (buchlotnik) Музык" - Ми в начале и ин в конце попали под раздачу, поскольку эти символы содержались в списке исключаемых. Это собственно, ответ на прошлогоднюю загадку )))
Как-то так – изучайте коды символов и чистите ваши выгрузки от всякого мусора с полным осознанием дела.

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

Производственный календарь.
#ПолезныеСсылки
Всем добра!
Со скуки запилил парсер производственного календаря за произвольный период (настраивается в параметрах) с сайта Консультант плюс. В телегу код не выкладываю - слишком громоздко, поэтому ссылочкой на форум. Всем желающим предлагаю для развития запилить функцию для выгрузки календаря за произвольный период. Выкладывайте в комментах и добавлю в основной пост. Если желающих не будет - сделаю сам, но позже.