Для тех, кто в танке

Номер недели по ISO силами M
#АнатомияФункций – custom

Всем привет! В чат подкинули задачку – определить номер недели по ISO.

Вроде ничего сложного, но… По ISO неделя начинается с понедельника (уже хорошо), но первая неделя года – та, что содержит четверг. И в целом номер недели привязывается к четвергу. Как раз сейчас была такая ситуация – 30 и 31 декабря 2024 относятся к первой неделе 2025, потому что четверг (02.01.2025) уже в новом году. А может быть обратная ситуация: 1-3 января 2027 будут относится к 53-ей неделе 2026, потому что четверг (31.12.26) оказывается в прошлом году.

Ну и по этому поводу немножко кода:

fnIsoWeekNum=(x)=>[t=(x)=>Date.AddDays(x,3-Date.DayOfWeek(x,Day.Monday)),
            a=t(x),
            b=t(#date(Date.Year(a),1,1)),
            c=Duration.Days(a-b)/7+Number.From(Date.Year(b)=Date.Year(a))][c]

где
t – функция определения четверга текущей недели (логика простая – берем номер дня недели и находим разницу между 3 и этим номером, разницу добавляем к текущей дате),
a – определяем четверг для текущей даты,
b – изюминка – находим четверг для первого января года НЕ текущей даты, а года её четверга (не забываем, что четверг может оказаться в прошлом году),
c – собственно, разница между a и b, делённая на 7, даст нам разницу в неделях, нумерация при этом должна быть с единицы, поэтому мы должны были бы плюсовать 1, но на самом деле не совсем так, поэтому вишенка - плюсуем 1 только если даты попали в один год, при нумерации с четверга из прошлого года это не требуется.
Как-то так – немножко логики, немножко кода – и задачка решена.

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

Почти реализация РАБДЕНЬ.МЕЖД()
#АнатомияФункций - custom

Всем привет!
Возник в чате вопрос по реализации экселевской РАБДЕНЬ.МЕЖД().
Соответственно, у меня возникло два вопроса.
Один к функции – «почему рабочие субботы не учитываем?!» - но это решаемо

g=(ot,dn,vyh,pra,rab)=>
    [   a=List.Buffer(Text.PositionOf(vyh,"1",Occurrence.All)),
        b=(x)=>if List.Contains(pra,x) or (List.Contains(a,Date.DayOfWeek(x,Day.Monday)) and not List.Contains(rab,x)) then 0 else 1,
        c=List.Generate(()=>[d=ot,p=0,i=p],
                        (x)=>x[i]<=dn,
                        (x)=>[d=Date.AddDays(x[d],1),p=b(d),i=x[i]+p],
                        (x)=>if x[p]=1 then x[d] else null),
        d=List.Last(List.RemoveNulls(c))][d]

или без Гены

g=(ot,dn,vyh,pra,rab)=>
    [   a=List.Buffer(Text.PositionOf(vyh,"1",Occurrence.All)),
        b=(x)=>if List.Contains(pra,x) or (List.Contains(a,Date.DayOfWeek(x,Day.Monday)) and not List.Contains(rab,x)) then false else true,
        c=List.Dates(ot+#duration(1,0,0,0),List.Count(pra)+Number.RoundUp(dn/(7-List.Count(a)))*List.Count(a)+dn,#duration(1,0,0,0)),
        d=List.Select(c,b){dn-1}][d]

Второй к вопрошающему – «а какого художника мы пытаемся экселевскую логику запихнуть в другой язык?!»
Потому как обычный код

let
    g=(ot,dn)=>slct{List.PositionOf(slct,ot,Occurrence.First,(c,v)=>c>v)+dn-1},    
    f=(x)=>List.Transform(Table.Column(Excel.CurrentWorkbook(){[Name=x]}[Content],x),Date.From),
    pra = List.Buffer(f("Праздники")),
    rab = List.Buffer(f("Рабочие")),
    
    from = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
    typ = Table.TransformColumnTypes(from,{"дата размещения", type date}),

    lst = List.Buffer(typ[дата размещения]),
    min = List.Min(lst),
    max = List.Max(lst),
    dates = List.Dates(min,Duration.TotalDays(max-min)+Number.RoundUp(List.Max(typ[Дней])/5)*2+List.Count(pra),#duration(1,0,0,0)),
    slct = List.Buffer(List.Sort(List.Difference(List.Select(dates,(x)=>Date.DayOfWeek(x,Day.Monday)<5),pra)&rab)),
    
    to = Table.AddColumn(typ,"дата выполнения",(x)=>g(x[дата размещения],x[Дней]))
in
    to

Будет кратно быстрее

По этому поводу смотрим рутуб

или дзен

ну а с исходниками всё давно выложено на sponsr.ru


Лайк, коммент, подписка приветствуются )))

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

С НАСТУПАЮЩИМ 2025 ГОДОМ!!!
#АнатомияФункций – List.TransformMany

Все привет!

Поздравляю с наступающим (или с наступившим – зависит от вашей TimeZone.Current) Новым Годом!!!

Спасибо, что читаете, смотрите, пишите каменты, поддерживаете на спонсоре.
Всем здоровья, хорошего настроения и материального благополучия!

Кто забыл/не успел нарядить ёлку – срочно юзайте код:

let 
    p=11,
    r=(x)=>Text.Repeat("*",x),
    a=(x)=>Text.PadStart("/"&r(x),p," "),
    z=(x)=>Text.PadEnd(r(x)&"\",p," "),
    d=(x)=>a(x)&z(x),
    n=(x)=>{x..2*(x+1)},
    i=List.TransformMany({0..(p-3)/2},n,(x,y)=>d(y)),
    k=Text.Combine(i,"#(lf)")
in 
k

Ну а как оно так – сложил в общий доступ на sponsor

Надеюсь, всё что было написано/записано в 2024 было (а в 2025 будет) полезно.
Всех благ!
@buchlotnik

Полный перебор комбинаций
#АнатомияФункций - Table.Join, List.Accumulate

Всем привет!
Никто не читает старый добрый танк…
Как перебрать все варианты для двух таблиц Лёха писал тут.
Как это сделать для нескольких таблиц я писал тут.

Но, видимо это было давно и неправда – по этому поводу код:

let
    g=(x)=>Table.SelectRows(Table.SelectColumns(from,x),(y)=>Record.Field(y,x)<>null),
    f=(x,y)=>Table.Join(x,{},g(y),{}),
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    to = List.Accumulate(Table.ColumnNames(from),#table({},{{}}),f)
in
to

А расшифровку происходящего смотрим:
С исходниками на спонсоре

На рутубе

На дзене

Лайк, коммент, подписка приветствуются )))

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

GroupKind.Local + Table.ReverseRows или группировка снизу
#АнатомияФункций – Table.Group, Table.Reverse.Rows

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

let
    f=(x)=>[a=x{0},
            b=Table.Last(x)[DateTime],
            c=[Date=a[Date],Agent=a[Agent],Ready=a[DateTime],Logout=b,Duration=Duration.TotalMinutes(Logout-Ready)],
            d=if Table.RowCount(x)<2 then null else c][d],
    from = Excel.CurrentWorkbook(){[Name="big"]}[Content],
    fltr = Table.SelectRows(from,(x)=>x[State]="Ready" or x[State]="Logout"),
    lst = List.Buffer({"Logout"}&fltr[State]),
    add = Table.AddIndexColumn(fltr,"prev"),
    tr = Table.TransformColumns(add,{"prev",(x)=>lst{x}}),
    gr = Table.Group(tr,"prev",{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c="Logout")),
    to = Table.FromRecords(List.RemoveNulls(gr[tmp]))
in
to

А потом по-человечески:

let
    f=(x)=>[b=x{0}[DateTime],
            a=Table.Last(x),
            c=[Date=a[Date],Agent=a[Agent],Ready=a[DateTime],Logout=b,Duration=Duration.TotalMinutes(Logout-Ready)],
            d=if Table.RowCount(x)<2 then null else c][d],
    from = Excel.CurrentWorkbook(){[Name="big"]}[Content],
    fltr = Table.SelectRows(from,(x)=>x[State]="Ready" or x[State]="Logout"),
    tr = Table.ReverseRows(fltr),
    gr = Table.Group(tr,"State",{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c="Logout")),
    to = Table.FromRecords(List.RemoveNulls(gr[tmp]))
in
    to

Зачем и почему оно так смотрим:
С исходниками на спонсоре

А также на рутубе, в дзене и скоро зальётся на ютуб
Лайк, коммент, подписка приветствуются )))

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

ABC-анализ методом двух касательных
#АнатомияФункций – приёмы

Всем привет!
Чуть не поругались с Игорем на тему, какой метод определения границ групп в АВС-анализе является «правильным», по этому поводу восстанавливаю справедливость и привожу вариант для динамических границ, а именно, метод двух касательных:

(таблица,группа,столбцы)=>
[   g=(x)=>[a=Record.ToList(Record.SelectFields(x,столбцы)),
            b=Record.Field(dict,Record.Field(x,группа)),
            c=List.Transform(List.Zip({a,b}),(x)=>if x{0}>=x{1}{0} then "A" else if x{0}>=x{1}{1} then "B" else "C")][c],
    f=(x)=>(y)=>[a=List.Buffer(Table.Column(y,x)),
                b=List.Average(a),
                c={b,List.Average(List.Select(a,(x)=>x<b))}][c],
    gr = Table.Group(таблица,группа,List.Transform(столбцы,(x)=>{x,f(x)})),
    dict = Record.FromList(Table.ToList(gr,List.Skip),Table.Column(gr,группа)),
    add = Table.AddColumn(таблица,"tmp",g),
    nms = List.Transform(столбцы,(x)=>"ABC_"&x),
to = Table.SplitColumn(add,"tmp",(x)=>x,nms)][to]

Ну а что тут к чему и почему так мало кода… смотрим в видосе.

Для подписчиков с исходниками – Sponsr
Рутуб
Дзен

Лайк, коммент, подписка приветствуются )))

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

PNG – вынимаем параметры изображения
#АнатомияФункций – BinaryFormat

Всем ‎привет!‏ ‎
Давно ‎не ‎ковырялись ‎в ‎бинарке,‏ ‎а ‎тут‏ ‎в‏ ‎чат‏ ‎пришли ‎с ‎задачкой ‎про‏ ‎png.
Конкретно нужно из бинарного содержимого вынуть информацию о ширине и высоте изображения.
Ну, собственно, дело не хитрое – рассмотрим варианты.
Через Binary.Range

let
    f=(x)=>{BinaryFormat.UnsignedInteger32(Binary.Range(x,16,4)),BinaryFormat.UnsignedInteger32(Binary.Range(x,20,4))},
    
    from = Folder.Files("C:\Users\muzyk\Desktop\PQ КУРС МАТЕРИАЛЫ"),
    filtr = Table.SelectRows(from,(x)=>x[Extension]=".png")[[Name],[Content]],
    to = Table.SplitColumn(filtr,"Content",f,{"Width","Height"})
in
    to

Через BinaryFormat.List

let
    f=(x)=>List.Skip(BinaryFormat.List(BinaryFormat.UnsignedInteger32,6)(x),4),
    
    from = Folder.Files("C:\Users\muzyk\Desktop\PQ КУРС МАТЕРИАЛЫ"),
    filtr = Table.SelectRows(from,(x)=>x[Extension]=".png")[[Name],[Content]],
    to = Table.SplitColumn(filtr,"Content",f,{"Width","Height"})
in
    to

Через BinaryFormat.Record

let
    f=BinaryFormat.Record(
        [skip=BinaryFormat.Binary(16),
        Width=BinaryFormat.UnsignedInteger32,
        Height=BinaryFormat.UnsignedInteger32]),
    
    from = Folder.Files("C:\Users\muzyk\Desktop\PQ КУРС МАТЕРИАЛЫ"),
    filtr = Table.SelectRows(from,(x)=>x[Extension]=".png")[[Name],[Content]],
    tr = Table.TransformColumns(filtr,{"Content",f}),
    to = Table.ExpandRecordColumn(tr,"Content",{"Width","Height"})
in
    to

Ну а что тут к чему, и почему так, смотрим
С исходниками на спонсоре
На рутубе
На дзене


Лайк, коммент, подписка приветствуются )))

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

«Справочник» и горе-погроммисты
#АнатомияФункций

Всем ‎привет!‏
‎Напоролся ‎на ‎видео ‎по ‎работе‏ ‎со ‎справочниками.
Причём сначала предложили вполне приличный мышкоклац:

let
    Источник = Excel.CurrentWorkbook(){[Name="Sales2"]}[Content],
    #"Объединенные запросы" = Table.NestedJoin(Источник, {"Service ID"}, dimCost, {"Service ID"}, "dimCost", JoinKind.LeftOuter),
    #"Развернутый элемент dimCost" = Table.ExpandTableColumn(#"Объединенные запросы", "dimCost", {"Cost"}, {"Cost"}),
    #"Замененное значение" = Table.ReplaceValue(#"Развернутый элемент dimCost",null,0,Replacer.ReplaceValue,{"Cost"})
in
#"Замененное значение"

Вот только потом заявили, что «лучше писать на М»… И написали такое, что кроме «чем вот так, лучше мышкоклацать» мне сказать нечего.
Поэтому вот так стоит писать на М:

let
    base =  Excel.CurrentWorkbook(){[Name="dimCost2"]}[Content],
    dict = Record.FromList(base[Cost],List.Transform(base[Service ID],Text.From)),
    from = Excel.CurrentWorkbook(){[Name="Sales2"]}[Content],
    to = Table.AddColumn(from,"Cost",(x)=>Record.FieldOrDefault(dict,Text.From(x[Service ID]),0))
in
    to

Ну а как не стоит, да ещё с порцией моего бомбления смотрим:
С исходниками – на спонсоре
На рутубе
На дзене
Лайк, коммент, подписка приветствуются )))

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

Пы.Сы. а в рамках курса мы уже добрались до таблиц – кому интересно - присоединяйтесь

ABC-анализ на М
#АнатомияФункций – приёмы

Все привет!
Как насчёт задачки, чтоб аккумулятор с генератором, сортировками, группировками,буферами и комбайнами всякими?
Ну вот примерно такое и требуется, если ABC-анализ проводить на стороне PQ, да чтоб сразу по пачке столбцов, с сохранением порядка строк, блекджеком и … (хотя, когда закончите – они тоже не помешают).

В общем когда-то давно, на заре Мерки, я решил это так:

(таблица,группа,столбцы)=>
let
    ABC=(tbl,col,colname)=>[
    t = Table.Buffer(Table.Sort(tbl,{col,Order.Descending})),
    r = List.Buffer(Table.Column(t,col)),
    n = List.Count(r),s = List.Sum(r),
    a = 0.5*s,b = 0.8*s,c = 0.95*s,
    f=(x)=>if x<=a then "A" else if x<=b then "B" else if x<=c then "C" else "D",
    g = List.Generate(()=>[i=0,j=r{i},k="A"],(x)=>x[i]<n,(x)=>[i=x[i]+1,j=r{i}+x[j],k=f(j)],(x)=>x[k]),
   res = Table.FromColumns(Table.ToColumns(t)&{g},Table.ColumnNames(t)&{colname})][res],
   add = Table.AddIndexColumn(таблица,"i"),
   lst = List.Buffer(List.Transform(столбцы,(x)=>{x,"ABC_"&x})),
   g=(tbl)=>List.Accumulate(lst,tbl,(s,c)=>ABC(s,c{0},c{1})),
   gr=Table.Group(add,группа,{"t",g}),
   exp = Table.ExpandTableColumn(gr,"t",List.Combine(lst)&{"i"}),
   to = Table.RemoveColumns(Table.Sort(exp,"i"),"i")
in
to

Ну решил и забыл. А тут неожиданно в чат принесли код на оптимизацию, и я начал его переписывать… закрались сомнения – полез в архивы – понял, что переписываю сам себя из 2020 ))) Вот только переписать всё равно было необходимо, поэтому получилось такое:

(таблица,группа,столбцы)=>
[
    ABC=(tbl,col,colname)=>[
    t = Table.Buffer(Table.Sort(tbl,{col,Order.Descending})),
    r = List.Buffer(Table.Column(t,col)),
    n = List.Count(r),s = List.Sum(r),
    a = g(0.5*s), b = g(0.8*s), c = g(0.95*s),
    f=(x)=>if x<=a or x=0 then "A" else if x<=b then "B" else if x<=c then "C" else "D",
    gen = List.Buffer(List.Generate(()=>[i=0,j=r{i}],(x)=>x[i]<n,(x)=>[i=x[i]+1,j=r{i}+x[j]],(x)=>x[j])),
    g=(x)=>List.PositionOf(gen,x,Occurrence.Last,(c,v)=>v>=c),
   res = Table.TransformColumns(Table.AddIndexColumn(t,colname),{colname,f})][res],
   add = Table.AddIndexColumn(таблица,"i"),
   lst = List.Buffer(List.Transform(столбцы,(x)=>{x,"ABC_"&x})),
   g=(tbl)=>List.Accumulate(lst,tbl,(s,c)=>ABC(s,c{0},c{1})),
   gr=Table.Group(add,группа,{"t",g}),
   exp = Table.Combine(gr[t]),
   to = Table.RemoveColumns(Table.Sort(exp,"i"),"i")][to]

Если думаете, что это практически тоже самое – ошибаетесь, оно отличается алгоритмически и при этом кратно шустрее )))

Ну а детали можно посмотреть:
На спонсоре с исходниками
На рутубе
На дзене
На ютубе

Лайк, коммент, подписка приветствуются )))

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

Пишем эффективно — Combine + Expand против FromRecords
#АнатомияФункций – Table.FromRecords

Всем привет!
В чат подкинули задачку. По этому поводу наиболее простым и логичным («красивым») показался такой код:

let
    f=(x)=>Function.Invoke(Record.FromList,List.Reverse(List.Zip(List.Split(List.RemoveNulls(x),2)))),
    
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="TData"]}[Content]),
    nms = List.Select(Table.ColumnNames(from),(x)=>Text.Contains(x,"DL")),
    cmb = Table.Buffer(Table.CombineColumns(from,nms,f,"tmp")),
    newnms = List.Distinct(List.Combine(List.Transform(cmb[tmp],Record.FieldNames))),
    to = Table.ExpandRecordColumn(cmb,"tmp",newnms)
in
    to

Вроде всё логично… просто зачем два табличных преобразования, когда можно на списках? Риторический вопрос )))

let
    f=(x)=>[a=List.Zip(List.Split(List.RemoveNulls(List.Skip(x,pos)),2)),
            b=Record.FromList(List.FirstN(x,pos)&a{1},fnms&a{0})][b],
    
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="TData"]}[Content]),
    nms = Table.ColumnNames(from),
    pos = List.PositionOf(nms,"DL",Occurrence.First,Text.Contains),
    fnms = List.Buffer(List.FirstN(nms,pos)),
    lst = List.Buffer(Table.ToList(from,f)),
    newnms = List.Distinct(List.Combine(List.Transform(lst,Record.FieldNames))),
    to = Table.FromRecords(lst,newnms,MissingField.UseNull)
in
    to

Вроде сложнее, но реально быстрее.

А как так и почему смотрим
На рутубе дзене ютубе
Исходники уже лежат на спонсоре

Лайк, коммент, подписка приветствуются )))

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

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

Кому интересно - заходим.
Прошедшие видео никуда не деваются и продолжают быть доступными.

Надеюсь кому-то будет полезно)))
Всех благ!
@buchlotnik

Альтернативные источники видосов 2

Всем привет!
Эпопея с тормозами ютуба продолжается, дзен заходит не всем, да и заливать туда нужно видео по одному, а вот на рутубе нашёл волшебную кнопку "перелить всё с ютуб". Процесс идёт не очень быстро, но вот за ночь 14 штук уже перелиты (а в стеке действительно стоят ВСЕ).
По этому поводу для всех, кому интересно, мои видосы теперь ищем:
- на рутубе
- на дзене
- ну а с исходниками и всякими доп. материалами (навроде курса по языку М) - на sponsr

Лайк, коммент, подписка приветствуются )))

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

O(n^2) – побеждаем сложность
#АнатомияФункций – приёмы

Всем привет!

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

let
    from = List.Distinct(Excel.CurrentWorkbook(){[Name="база"]}[Content][Наименование]),
    lst = List.Buffer(List.Transform(List.Zip({from,{1..List.Count(from)}}),(x)=>{x{0},Text.ToList(Text.Upper(x{0})),Text.Length(x{0}),x{1}})),

    g=(x,y)=>2*List.Count(List.Intersect({x{1},y{1}}))/(x{2}+y{2}),
    f=(x)=>List.Select(List.Transform(List.Skip(lst,x{3}),(y)=>{x{0},y{0},g(x,y)}),(z)=>z{2}>0.86),
    tr = List.TransformMany(lst,f,(x,y)=>y),
    to = Table.FromList(tr,(x)=>x,{"Наименование","Похожее","Подобие"})
in
to

Ну а что тут к чему смотрим -
С исходниками на sponsr - там же и курс по языку М
На дзен
и если повезет на ютуб

Лайки, комменты, подписки приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik

Курс по языку М в Power Query

Всем привет!

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

Обзор курса тут.

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

Expression.Evaluate + таблица условий =… кайф
#АнатомияФункций - Expression.Evaluate

Всем привет!

Подогнали задачку с таблицей условий для формирования агрегаций по таблице. Так и хочется сказать, что я таблицы не люблю, ими давлюсь и предпочитаю списки разводить… Но, с другой стороны, почему бы не поупражняться с Expression.Evaluate?

По этому поводу код:

let
    f = (x)=>[  a = "Column"&Text.From(List.Last(Record.ToList(x{0}))),
                b = Table.SelectRows(base,g(x)),
                c = List.Sum(Table.Column(b,a))][c],
    g = (x)=>[  a = List.Split(List.RemoveLastN(List.Skip(Table.ToColumns(x)),1),3),
                b = (x)=>if x{2}{0}=null then null else "("&Text.Combine(List.Transform(List.Distinct(x{2}),c(x))," or ")&")",
                c = (x)=>(y)=>Text.Format("(x[Column#{0}]#{1}""#{2}"")",{x{0}{0},x{1}{0},y}),
                d = Expression.Evaluate("(x)=>"&Text.Combine(List.Transform(a,b)," and "))][d],
    
    base = Table.Buffer(Excel.CurrentWorkbook(){[Name="Исходные"]}[Content]),
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.Group(from, "Строка итога", {"Сумма",f})
in
to

Ну а что тут к чему – смотрим дзен
Файл-исходник забираем на sponsr
Даже ютуб сегодня благосклонен

Лайки, комменты, подписки приветствуются )))
(по подписке ещё и курс с понедельника)

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

Два словаря, Combine, Expand и Split – а как вы провели выходные?
#АнатомияФункций – приёмы

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

let
    lst = List.Buffer(Excel.CurrentWorkbook(){[Name="artlist"]}[Content][Value]),
    dict = Record.FromList(List.Repeat({true},List.Count(lst)),lst),
    f=(x)=>Record.FieldOrDefault(dict,x[#"Артикул (продукции)"],false),
    g=(x)=>not f(x),
    h=(x)=>Table.SelectRows(from,x),
    
    from = Table.Buffer(Excel.CurrentWorkbook(){[Name="combatset"]}[Content]),
    nms = List.Buffer({"Реквизиты спецификации"}&List.LastN(Table.ColumnNames(from),4)),
    dict1 = Record.FromTable(Table.RenameColumns(Table.Group(h(f),"Партия (продукции)",{"Value",(x)=>Table.SelectColumns(x,nms&{"Сдано на склад"})}),{"Партия (продукции)","Name"})),

    j=(x)=>[a=Record.FieldOrDefault(dict1,x{3}),
            b=List.Last(x),
            c=Table.ToList(a,(x)=>List.FirstN(x,4)&{x{4}/x{5}*b}),
            d=if a = null then {x} else c][d],

    cmb = Table.CombineColumns(h(g),nms,j,"tmp"),
    exp = Table.ExpandListColumn(cmb,"tmp"),
    to = Table.SplitColumn(exp,"tmp",(x)=>x,nms)

in
to

Ну а что тут к чему – разбираю на Дзене

Файл с исходниками уже лежит на sponsr

А с ютубом всё – даже в студию не смог зайти зашёл - всё будет, но позже


Лайки, комменты, подписки приветствуются )))

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

Фильтрация списка по списку через словарь и Splitter
#АнатомияФункций - Record.FieldOrDefault, Splitter.SplitTextByRanges

Всем привет!
Итак, задачка – «отфильтровать один список по частичным совпадениям начала строк со вторым».
Делов-то:

let
    lst = Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col],
    dict = List.Buffer(Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match]),
    to = List.Select(lst,(x)=>List.Contains(dict,x,(y)=>Text.StartsWith(x,y)))
in
    to

Только проблема… медленно это. И вот приходит такой Андрей (m) и выкатывает:

let
    lst = List.Buffer(Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col]),
    dict = List.Buffer(Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match]),
    to = List.Transform(List.PositionOfAny(lst,dict,Occurrence.All,(c,v)=>Text.StartsWith(c,v)),(x)=>lst{x})
in
    to

А я говорю – да нет, тогда уж вот:

let
    lst = List.Buffer(Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col]),
    dict = List.Buffer(Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match]),
    to = List.Transform(List.PositionOfAny(lst,dict,Occurrence.All,Text.StartsWith),(x)=>lst{x})
in
    to

А тут ещё Игорь (CubRoot) приходит, говорит вот так надо:

let
    lst = Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col],
    dict = List.Buffer(Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match]),
    to = List.Select(lst,(x)=>Splitter.SplitTextByAnyDelimiter(dict)(x){0}="")
in
    to

А я такой – блин, да нет же – вот так надо:

let
    lst = Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col],
    dic = Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match],
    len = List.Buffer(List.Distinct(List.Transform(dic,Text.Length))),
    dict = Record.FromList(List.Repeat({true},List.Count(dic)),dic),
    f=(x)=>List.AnyTrue(List.Transform(len,(y)=>Record.FieldOrDefault(dict,Text.Start(x,y)))),
    to = List.Select(lst,f)
in
    to

... а сам думаю, но ведь со сплиттером-то и правда вкуснее:

let
    lst = Excel.CurrentWorkbook(){[Name="tsrc"]}[Content][col],
    dic = Excel.CurrentWorkbook(){[Name="tmatch"]}[Content][match],
    len = List.Buffer(List.Distinct(List.Transform(dic,(x)=>{0,Text.Length(x)}))),
    dict = Record.FromList(List.Repeat({true},List.Count(dic)),dic),
    f=(x)=>List.AnyTrue(List.Transform(Splitter.SplitTextByRanges(len)(x),(y)=>Record.FieldOrDefault(dict,y))),
    to = List.Select(lst,f)
in
    to

И вот так долго ли коротко ли скорость выполнения была приподнята в 15 раз )))
А как оно так – смотрим на дзене
Исходники с бонусными вариантами на sponsr
Сектанты могут верить, что и на ютубе появится

Лайки, комменты, подписки приветствуются )))

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

Отправка уведомлений из Power BI Report Server в Telegram чат
#АнатомияФункций – приёмы

Всем привет!
Собственно, истоки задачи тут
Идея отличная, но код…
Короче пришлось заморочиться, развернуть у себя RS и немножко написать попроще:

let 
    from = Sql.Databases(BASE){[Name="ReportServerPBI"]}[Data]{[Schema="dbo",Item="ExecutionLog3"]}[Data],
    dt = Date.StartOfDay(DateTime.LocalNow()),
    filtr = Table.SelectRows(from, each ([ItemAction] = "DataRefresh") and ([Status] = "rsInternalError") and [TimeEnd]>dt),
    cols = Table.SelectColumns(filtr,{"ItemPath","TimeStart","TimeEnd"}),
    sort = Table.Sort(cols,"TimeEnd"),
    lst = Table.ToList(sort,(x)=>Text.Format("❌ #{0}#(lf)с #{1}#(lf)по #{2}#(lf)",x)),
    txt = Text.Replace(Text.Combine(lst,"#(lf)"),"_","\_"),
    post = try Json.Document(Web.Contents("https://api.telegram.org/bot"&TOKEN&"/sendMessage", [Query = [chat_id=ID,text=txt, parse_mode = "Markdown"], Content = Text.ToBinary("")]))[ok] otherwise false ,
    tab = #table(1, {{post}}) 
in 
tab

Что тут к чему смотрим на дзене
Ютуб позже и без гарантии (тыц)

Лайки, комменты, подписки приветствуются )))

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

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

Всем привет!
Завершаем разбор задачи по поиску ключевых слов/фрагментов.
В чате был представлен отличный вариант:

let
    f=(x)=>[z1=List.PositionOf(search,x,Occurrence.First,(c,v)=>Text.Contains(v,c)),
            z2=if z1<>-1 then res{z1} else null][z2],

    dict=Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    search = List.Buffer(dict[Назначение платежа]),
    res = List.Buffer(dict[Статья расходов]),

    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
to

Но я предлагаю другой:

let
    f=(x)=>((a)=>res{List.PositionOf(search,a,Occurrence.First,(c,v)=>Text.Contains(v,c))})(Text.Lower(x)),

    dict=Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    search = List.Buffer(dict[Назначение платежа]&{""}),
    res = List.Buffer(dict[Статья расходов]&{null}),

    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    to = Table.TransformColumns(from,{"Назначение платежа",f})
in
    to

И вот как оно так и почему быстрее смотрим на дзене -
Исходники лежат на sponsr
А с ютубчиком увы… впн-ить админку, чтобы туда что-то залить – это уже перебор. (поправочка, если всё-таки зальётся, то будет тут)

Лайки, комменты, подписки приветствуются )))

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

Пишем функцию вместо записи
#АнатомияФункций – синтаксис

Всем привет!
Продолжаем решать задачку про поиск первого вхождения.
Собственно, очень сильно не хотелось использовать два Table.TransformColumns, поэтому
Вот это

let
    f=(x)=>List.Skip(dict,(y)=>not Text.Contains(x,y{0})){0}?{1}?,
    dict=List.Buffer(Table.ToList(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],(x)=>x)),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
to

Превратилось вот в это:

let
    f=(x)=>((z)=>List.Skip(dict,(y)=>not Text.Contains(z,y{0})){0}?{1}?)(Text.Lower(x)),
    dict=List.Buffer(Table.ToList(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],(x)=>x)),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    to = Table.TransformColumns(from,{"Назначение платежа",f})
in
    to

А как я дошёл до жизни такой смотрите на дзене
Исходники ищем на sponsr
А про ютуб ничего не скажу – даже студия висит в студию зашёл - сцыль

Лайки, комменты, подписки приветствуются )))

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

Хватит мучать List.Accumulate!
#АнатомияФункций – List.Accumulate, List.Skip

Всем привет!
Решил разобрать «интересный» подход к нахождению необходимого пункта по фрагменту ключевого текста (т.е. поиск по словарю с неполным соответствием).
Почему-то уже неоднократно вижу приматывание в этой ситуации решения от задачи по множественной замене в тексте. На входе примерно такой код:

let
    f=(x)=>Text.Split(List.Accumulate(dict,x,(s,c)=>Text.Replace(s,c[Назначение платежа],c[Статья расходов])),"!"){1}?,
    dict = List.Buffer(Table.ToRecords(Table.TransformColumns(Excel.CurrentWorkbook(){[Name="справочник"]}[Content],{"Статья расходов",(x)=>"!"&x&"!"}))),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="данные"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
to

Который стоит превратить вот в такой:

let
    f=(x)=>Text.Split(List.Accumulate(dict,[a=x,b=false],(s,c)=>if s[b] then s else if Text.Contains(s[a],c[Назначение платежа]) then [a=c[Статья расходов],b=true] else s)[a],"!"){1}?,
    
    dict=List.Buffer(Table.ToRecords(Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content], {"Статья расходов", (x)=>"!"&x&"!"}))),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
    to

А лучше вот в такой:

let
    f=(x)=>[a=List.Accumulate(dict,[a=x,b=false],(s,c)=>if s[b] then s else if Text.Contains(s[a],c[Назначение платежа]) then [a=c[Статья расходов],b=true] else s),
            b=if a[b] then a[a] else null][b],
    dict=List.Buffer(Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content])),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
    to

А ещё лучше… оставить в покое несчастный аккумулятор и решать задачи функциями, которые для них предназначены:

let
    f=(x)=>List.Skip(dict,(y)=>not Text.Contains(x,y{0})){0}?{1}?,

    dict=List.Buffer(Table.ToList(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],(x)=>x)),
    from = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="data"]}[Content])[[Дата проведения],[Сумма в валюте счёта],[Назначение платежа]],
    tr = Table.TransformColumns(from,{"Назначение платежа",Text.Lower}),
    to = Table.TransformColumns(tr,{"Назначение платежа",f})
in
    to

Что тут к чему и порция бомбления уже на Дзене
Файл-исходник на sponsr
Если повезёт, то и ютуб подтянется

Лайки, комменты, подписки приветствуются )))

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

Так ли плох FillDown?
#Анатомия Функций – Table.FillDown, List.Generate

Всем привет!
Прилетела в чат задачка на «FillDown не до конца» (т.е. протяжка вниз, но с доп. Условиями).
Понятно, что это решается через генератор:

let
    from = Excel.CurrentWorkbook(){[Name="ЕСТЬ"]}[Content],
    lst = List.Buffer(Table.ToList(from,(x)=>x)),
    n=List.Count(lst),
    gen = List.Generate(()=>[i=0,j=lst{i},k=0,l=j],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,j=lst{i},
                                k=List.PositionOf(j,null,Occurrence.First,(c,v)=>c<>v),
                                l=List.FirstN(x[l],k)&List.Skip(j,k)],
                        (x)=>x[l]),
    to = Table.FromList(gen,(x)=>x,Value.Type(from))
in
to

А ещё этот генератор можно подускорить и с оптимизировать под большой объем:

let
    
    f=(x)=>
    [lst = List.Buffer(Table.ToList(x,(x)=>x)),
    n=List.Count(lst),
    gen = List.Generate(()=>[i=0,k=0,l=lst{i}],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,
                                k=List.PositionOf(lst{i},null,Occurrence.First,(c,v)=>c<>v),
                                l=List.ReplaceRange(lst{i},0,k,List.FirstN(x[l],k))],
                        (x)=>x[l])][gen],
    from = Excel.CurrentWorkbook(){[Name="ЕСТЬ"]}[Content],
    gr = Table.Group(from,"Path 1",{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c<>null)),                    
    to = Table.FromList(List.Combine(gr[tmp]),(x)=>x,Value.Type(from))
in
    to

А ещё… можно использовать FillDown, просто в связке с Zip:

let
    f=(x)=>Table.ToList(x,(x)=>x),
    g=(x)=>[a=List.PositionOf(x{0},null,Occurrence.First,(c,v)=>c<>v),
            b=List.ReplaceRange(x{0},0,a,List.FirstN(x{1},a))][b],
    h=(x)=>List.Zip({f(x),f(Table.FillDown(x,nms))}),
    
    from = Excel.CurrentWorkbook(){[Name="ЕСТЬ"]}[Content],
    nms = List.RemoveLastN(Table.ColumnNames(from),3),
    gr = Table.Group(from,"Path 1",{"tmp",h},GroupKind.Local,(s,c)=>Number.From(c<>null)),
    to = Table.FromList(List.Combine(gr[tmp]),g,Value.Type(from))
in
    to

И этот последний вариант оказался самым шустрым.
Детали смотрим на дзене
Файл с исходниками доступен подписчикам на спонсоре
И когда эта хрень загрузится вот тут будет ссылка с ютуба

Лайки, комменты, подписки приветствуются )))

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

Альтернативные источники видосов

Всем привет!
Уж не знаю как вас, а меня заколебал тормозящий ютуб.
Не знаю сколько это продлится, но на всякий случай предлагаю следующие альтернативы:
дзен - https://dzen.ru/id/66ab6ee42a234a614daa87f9?share_to=link
sponsr - https://sponsr.ru/pq_m_buchlotnik/

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

Списки против записей-2
#АнатомияФункций – списки

Всем привет!
Ну вот не отпускала мысль, особенно после пересмотра прошлого видео, что что-то не так.
По этому поводу код:

let
f=(x)=>[a=dm(x{0},x{1}),
        b=Duration.TotalHours(Date.EndOfMonth(x{0})-x{0}),
        c=Duration.TotalHours(x{1}-Date.StartOfMonth(x{1})),
        d=ym(x{0})-num,
        e=x&List.Repeat({null},d),
        f=if a=0 then e&{Duration.TotalHours(x{1}-x{0})} 
        else if a=1 then e&{b,c}
        else e&{b}&List.Range(hrs,d+1,a-1)&{c}][f],
h=(x,y,z)=>if x>y then z else @h(Date.AddMonths(x,1),y,z&{x}),
ym=(x)=>Date.Year(x)*12+Date.Month(x),
dm=(x,y)=>ym(y)-ym(x),
 
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ot = Date.StartOfMonth(List.Min(from[Начало работ])),
    num = ym(ot),
    do = List.Max(from[Завершение работ]),
    lst = List.Buffer(h(ot,do,{})),
    nms = Table.ColumnNames(from)&List.Transform(lst,(x)=>DateTime.ToText(x,"yyyyMM")),
    hrs = List.Buffer(List.Transform(lst,(x)=>Duration.TotalHours(Date.EndOfMonth(x)-x))),
    to = Table.FromList(Table.ToList(from,f),(x)=>x,nms)
in
to

Ни разу не лаконичный, зато дело своё делает как надо )))

Ну а что тут к чему смотрите, как всегда, на Ютубе

Лайк, коммент, подписка приветствуются )))
Поддержка канала

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

Списки против записей, генератор против рекурсии или коротко не значит быстро
#АнатомияФункций – рекурсия, List.Generate

Всем привет!
Был запрос на разбор сложных задач с «эволюцией» решений. По этому поводу:
Исходное

let
    f=(from,to)=>[gen=List.Generate(()=>[s=from,e=List.Min({to,Date.EndOfMonth(from)})],
                                (x)=>x[s]<to,
                                (x)=>[s=Date.StartOfMonth(Date.AddMonths(x[s],1)),e=List.Min({to,Date.EndOfMonth(s)})],
                                (x)=>Record.FromList({Duration.TotalHours(x[e]-x[s])},{DateTime.ToText(x[s],"yyyyMM")})),
                tbl=Table.FromRecords({[Начало=from,Окончание=to]&Record.Combine(gen)})][tbl],
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = Table.ToList(from,(x)=>f(x{0},x{1})),
    to = Table.Combine(lst)
in
to

Чутка поправленное

let
    f=(x,y,z)=>if Date.EndOfMonth(x)>y 
                then Record.AddField(z,DateTime.ToText(x,"yyyyMM"),Duration.TotalHours(y-x))
                else @f(Date.StartOfMonth(Date.AddMonths(x,1)),y,Record.AddField(z,DateTime.ToText(x,"yyyyMM"),Duration.TotalHours(Date.EndOfMonth(x)-x))),
    
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Buffer(Table.ToList(from,(x)=>f(x{0},x{1},[Начало=x{0},Окончание=x{1}]))),
    nms = List.Distinct(List.Combine(List.Transform(lst,Record.FieldNames))),
    to = Table.FromRecords(lst,nms,MissingField.UseNull)
in
    to

С радикально изменённой логикой:

let
    
f=(x)=>[a=g(x{0},x{1},{}),
        b=List.PositionOf(tr,DateTime.ToText(x{0},"yyyyMM")),
        c=x&List.ReplaceRange(nul,b,List.Count(a),a)][c],
g=(x,y,z)=>if Date.EndOfMonth(x)>y 
                then z&{Duration.TotalHours(y-x)}
                else @g(Date.StartOfMonth(Date.AddMonths(x,1)),y,z&{Duration.TotalHours(Date.EndOfMonth(x)-x)}),
    
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ot = Date.StartOfMonth(List.Min(from[Начало работ])),
    do = List.Max(from[Завершение работ]),
    gen = List.Generate(()=>ot,(x)=>x<do,(x)=>Date.AddMonths(x,1)),
    tr = List.Buffer(List.Transform(gen,(x)=>DateTime.ToText(x,"yyyyMM"))),
    nul = List.Buffer(List.Repeat({null},List.Count(tr))),

    to = Table.FromList(Table.ToList(from,f),(x)=>x,Table.ColumnNames(from)&tr)
in
    to

И немножко допиленное:

let
    
f=(x)=>x&List.Repeat({null},List.PositionOf(tr,DateTime.ToText(x{0},"yyyyMM")))&g(x{0},x{1},{}),
g=(x,y,z)=>if Date.EndOfMonth(x)>y 
                then z&{Duration.TotalHours(y-x)}
                else @g(Date.StartOfMonth(Date.AddMonths(x,1)),y,z&{Duration.TotalHours(Date.EndOfMonth(x)-x)}),
h=(x,y,z)=>if x>y then z else @h(Date.AddMonths(x,1),y,z&{x}),
    
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ot = Date.StartOfMonth(List.Min(from[Начало работ])),
    do = List.Max(from[Завершение работ]),
    tr = List.Buffer(List.Transform(h(ot,do,{}),(x)=>DateTime.ToText(x,"yyyyMM"))),
    to = Table.FromList(Table.ToList(from,f),(x)=>x,Table.ColumnNames(from)&tr)
in
    to

Ну а что тут к чему смотрите, как всегда, на Ютубе

Лайк, коммент, подписка приветствуются )))
Поддержка канала

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

Третья часть залита, стартуем в полдень
https://youtu.be/_AzMIwSaB78

Вторая часть уже на ютубе, начинаем через пару минут )))
https://youtu.be/bEjeAkT6F_Y