List.Generate + Date.Add* - получаем списки дат и времён
#АнатомияФункций - List.Generate

Всем привет!
Сегодня в очередной раз написал в чат генератор списка времён. Видимо вопрос актуальный, поэтому разбираем:

let
    // List.Generate(()=>дата_начала,(x)=>x<=дата_окончания,(x)=>соответствующая_функция_добавления)
    // #date(year,month,day)
    // #datetime(year,month,day,hour,minute,second)
    // #duration(days,hours,minutes,seconds)
    
    y = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>Date.AddYears(x,1)),
    q = List.Generate(()=>#date(2021,1,11),(x)=>x<=#date(2025,12,31),(x)=>Date.AddQuarters(x,1)),
    qm = List.Generate(()=>#date(2021,1,11),(x)=>x<=#date(2025,12,31),(x)=>Date.AddMonths(x,3)),
    m = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>Date.AddMonths(x,1)),
    w = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>Date.AddWeeks(x,1)),
    wd = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>Date.AddDays(x,7)),
    d = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>Date.AddDays(x,1)),
    d1 = List.Generate(()=>#date(2021,1,1),(x)=>x<=#date(2025,12,31),(x)=>x+#duration(1,0,0,0)),
    h = List.Generate(()=>#datetime(2023,4,16,0,0,0),(x)=>x<=#datetime(2023,4,18,0,0,0),(x)=>x+#duration(0,1,0,0)),
    m15 = List.Generate(()=>#datetime(2023,4,16,0,0,0),(x)=>x<=#datetime(2023,4,18,0,0,0),(x)=>x+#duration(0,0,15,0))
in
    m15

Сначала комменты в коде – сам List.Generate мы уже разбирали тут. Принципиально ничего сложного – имеем дату/датувремя начала, на каждой итерации проверяем не превышение даты/датывремени окончания, функция добавления прибавляет к дате/датевремени нужный период.

Сначала рассмотрим семейство Date.Add*.
y - Date.AddYears добавляет годы, вторым аргументом регулируем шаг в годах (мало ли, вам пятилетки нужны)
q - Date.AddQuarters добавляет кварталы
qm – Date.AddQuarters можно заменить Date.AddMonths с шагом 3 месяца
m – так выглядит генерация с шагом в месяц
w – с шагом в неделю
wd - Date.AddWeeks можно заменить Date.AddDays с шагом в 7 дней
d – ну и генерация с шагом в 1 день соответственно

Теперь рассмотрим альтернативу:
d1 – вместо Date.AddDays можно использовать +#duration(1,0,0,0) – т.е. добавление длительности в 1 день
Соответственно использование #duration позволяет генерить датувремя с шагом, меньше суток:
h – генерим с шагом в 1 час
m15 – шаг 15 минут

Как видите схема генерации принципиально не меняется, главное помнить про List.Generate – он есть, и он хороший )))

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

ЧИСТРАБДНИ на М или практическое применение List.Generate
#АнатомияФункций - List.Generate
Всем привет!

В продолжение прошлого поста про генерацию дат давайте обсудим вопрос генерации списка только рабочих дней, ну и соответственно реализации экселевской ЧИСТРАБДНИ (NETWORKDAYS)

Комплексный пример выглядит так:

let
    f=(ot as date,do as date, optional holidays as list) as number=>
            [ a=List.Generate(()=>ot,(x)=>x<=do,(x)=>Date.AddDays(x,1)),
              b=List.Select(a,(x)=>Date.DayOfWeek(x,Day.Monday)<5),
              c=List.RemoveMatchingItems(b,holidays),
              d=List.Count(if holidays=null then b else c)][d],

    hol = {#date(2023,2,24),#date(2023,1,2),#date(2023,1,3),#date(2023,1,4),#date(2023,1,5),#date(2023,1,6),#date(2023,1,7),#date(2023,5,8),#date(2023,2,23),#date(2023,3,8),#date(2023,5,1),#date(2023,5,9),#date(2023,6,12),#date(2023,11,6)},
    from=#table(type table [ot=date,do=date],{{#date(2023,1,1),#date(2023,12,31)},{#date(2023,1,1),#date(2023,3,31)},{#date(2023,4,1),#date(2023,6,30)},{#date(2023,7,1),#date(2023,9,30)},{#date(2023,10,1),#date(2023,12,31)}}),
    to = Table.AddColumn(from,"networkdays",(r)=>f(r[ot],r[do],hol),Int64.Type)
in
    to

Разбираем по шагам:
f – функция, эквивалентная ЧИСТРАБДНИ – о ней чуть ниже
hol – список праздников,
from – исходная таблица
to – результирующая – вроде всё прозрачно.

Теперь сама функция f.
Вводим три аргумента: ot – дата начала, do – дата окончания, holidays – необязательный аргумент, список праздников

Шаги внутри функции:
a – генерация списка дней от даты начала до даты окончания – сделано через AddDays – это эффективнее, чем +#duration(1,0,0,0)
b – выбираем только рабочие (номер дня в неделе меньше 5, считая с понедельника, напоминаю, что нумерация идёт с нуля)
c – удаление из полученного списка праздничных дней – сделано через List.RemoveMatchingItems – это шустрее, чем List.Difference
d – последний шаг, возвращающий число дней – обращаю внимание – идёт проверка на наличие третьего аргумента: если передан список выходных – подсчитываем список из шага c, иначе – подсчитываем список из шага b
В принципе, если вам нужно именно сгенерировать список дат рабочих дней – останавливайтесь на шаге c.

Как-то так. Код достаточно короткий и на мой вкус логичный – пользуйтесь!

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

СЖПРОБЕЛЫ, только круче, или причём тут Text.SplitAny
#АнатомияФункций - Text.SplitAny

Всем привет!
В продолжение сегодняшнего обсуждения в чате решим одну задачку. Итак, как видно из названия поста, нам нужно реализовать функцию СЖПРОБЕЛЫ – мы раньше уже обсуждали очистку текста , но проблема в том, что Text.Trim, Text.TrimStart и Text.TrimEnd не удаляют лишние пробелы в середине текста, а СЖПРОБЕЛЫ – удаляет. Что ж, решим эту проблему:

let
    tbl = #table({"txt"},{{"   мама   мыла     раму"},{"   мама     #(lf)     мыла   #(tab)#(tab)#(lf)  раму"}}),
    to = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.Split(x," "),(x)=>x<>"")," ")}),
    to1 = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.SplitAny(x," #(00A0)#(lf)#(tab)"),(x)=>x<>"")," ")}),
    to2 = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.SplitAny(x,""),(x)=>x<>"")," ")}),
    f = (txt,optional splitby,optional combby)=>Text.Combine(List.Select(Text.SplitAny(txt,""&(splitby??"")),(x)=>x<>""),combby??" "),
    to3 = Table.TransformColumns(tbl,{"txt",f}),
    to4 = Table.TransformColumns(tbl,{"txt",(i)=>f(i," ")}),
    to5 =Table.TransformColumns(tbl,{"txt",(i)=>f(i,null,"#(lf)")})
in
    to5

По шагам:
tbl – исходная таблица с не очень чистым текстом
to – преобразовываем текстовый столбец. Логика простая – разделяем текст по пробелу (Text.Split), при этом получаем список, два пробела подряд также будут разделены и в список попадёт "" (пустая строка), убираем пустые строки (List.Select) и собираем обратно текст через пробел (Text.Combine). Для первой строки в таблице задача решена, но вторая содержит неразрывные пробелы, табуляции, переносы строк – можно ли избавиться и от них тоже? Можно:
to1 – просто используем Text.SplitAny, которой во втором аргументе передадим помимо пробела ещё и другие разделители
Но сегодня выяснилось, что можно и проще:
to2 – снова используем Text.SplitAny, просто в этот раз вторым аргументом передаём пустую строку – и в этой ситуации она сама прекрасно делит текст по пробельным символам и непечатным символам, что на мой вкус удобно
f – ну и подытожим написанием условно универсальной функции. Обязательный аргумент txt – обрабатываемый текст, необязательные – splitby и combby – разделители по которым надо делить текст и разделитель, через который надо обратно собрать текст соответственно.
to3 – просто используем функцию
to4 – делим только по пробелу
to5 – делим по пробельным и нечитаемым символам, а собираем через разрыв строки (обращаю внимание на синтаксис – второй аргумент передан как null)

Как-то так. Вроде несложно, но пришлось пописать, зато получилась СЖПРОБЕЛЫ с блекджеком и … необязательными аргументами. Пользуйтесь!

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

ДОбыча иерархии – ковыряемся в xml через M
#АнатомияФункций – Xml.Document

Всем привет! Несколько раз всплывал вопрос как вытащить из файла не только данные, но и уровни иерархии. Иерархия при этом может быть в виде структуры, а может быть и в виде отступов, которые непонятно как ловить (привет 1С). На самом деле нам в обоих случаях надо добраться до xml-разметки листа и получить оттуда нужную информацию. В первом комменте под постом будет пример файла (однолистовая выгрузка) с обоими вариантами, попробуем его спарсить:

let
    unzip=Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/UnZip.pq")),#shared),
    bin = Binary.Buffer(File.Contents("C:\Users\User\Downloads\Структура Item_.xlsx")),
    sh=Excel.Workbook(bin,false){0}[Data],
    xml=Xml.Document(unzip(bin){[FileName="xl/worksheets/sheet1.xml"]}[Content]){0}[Value]{[Name="sheetData"]}[Value],
    f=(x)=>x{[Name="outlineLevel"]}?[Value]?,
    g=(x)=> x{0}[Attributes]{[Name="s"]}?[Value]?,
    tr=Table.TransformColumns(xml,{{"Attributes",f},{"Value",g}}),
    lst=List.Sort(List.Distinct(tr[Value]),(x)=>Number.From(x)),
    dict=Record.FromList(List.Positions(lst),lst),
    tr1=Table.TransformColumns(tr,{"Value",(x)=>Record.Field(dict,x)}),
    lst1=Table.ToList(tr1,(x)=>List.LastN(x,2)),
    lst2=Table.ToList(sh,(x)=>x),
    to=Table.FromList(List.Zip({lst2,lst1}),List.Combine,Table.ColumnNames(sh)&{"величина отступа","уровень иерархии"})
in
    to

По шагам:
unzip – тащим функцию-разархиватор с моего гитхаба (принцип описан здесь, а если хотите держать функцию у себя – заберите её тут)
bin – подключаемся к бинарному содержимому файла и помещаем его в буфер. Суть в том, что нам нужно обратиться к нему дважды – отдельно за данными, отдельно – за иерархией
sh – получили данные с листа традиционным способом
xml – получили данные о том же листе, но через разметку., конкретно sheetdata – обратите внимание, что это таблица, содержащая информацию о строках – содержимое и атрибуты.

Теперь напишем две функции :
f – получает информацию об outlineLevel – это и есть уровень строки в иерархии, если она определена структурой
g – а здесь посложнее, дело в том, что 1С-ые отступы являются атрибутом не строки, а отдельной ячейки. В данном случае мы обращаемся к свойствам первого столбца конкретной строки и забираем оттуда s – величину отступа (если нужен не первый столбец заменяем {0} на другую нужную позицию)
tr – ну и применяем, функцию f к атрибутам строки, а функцию g – к значениям строки – на выходе имеем таблицу с соответствующей построчной информацией.

Следующие три шага не являются обязательными, но позволяют превратить величину отступа в уровень иерархии
lst – получили список уникальных значений и отсортировали по возрастанию
dict – собрали из него словарь
tr1 – заменили отступы на уровни через словарь

lst1, lst2, to - дело осталось за малым – данные и информацию об иерархии надо объединить - в данном случае сделано на списках через построчный zip

Как-то так. Пример учебный, поэтому мы добыли сразу оба варианта, в реальной жизни вы можете оставить только нужный вам. В любом случае это очень мало несложного кода. Поэтому не бойтесь xml-разметки, к ней просто надо немножко привыкнуть, а потом заставить приносить пользу.

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

ДОбыча иерархии 2 – боремся с пустыми строками
#АнатомияФункций – Xml.Document

Всем привет! В предыдущем посте мы рассмотрели подход к чтению интересующей нас информации из xml-разметки файла. При этом возможно 2 проблемы:
1 – у любого листа есть его dimension (в xml этот параметр называется именно так) – область, в которой, по мнению Excel, находятся данные (в первом каменте пример – данные начинаются с D5, хотя здоровому человеку очевидно, что с D6) – при обращении к листу pq загружает именно эту область
2 – в данных возможны пустые строки, соответственно информация о них в разметке не хранится

Что ж, на примере вытаскивания структуры решим и эти проблемы:

let
    unzip=Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/UnZip.pq")),#shared),
    bin = Binary.Buffer(File.Contents("C:\Users\muzyk\Desktop\Структура Item_trouble.xlsx")),
    xml=Xml.Document(unzip(bin){[FileName="xl/worksheets/sheet1.xml"]}[Content]){0}[Value]{[Name="sheetData"]}[Value][Attributes],
    val=List.Transform(xml,(x)=>x{[Name="outlineLevel"]}?[Value]?),
    nms=List.Transform(xml,(x)=>x{[Name="r"]}?[Value]?),
    dict=Record.FromList(val,nms),
    sh = Excel.Workbook(bin,false){0}[Data],
    add = Table.AddIndexColumn(sh, "Уровень", Number.From(nms{0}),1),
    to=Table.TransformColumns(add,{"Уровень",(x)=>Record.FieldOrDefault(dict,Text.From(x))})
in
    to

По шагам:
unzip, bin – всё как в прошлый раз
xml – поскольку вынимаем только структуру, сразу загружаем таблицу Attributes для каждой строки
val – получаем для каждой строки значение её outlineLevel
nms – получаем для каждой строки её номер (посмотрите на этот шаг при парсинге файла-примера – можете убедиться, что нумерация не сквозная)
dict – соберем из номеров и уровней словарь на записях
sh – как и в прошлый раз получаем данные первого листа
add – а вот пересборку делаем по-другому – а именно – добавляем столбец индекса, но не с 1, а с первой строки в dimension
to – подцепляем данные из словаря через Record.FieldOrDefault, поскольку не обо всех строках у нас имеется информация

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

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

Качаем отфильтрованные таблицы из файлов xlsx
#АнатомияФункций – Xml.Document

Всем привет!
Что-то уже несколько раз всплывал вопрос получения отфильтрованной таблицы из экселевского файла.
Общее замечание – при обращении к файлу он читается целиком, поэтому сократить время загрузки не получится, но получить на выходе данные только из отфильтрованных строк можно, для этого мы снова полезем в xml-разметку.

В этот раз напишем функцию для обработки файла:

(bin)=>
let
    bin = Binary.Buffer(bin),
    xml=Xml.Document(unzip(bin){[FileName="xl/worksheets/sheet1.xml"]}[Content]){0}[Value]{[Name="sheetData"]}[Value][Attributes],
    val=List.Transform(xml,(x)=>x{[Name="hidden"]}?[Value]?),
    nms=List.Transform(xml,(x)=>x{[Name="r"]}?[Value]?),
    dict=Record.FromList(val,nms),
    sh = Excel.Workbook(bin,false){0}[Data],
    add = Table.AddIndexColumn(sh, "tmp", Number.From(nms{0}),1),
    tbl=Table.TransformColumns(add,{"tmp",(x)=>Record.FieldOrDefault(dict,Text.From(x))}),
    filtr = Table.SelectRows(tbl, each ([tmp] = null)),
    to = Table.RemoveColumns(filtr,{"tmp"})
in
    to

Собственно, мы по большей части это разбирали тут и тут
bin – забуферили бинарное содержимое (мы его читаем дважды)
xml – получили данные в виде списка об атрибутах строк
val – получили информацию о свойстве hidden
nms – получили данные о номере строки
dict – собрали словарь
sh – теперь из бинарного содержимого получили данные с самого листа
add – добавили временный столбец индекса (не забыли учесть, что нумерация может быть не с единицы)
tbl – применили словарь к нашему столбцу индекса, т.е. заменили номера строк на информацию скрыта строка или нет
filtr – оставили только нескрытые строки
to – удалили временный столбец

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

Как-то так. Ковыряйте xml – там много полезного, причём не только в экселевских файлах.

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

ListInterpolate – заполняем пропуски в списках с помощью List.Generate
#АнатомияФункций – CustomFunctions

Всем привет!
Последнее время несколько раз всплывала задача интерполяции значений: на входе имеем таблицу с пропусками по столбцу, которые необходимо заполнить, но не с помощью FillDown или замены null на среднее, а именно арифметической прогрессией, то есть вот такое:

{2,null,6,8,9,null,7,null,null,1}

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

{2,4,6,8,9,8,7,5,3,1}

Т.е. число пропусков может быть переменным, шаг тоже и вот «надо как-то решить».

Начнём с интерполяции одного списка. Для этого соорудил функцию ListInterpolate:

(lst)=>
        [   lst=List.Buffer(lst),
            n = List.Count(lst),
            int=(x,y,z)=>List.Transform({1..x},(i)=>y+i*(z-y)/x),
            gen = List.Generate(
                    ()=>[i=0,l=lst{i},m=0,s=l,o={l}],
                    (x)=>x[i]<n,
                    (x)=>[  i=x[i]+1,l=lst{i},
                            m=if l = null then x[m]+1 else 0,
                            s=if l = null then x[s] else l,
                            o=if l=null then null else if x[m]>0 then int(x[m]+1,x[s],s) else {l}],
                    (x)=>x[o]),
            to = List.Combine(List.RemoveNulls(gen))][to]

Функция принимает в качестве аргумента список.
Далее разбираем по шагам:
lst – забуферили исходный список
n – нашли число его элементов – это потребуется для генерации
int – функция интерполяции, принимает три аргумента:
x – число генерируемых элементов,
y – от какого числа ,
z – до какого (включительно);
здесь получаем элементы простой арифметикой

gen – а вот теперь генератор. Здесь оперируем записью с пятью полями:
i – счётчик элементов анализируемого списка,
l -i-ый элемент анализируемого списка,
m – счётчик элементов для генерации,
s – последнее не null-овое числовое значение,
o – список, который отдаём в качестве результата генерации.
Принцип работы: проходим по всем элементам списка, на каждой итерации проверяем, не встретился ли нам null, если встретился – запускаем счётчик m и считаем число элементов, которые надо заполнить, всё это время в качестве результата отдаём null, как только возникает очередной не null – генерим список и обнуляем счётчик

to – очищаем полученный в ходе генерации список от null, объединяем полученные списки.

Ну и проверяем:

ListInterpolate({1,null,3,4,null,null,7,null,9})//{1,2,3,4,5,6,7,8,9}
ListInterpolate({1,2,null,2,null,2,1})//{1,2,2,2,2,2,1}
ListInterpolate({-1,null,1,null,-1}) //{-1,0,1,0,-1}

Работает, как и задумывалось )))
Собственно, всё. Функцию можно брать в работу, ну а если возникнут затруднения с применением – в следующий раз разберём как с её помощью пачкой заполнить несколько столбцов в таблице.

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

TableInterpolate – заполняем пропуски, но уже в таблице и пачкой
#АнатомияФункций - CustomFunctions

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

(tbl,lst)=>
let
    nms = List.Buffer(Table.ColumnNames(tbl)),
    tr = List.Transform(lst,(x)=>List.PositionOf(nms,x)),
    col=List.Buffer(Table.ToColumns(tbl)),
    f=(x,y)=>List.ReplaceRange(x,y,1,{ListInterpolate(x{y})}),
    acc=List.Accumulate(tr,col,f),
    to = Table.FromColumns(acc,Value.Type(tbl))
in
    to

Функция принимает на вход два аргумента:
tbl – исходная таблица,
lst – список столбцов для преобразования.

Разбираем по шагам:
nms – получили список названий столбцов исходной таблицы,
tr – список названий столбцов для преобразования превращаем в список их позиций в таблице
col – саму таблицу преобразуем в список списков по столбцам
f – ну и напишем функцию для аккумулятора – она заменяет конкретный элемент списка (по индексу) им же, но с применённой ListInterpolate
acc – а теперь собираем все вышенаписанные шаги в аккумуляторе: идем по списку интересующих нас номеров столбцов и применяем к ним интерполятор
to – осталось только собрать таблицу обратно (обратите внимание на последний аргумент – данные о типах столбцов получаем из исходной таблицы, т.е. информация не будет потеряна).

Ну и как это применять:

let
    tbl = #table(type table [a=date,b=number,c=text,d=number,e=text,f=number],
        {{#date(2023,1,1),1,"мама",1,"папа",1},
        {#date(2023,1,2),null,"мыла",null,"пил",null},
        {#date(2023,1,3),null,"раму",3,"пиво",null},
        {#date(2023,1,4),3,"мама",null,"папа",null},
        {#date(2023,1,5),null,"мыла",null,"пил",3},
        {#date(2023,1,6),5,"раму",5,"пиво",5}}),
    lst = Table.ColumnsOfType(tbl,{type number}),
    to = TableInterpolate(tbl,lst)
in 
    to

В данном случае
tbl – исходная таблица
lst – нужные столбцы – здесь я просто взял все числовые; вы же можете написать список руками или, например, использовать List.Select
to – применяем функцию

Как-то так – пара несложных функций и решена вполне себе заковыристая задача. Пользуйтесь!

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

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

Всем привет!
Всплыл в чате вопрос про сортировку таблицы. Ежу понятно, что нужно использовать Table.Sort. Заглянем в справку:

Table.Sort(table as table, comparisonCriteria as any) as table

Второй аргумент имеет тип any, а значит нас ждёт что-то интересное.
В целом мы уже разбирали вопрос сортировки списка и выяснили, что во второй аргумент можно запихивать функцию. Но то было про списки, а тут таблица. С другой стороны, мы разбирали Table.Max - и там тоже выясняли, как использовать функцию во втором аргументе.
Короче, как в одном анекдоте: «у бабочек и цветочков происходит примерно то же самое»:

let 
    from = #table(type table [t=text,d=date,n=number],{{"A-001-2023",#date(2023,1,1),80},{"B-002-2023",#date(2023,1,2),88},{"A-003-2023",#date(2023,1,1),52},{"B-001-2023",#date(2023,1,2),99},{"A-002-2023",#date(2023,1,1),88},{"B-003-2023",#date(2023,1,2),80}}),
    to0 = Table.Sort(from,"d"),
    to1 = Table.Sort(from,{"d",Order.Descending}),
    to2 = Table.Sort(from,{"d","n"}),
    to3 = Table.Sort(from,{"n","d"}), 
    to4 = Table.Sort(from,{"d",{"n",Order.Descending}}),
    to5 = Table.Sort(from,(x)=>x[t]),
    to6 = Table.Sort(from,(x)=>Number.From(Text.Split(x[t],"-"){1})),
    to7 = Table.Sort(from,{(x)=>Number.From(Text.Split(x[t],"-"){1}),"d"}),
    to8 = Table.Sort(from,{{(x)=>Number.From(Text.Split(x[t],"-"){1}),Order.Descending},"d"})
in 
    to8

По шагам:
from – исходная таблица
to0 – простая сортировка по столбцу d, порядок сортировки по умолчанию, т.е. по возрастанию
to1 – то же самое, но по убыванию – обращаем внимание, что теперь мы передаём список {название_столбца,порядок_сортировки}
to2 – сортировать можно по нескольким столбцам, в данном случае сначала по столбцу d, потом по столбцу n
to3 – обращаю внимание – порядок перечисления имеет значение, в данном случае сортировка сначала по столбцу n, а потом по d
to4 – ну и при сортировке по нескольким столбцам также можно задать порядок – в данном случае сначала по d по возрастанию, а затем по n по убыванию – не запутайтесь в фигурных скобках )))

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

Можно, конечно, не городить огород, разбить один столбец на несколько, потом отсортировать, потом удалить лишний столбец, но с функцией оно бывает проще и изящнее. Пользуйтесь!

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

Table.SelectRows + Record.FieldOrDefault или снова словари на записях
#АнатомияФункций - Record.FieldOrDefault

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

let
    from = #table(type table [a=text,b=number,c=number],{{"a",1,1},{"b",2,2},{"c",3,3},{"d",4,4},{"e",5,5},{"f",6,6},{"g",7,7}}),
    lst={"a","c","e","g"},
    to = Table.SelectRows(from,(x)=>List.Contains(lst,x[a])),
    tbl = Table.FromList(lst,(x)=>{x},{"a"}), 
    to1 = Table.Join(from,"a",tbl,"a"),
    dict = Record.FromList(List.Repeat({true},List.Count(lst)),lst),
    to2 = Table.SelectRows(from,(x)=>Record.FieldOrDefault(dict,x[a],false))
in
    to2

Итак, имеем:
from – фильтруемая таблица,
lst – список искомых значений
to – классическое решение через List.Contains, на котором можно было бы и закончить пост… НО, хорошо, если список действительно состоит из буквально нескольких значений, а встречаются варианты, когда имеется список контрагентов из нескольких десятков позиций, или список пользователей на несколько сотен или вообще одна таблица в десятки или сотни тысяч строк фильтруется по другой таблице в десятки или сотни тысяч строк – и вот тут List.Contains начинает «тупить».

Попробуем найти альтернативу:
tbl – соберём из списка таблицу в один столбец, причём название столбца должно совпадать с названием столбца в фильтруемой таблице (если у вас сразу на входе нужная таблица – просто радуемся, а так возможно потребуется Table.RenameColumns)
to1 – а теперь делаем Table.Join – поскольку имена столбцов совпадали, ничего лишнего в таблицу не добавилось, пятый аргумент не указан, поэтому по умолчанию JoinKind.Inner, т.е. только совпадающие строки из обеих таблиц, короче сразу получили отфильтрованную таблицу, вообще без лишних телодвижений. В первом комментарии будут промеры – это реально шустрый вариант

Ну а вишенкой на торте буде вариант, заявленный в теме поста – словари на записях )))
dict – превратим наш список в запись, где интересующие нас значения – названия полей, а в самих полях напишем просто true
to2 – ну и теперь фильтруем через Record.FieldOrDefault, где дефолтом будет false – логика простая – если в записи есть соответствующее поле – сохраняем строку, иначе – выкидываем. На больших массивах данный вариант показывает наилучшие результаты.

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

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

Мерка - теперь и на ютубе
#ПолезныеСсылки - Мерка

Всем привет!
Не созрел я пока до полноценного канала, но кое-что выкладывать буду.
Посему - мануал по Мерке:
https://www.youtube.com/watch?v=pScYcnbsG2c&t=5s
А там посмотрим, может и попишу код на камеру )))

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

PQ тизер курса
#ПолезныеСсылки - Код

Всем привет!
Осенью запускаем курс по pq - по ссылке анонс того, чем будем заниматься )))
https://www.youtube.com/watch?v=Rgy4vXWV1jU

Версия режиссерская, т.е. моя - тут мало про курс и много того, как я пишу, по мне - это самое интересное.
Маркетологи зарубили, а мне нравится, так что смотрите - может полезностей каких подсмотрите )))

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

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

Table.Group vs Table.UnpivotOtherColumns+Table.Pivot или делаем группировку сразу по всем столбцам
#АнатомияФункций - Table.Group

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

let
    from = #table({"a".."z"},List.Repeat(List.Transform({"a".."z"},(x)=>{x}&{1..25}),10)),
    unpivot = Table.UnpivotOtherColumns(from, {"a"}, "Атрибут", "Значение"),
    pivot = Table.Pivot(unpivot, List.Distinct(unpivot[Атрибут]), "Атрибут", "Значение", List.Sum)
in
    pivot

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

И вместо этой красоты я предлагаю нечто такое:

let
    from = #table({"a".."z"},List.Repeat(List.Transform({"a".."z"},(x)=>{x}&{1..25}),10)),
    nms=List.Buffer(Table.ColumnNames(from)),
    lst=List.Transform(List.Skip(nms),(x)=>{x,(y)=>List.Sum(Table.Column(y,x))}),
    to=Table.Group(from,nms{0},lst)
in
    to

по шагам:
from – источник
nms – получили список названий столбцов
lst – преобразовали список в список списков (пропустив столбцы, которые надо оставить с помощью List.Skip, само преобразование разобрано по ссылке выше)
to – и применили это всё в Table.Group
Получается заморочно, нужно писать код, сложный для понимания, с вложенными функциями и т.д. Ну и на кой это всё? Постоянные читатели знают, а вновь прибывшим поясню – ответ в первом комментарии после поста – сравните скорости выполнения запросов. На малых объемах разницы нет или почти нет, а потом она стремительно растёт. Именно поэтому мы пишем код, даже если задачка решается в пару кликов в интерфейсе – просто так эффективнее.

Так что, если задача не разовая, лучше потратить немножко времени на расширенный редактор и получить «летающий» запрос, чем быстренько наклацать, а потом каждый раз ждать, пока оно обновится.

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

Expression.Evaluate или построчное вычисление выражений из ячейки
#АнатомияФункций - Expression.Evaluate

Всем привет!
В своё время мы уже разбирали функцию Expression.Evaluate и выяснили, что можно вычислять выражения вида "123+234+345", что можно написать функцию (x)=>Expression.Evaluate("…x…",[x=x]), и вообще превратили кусок текста с гитхаба в код с помощью #shared.

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

let
    from = #table({"дата","текст","выражение","поле1","поле2"},
    {{#date(2023,1,1),"Вася","-поле1/поле2",11,22},
    {#date(2023,2,2),"Коля","поле1-поле2",0,33},
    {#date(2023,3,3),"Петя","поле1*поле2-поле1",33,44}}),
    f=(rec)=>Expression.Evaluate(rec[выражение],rec),
    to=Table.AddColumn(from,"результат",f)
in
    to

И как бы весь код ))). Суть в том, что при добавлении столбца в функцию f передаётся текущая строка таблицы в виде записи (rec) и мы просим вычислить выражение из соответствующего поля (rec[выражение]), а встречающиеся имена заменить на значения из текущей записи.

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

let
    from = #table({"дата","текст","выражение","поле1","поле2"},
    {{#date(2023,1,1),"Вася","-поле1/поле2",11,22},
    {#date(2023,2,2),"Коля","поле1-поле2",null,33},
    {#date(2023,3,3),"Петя","поле1*поле2-поле1",33,44}}),
    
    nms=List.Buffer(Table.ColumnNames(from)),
    f=(rec)=>[  a=Record.ToList(rec),
                b=List.Transform(a,(x)=>if x=null then 0 else x),
                c=Record.FromList(b,nms),
                d=Expression.Evaluate(rec[выражение],c)][d],
    to=Table.AddColumn(from,"результат",f)
in
    to

в этой ситуации код выше не отработал бы как надо, поскольку любые арифметические операции со значением null дают null. Поэтому пришлось немножко усложнить код:
nms – названия всех столбцов таблицы – нам это потребуется для создания записей на каждой строке – поэтому не забыли List.Buffer
f – функция от записи, которая делает следующее:
a – получили список значений всех полей,
b – заменили значения null на 0
c – заново собрали запись, но уже с нулями
d – отдали её в expression.Evaluate
to – применяем вновь полученную функцию к нашей таблице и получаем корректный результат.

Как-то так. Второй аргумент Expression.Evaluate сильно развязывает руки, надо просто передавать в него корректную запись.

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

Table.ReplaceValue или самая гибкая функция
#АнатомияФункций - Table.ReplaceValue

Всем привет!
За последнее время несколько раз всплывал вопрос по Table.ReplaceValue.
Редко ей пользуюсь, поскольку она немножко совсем не шустрая, и в общем-то задачи могут быть решены другими способами.
Но, с другой стороны, функция гибкая, немножко нестандартная и может представлять интерес для любителей пользовательских функций в аргументах )))

Итак, справка гласит:

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list)

table – в какой таблице,
oldVlue – какое значение,
newValue – на какое значение,
replacer – с помощью какой функции,
columnsToSearch – в каких столбцах меняем

Ну поехали:

let
    from = Table.FromRecords({[a=1,b=1,c="по",d="топот",e=null],[a=2,b=null,c="го",d="загород",e="ф"],[a=3,b=null,c="да",d="дар",e=null],[a=4,b=2,c="хо",d="хорь",e=null],[a=5,b=null,c="ро",d="роща",e="ы"],[a=6,b=null,c="ша",d="шапка",e=null],[a=7,b=3,c="се",d="север",e=null],[a=8,b=null,c="год",d="годнота",e="в"],[a=9,b=null,c="ня",d="няша",e=null]}),
    to1 = Table.ReplaceValue(from,null,0,Replacer.ReplaceValue,{"b","e"}),
    to2 = Table.ReplaceValue(from,null,each if [e] is text then 0 else [a],Replacer.ReplaceValue,{"b"}),
    to3 = Table.ReplaceValue(from,"о","@",Replacer.ReplaceText,{"d"}),
    to4 = Table.ReplaceValue(from,each [c],"@",Replacer.ReplaceText,{"d"}),
    to5 = Table.ReplaceValue(from,each [a],each [b],(x,y,z)=>[x=x,y=y,z=z],{"d"}),
    to6 = Table.ReplaceValue(from,null,null,(x,y,z)=>if x is text then "здесь был текст" else x,Table.ColumnNames(from)),
    to7 = Table.ReplaceValue(from,each [b],null,(x,y,z)=>if y<>null then "-" else x,Table.ColumnNames(from)),
    to8 = Table.ReplaceValue(from,null,each [b],(x,y,z)=>if z<>null then "-" else x,Table.ColumnNames(from)),
    to9 = Table.ReplaceValue(from,each [a],each [b],(x,y,z)=>if y=z then "-" else x,Table.ColumnNames(from)),
    to10 = Table.ReplaceValue(from,(x)=>x,(x)=>x,(x,y,z)=>[x=x,y=y,z=z],Table.ColumnNames(from))
in
    to10

from – входные данные
to1 – простая замена null на 0 по столбцам b и e, Replacer.ReplaceValue говорит, что меняем значение целиком
to2 – усложняем – в newValue можно прописать условие, в данном случае меняем null на 0 только если в столбце «e» содержится текст
to3 – другой вариант замены – Replacer.ReplaceText, т.е. замена в тексте, в данном случае меняем «о» на «@» в столбце «d»
to4 – oldValue также не обязано быть фиксированным – в данном случае производим замену в столбце «d», но в качестве oldValue берем текст из столбца «c»

Промежуточный итог – oldValue и newValue могут быть представлены фиксированным значением, значением из конкретного столбца и даже более сложным условием.

А что же с Replacer-ом? И вот тут самое интересное:
to5 – во-первых, это функция от трёх аргументов ("в каком значении", "что","на что меняем") – для наглядности вывел в виде записи передаваемые аргументы
to6 – и собственно говоря, теперь творим всякое – в данном случае вообще не переданы oldValue и newValue, поскольку мы просто проверяем является ли значение текстом, причём сразу по всем столбцам
to7 – а так мы ставим прочерки во всех столбцах, если столбец «b» не null
to8 – почти то же самое, просто значение из столбца «b» передали не во второй, а в третий аргумент – я же говорил, что функция гибкая )))
to9 – ну а вот так можно задать условие по двум столбцам, передав их значения в два аргумента
to10 – ну и на закуску – все предыдущие варианты я написал через each, хотя внимательные читатели помнят, что это всего лишь синтаксический сахар для (x)=>x. Т.е. на самом деле и во второй, и в третий аргумент можно передать всю строку целиком в виде записи – соответственно, можно написать любую функцию от любого набора столбцов, а пятый аргумент обеспечит нас значением из конкретной ячейки и наши возможности ограничиваются только фантазией.

Как-то так – крайне гибкая функция, но платой за гибкость является не самая высокая скорость.

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

Производственный календарь 2
#АнатомияФункций – CustomFunctions

Всем привет!
Подкинули повод спарсить производственный календарь с Консультанта. Тема уже поднималась Лёхой, но код не был выложен в силу громоздкости.
Подумал, что надо бы исправить ситуацию )))

Посему функция consultant:

(optional year as nullable number) =>
   [path = "http://www.consultant.ru/law/ref/calendar/proizvodstvennye/",
    yr = year??Date.Year(DateTime.LocalNow()), 
    from = Text.FromBinary(Web.Contents(path&Text.From(yr)&(if yr = 2020 then "b" else ""))),
    splt = List.Skip(Text.Split(from,"#(tab,tab,tab)<td class=")),
    lst=List.Zip({{1..12},splt}),
    f=(x)=>[a=Text.Split(x{1},"<td class="),
            b=List.Select(a,(x)=>not Text.Contains(x,"inactively")),
            c=List.Transform(b,(x)=>List.Range(Text.SplitAny(x,"><"),0,2))][c],
    g=(x,y)=>{#date(yr,x{0},Number.From(y{1})),Text.Remove(y{0},"""")},
    tr=List.TransformMany(lst,f,g),
    tbl=Table.FromList(tr,(x)=>x,type table [Date=date,DateType=text])][tbl]

()=> Обращаем внимание – функция без обязательных аргументов
path – откуда тащим
yr – год, за который нужен календарь - если аргумент не передан – берём текущий год (у кого не работает ?? меняем выражение на if year is null then Date.Year(DateTime.LocalNow()) else year)
from – тащим информацию с сайта. Есть нюанс – 2020 был непростым годом, поэтому тогда календарь перезаливали и он получил адресацию 2020b, что собственно здесь и учитывается. В данном случае просто получаем текст со страницы
split – хитро делим текст – помесячные блоки отбиваются тройным табом – обращаю внимание как это записано, ну и первый кусок (до помесячных) отбрасываем
lst – соединяем блоки текста с номерами месяцев через List.Zip
tr – немножко перескочим – полученный список отправляем в List.TransformMany, для которой нам нужны две функции:
f – делит текстовый блок по "<td class=", оставляет фрагменты без "inactively" (у них на сайте месячные блоки стандартные 7 на 6, и так обозначены пустые), и их сплитуем по ">" и "<", оставив первые два фрагмента – статус и номер дня соответственно
g – собираем из полученного дату и статус даты без лишних кавычек

tbl – из полученного списка получаем таблицу, сразу задав типизацию
Как бы всё)))

Как этим пользоваться:

consultant() // календарь на текущий год
consultant(2024) //календарь на конкретный год
Table.Combine(List.Transform({2010..2024},consultant)) //календарь за период лет

Не думаю, что сообщил что-то прям новое, но синтаксически выглядит прикольно.

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

Уважаемый Михаил. Поздравляю с днём рожденья! Желаю щастья в личной жизни! ПуХ.
Всем желающим поздравить виновника торжества напоминаю что реквизиты у него в профиле @buchlotnik

Продвинутое владение Power Query
начинается с написание кода на языке М.

Миша Музыкин один из тех, кто очень круто разбирается в PQ и активно делится своими знаниями через посты и ответы на вопросы в своих ТГ каналах

Принять участие

Мы долго шли к этому курсу и вот программа обучения
16 (пн) окт. 19:30 Мск Как вообще писать код на М
23 (пн) окт. 19:30 Мск Что это за списки, на которых быстрее?
26 (чт) окт. 19:30 Мск Таблицы вдоль и поперек – и это не фигура речи, а разное представление (основная тема - table)
30 (пн) окт. 19:30 Мск Записи или как перестать бояться квадратных скобок в коде (основная тема – record)
02 (чт) ноя. 19:30 Мск Работа с файлами и папками – просто кодом, просто в запросе, просто так можно (основная тема Excel.* + Folder.*)

Подробности смотрите на сайте

Регистрируйтесь. Принимаем оплату от компаний, можем подождать до 30 октября

Тут должен быть текст по возможности, которые открывает PQ, но я фиг знает что сюда писать.
Если вы в теме PQ (то вам не нужно объяснять что писать запросы ручками на М - это ПУШКА).
Если вы не в теме PQ, то вам скорее всего рано на этот курс.

Всем отличных выходных и до встречи в понедельник.

Table.TransformColumnTypes 1 – определяем типы данных в столбцах пачкой без привязки к именам
#АнатомияФункций - Table.TransformColumnTypes

Всем привет!
Очередной раз всплыл вопрос про типизацию столбцов. Кто не в курсе напомню суть проблемы – можно выставить в настройках, чтобы типы определялись при загрузке таблицы автоматически, можно уже после загрузки таблицы в интерфейсе нажать кнопку «Определить тип данных» - и всё будет замечательно… НО! На самом деле в ходе данной операции PQ создаёт шаг, использующий функцию Table.TransformColumnTypes и во втором аргументе оказывается фиксированный список столбцов с типами. Соответственно, если в дальнейшем в таблицу будут добавляться столбцы – они просто не будут типизироваться, а если какой-то столбец будет переименован или удалён, то вообще вылезет ошибка.

Посему задача – уйти от фиксированного списка и соорудить всё кодом. Поехали:

let
    from = #table({"a".."e"},{{123,"мама",12.34,#date(2023,1,1),#time(1,2,3)},{234,"мыла",23.45,#date(2023,2,2),#time(2,3,4)},{345,"раму",34.56,#date(2023,3,3),#time(3,4,5)}}),
    lst = List.Transform(Record.FieldValues(from{0}),Value.Type),
    tr = List.Zip({Table.ColumnNames(from),lst}),
    to = Table.TransformColumnTypes(from,tr)
in
    to

from - в простейшем варианте у нас красивая табличка без пропусков
lst – поэтому берем первую стоку, преобразуем её в список значений, определяем их типы
tr – соединяем названия столбцов с полученными типами через List.Zip
to – преобразуем столбцы

Правда, если б всё было так просто, не было бы поста… В данных могут быть пропуски:

let
    from = #table({"a".."e"},{{123,null,12.34,#date(2023,1,1),null},{null,"мыла",null,#date(2023,2,2),#time(2,3,4)},{345,"раму",34.56,null,#time(3,4,5)}}),
    lst=List.Transform(Table.ToColumns(Table.Range(from,0,10)),f),
    f=(x)=>List.Mode(List.Transform(List.RemoveNulls(x),Value.Type)),

    tr = List.Zip({Table.ColumnNames(from),lst}),
    to = Table.TransformColumnTypes(from,tr)
in
    to

from – уже менее красивая табличка
lst – поэтому мы не можем опираться на первую строку, а берем несколько, в данном случае 10 (стандартно интерфейс работает с первой 1000), разбиваем таблицу на списки по столбцам и к каждому полученному списку применяем функцию f
f – функция удаляет null-ы, определяет тип оставшихся значений и берёт наиболее часто встречающийся (List.Mode)
tr и to – аналогично первому случаю – собрали список преобразований и применили.

Вуаля, задача решена!

Правда если бы она была решена полностью в названии поста не было бы номера 1…
Потому что на самом деле у нас ещё остаются – определение целые или дробные числа у нас в столбце, датавремя или дата, даты/время/датавремя в виде текста (привет 1С) и т.п. И этим мы займемся в следующем посте )))

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

List.Generate, накопленная сумма, списки, записи и какие-то метаданные
#АнатомияФункций – List.Generate

Всем привет!
В своё время мы уже разбирали использование List.Generate и считали накопленную сумму.
Стандартный расчёт выглядит так (описание работы смотрим по ссылке):

let
    lst = List.Buffer({1..250000}),
    n=List.Count(lst),
    gen = List.Generate(()=>[i=0,s=lst{i}],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,s=x[s]+lst{i}],
                        (x)=>x[s])
in
    gen

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

let
    lst = List.Buffer({1..250000}),
    n=List.Count(lst),
    gen = List.Generate(()=>{0,lst{0}},
                        (x)=>x{0}<n,
                        (x)=>{x{0}+1,x{1}+lst{x{0}+1}},
                        (x)=>x{1})
in
    gen

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

Однако, пока писал ответ, возникла другая мысль – а давайте примотаем метаданные:

let
    lst = List.Buffer({1..250000}),
    n = List.Count(lst),
    gen = List.Generate(()=>0 meta [s=lst{0}],
                        (x)=>x<n,
                        (x)=>Value.ReplaceMetadata(x+1,[s=Value.Metadata(x)[s]+lst{x+1}]),
                        (x)=>Value.Metadata(x)[s])
in
    gen

т.е. вместо списка значений запихиваем накопленную сумму в метаданные нашего значения, при этом приходится использовать Value.Metadata и Value.ReplaceMetadata, что делает код несколько громоздким, зато см. промеры по скорости в первом комментарии под постом – это вполне себе шустро.

Поэтому делаем выводы: накопленная сумма – это тот случай, когда НА ЗАПИСЯХ быстрее, а вариант с метаданными, пусть чуть медленнее, зато весьма экзотичен.
Решайте задачи разными путями – это бывает интересно 😉

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

ЧИСТВНДОХ на М – а почему бы и нет?
#АнатомияФункций – CustomFunctions

Всем привет!
В чат закинули задачку – можно ли посчитать экселевскую ЧИСТВНДОХ через PQ?
Ну было бы нельзя, не было бы поста )))

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

ЧИСТВНДОХ =(даты as list,потоки as list) as number =>
[   sum=(x)=>List.Sum(List.Transform(lst,(y)=>y{1}/Number.Power(1+x,y{0}))),
    min = List.Min(даты),
    zip= List.Zip({даты,потоки}),
    lst = List.Buffer(List.Transform(zip,(x)=>{Duration.TotalDays(x{0}-min)/365,x{1}})),
    base = Number.Sign(sum(0)),
    gen=List.Generate( ()=>[i=0,a=0,b=100,p=(a+b)/2,s=sum(p),t=Number.Sign(s), f=t=base],
                        (x)=>x[i]<100 and x[b]-x[a]>0.0000001,
                        (x)=>[  i=x[i]+1,
                                a=if x[f] then x[p] else x[a],
                                b=if x[f] then x[b] else x[p],
                                p=(a+b)/2,
                                s=sum(p),
                                t=Number.Sign(s), 
                                f=t=base],
                        (x)=>x[p]),
    to = List.Last(gen)][to]

Где
sum – функция, вычисляющая сумму, которую мы должны обнулить
min – стартовая дата
zip, lst – получаем список для sum – по каждой дате получаем период в годах и сам поток
base – знак исходной суммы (нам это знание нужно, чтобы ловить переход через ноль)
gen – ну и запускаем генератор – обычный метод половинного деления (краевые значения и точность задал вручную, кому интересно может сделать параметрами или вообще вычислить)
to – результат последней итерации и есть искомое.

Как-то так – тут мало М, в основном численные методы.
Кому код покажется громоздким – можно переписать на рекурсию:

ЧИСТВНДОХ =(даты as list,потоки as list) as number =>
[   sum=(x)=>List.Sum(List.Transform(lst,(y)=>y{1}/Number.Power(1+x,y{0}))),
    min = List.Min(даты),
    zip= List.Zip({даты,потоки}),
    lst = List.Buffer(List.Transform(zip,(x)=>{Duration.TotalDays(x{0}-min)/365,x{1}})),
    base = Number.Sign(sum(0)),
    func=(i,a,b)=> [   p=(a+b)/2, s=sum(p), t=Number.Sign(s), f=t=base,
                        out =if i<100 and (b-a)>0.0000001 
                                then if f 
                                        then @func(i+1,p,b) 
                                        else @func(i+1,a,p) else p ][out],
    to = func(0,0,100)][to]

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

let
    from = #table({"Даты","Потоки"},{{#date(2019,1,1),-1090},{#date(2019,12,31),123},{#date(2020,12,31),123},{#date(2021,12,31),123},{#date(2022,12,31),123},{#date(2023,12,31),1353}}),
    to = ЧИСТВНДОХ(from[Даты],from[Потоки])
in
    to //0.13257

Вроде работает и даже не вешает комп, пользуйтесь!

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