Splitter 7 - Splitter.SplitTextByPositions, Splitter.SplitTextByRanges или и вот так тоже можно
#АнатомияФункций - Splitter.SplitTextByPositions, Splitter.SplitTextByRanges

Всем привет!
Завершая тему сплиттеров, рассматриваем следующих пациентов:

Splitter.SplitTextByPositions(positions as list, optional startAtEnd as nullable logical)
Splitter.SplitTextByRanges(ranges as list, optional startAtEnd as nullable logical)

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

Кейс 1 – разобрать текст, не затирая теги (или по простому – поделить текст по позициям одного разделителя и по позициям следующим за другим разделителем):

let
    txt= "<info><recnumber=1 type=A date=12.04.2012><name><first>Имя1</first><last>Фамилия1</last><medium>Отчество1</medium></name><doc><type>паспорт</type><number>23465</number></doc></info><info><recnumber=2 type=D date=15.04.2012><name><first>Имя2</first><last>Фамилия2</last><medium>Отчество2</medium></name><doc><type>св-во</type><number>98745</number></doc></info>",
pos = Text.PositionOf(txt,"<",Occurrence.All),
pos1 = List.Transform(Text.PositionOf(txt,">",Occurrence.All),(x)=>x+1),
lst = List.Sort(List.Distinct(pos&pos1)),
splt=Splitter.SplitTextByPositions(lst)(txt),
to = List.Select(splt,(x)=>Text.Trim(x)<>"")
in
to

Кейс 2 – пишем отсутствующий в 2016 Splitter.SplitTextByCharacterTransition:

let
    txt = "картошка 123морковка 29свёкла 11лук 14чеснок 13",
    lst = Splitter.SplitTextByRepeatedLengths(1)(txt),
    zip = List.Zip({lst,{""}&List.RemoveLastN(lst,1)}),
    f=(x)=>Text.Contains("0123456789",x),
    g=(x)=>not f(x{0}) and f(x{1}),
    pos = List.PositionOf(zip,g,Occurrence.All,(c,v)=>v(c)),
    to = Splitter.SplitTextByPositions(pos)(txt)
in
    to

Кейс 3 – делим текст по цифро-дефисовой последовательности, заканчивающейся точкой:

let
    
    txt = "1. Текст 1-1. Текст с пробелами 1-2. текст с числами 123 2. ещё текст 3. текст. 4. а текст-то бывает разный... издевательство 5. например с числами 2.5 12. и номеров много 123. очень много 1234. прям совсем",
    f=(x)=>Text.Contains("0123456789.-",x),
    g=(x)=>Text.Contains("0123456789",x),
    lst=Splitter.SplitTextByRepeatedLengths(1)(txt),
    tbl = Table.FromList(lst,(x)=>{f(x),g(x),x="."},{"flag","num","dot"}),
    add = Table.AddIndexColumn(tbl,"ind"),
    gr = Table.Group(add,"flag",{{"pos",(x)=>x{0}[ind]},{"dot",(x)=>List.Last(x[dot])=true},{"nums",(x)=>List.AnyTrue(x[num])}},GroupKind.Local),
    pos = Table.SelectRows(gr,(x)=>x[dot] and x[nums])[pos],
    to = Splitter.SplitTextByPositions(pos)(txt)
in
    to

Кейс 4 – эту задачку мы уже разбирали – только теперь решим её по-другому и выясним, что Splitter.SplitTextByRanges никакой не сплиттер, а профессиональный «выниматель подстрок» :

let
    from = Excel.CurrentWorkbook(){[Name="Таблица110"]}[Content],
    f=(x)=>[a=Splitter.SplitTextByRepeatedLengths(1)(x),
            b=(x)=>Text.Contains("0123456789 ",x),
            c=List.Zip({a,{""}&List.RemoveLastN(a,1)}),
            d=Text.PositionOf(x,"бит/с",Occurrence.Last),
            e=List.PositionOf(c,null,Occurrence.All,(c,v)=>b(c{0}) and not b(c{1})),
            f=List.Max(e,null,(x)=>if x > d then 0 else x),
            g={{0,Text.Length(x)},{f,d-f-2},{d-1,6}},
            z=Splitter.SplitTextByRanges(g)(x)][z],
    splt = Table.SplitColumn(from,"Custom",f,{"Custom","скока","чего"}),
    to = Table.TransformColumns(splt,{"скока",Number.From})
in
    to

Как вы догадываетесь, подробное описание того, что тут к чему, смотрим на Ютубе

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

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

LocalNow vs FixedLocalNow или который час?
#АнатомияФункций - DateTime.LocalNow, DateTime.FixedLocalNow, DateTimeZone.UtcNow, DateTimeZone.FixedUtcNow , DateTimeZone.LocalNow, DateTimeZone.FixedLocalNow

Всем привет!

В каментах просили разобрать разницу между функциями получения текущих даты и времени. Базово функций три:
DateTime.LocalNow – возвращает системные дату и время;
DateTimeZone.LocalNow – возвращает системные дату, время и часовой пояс
DateTimeZone.UtcNow – возвращает всемирное координированное время

При этом у каждой есть её фиксированный вариант - DateTime.FixedLocalNow, DateTimeZone.FixedLocalNow, DateTimeZone.FixedUtcNow.
Идея фиксации состоит в том, что в ходе запроса функция вычисляется ровно один раз и уже не меняет своё значение при множественных вызовах.

Давайте сравним:

let
    tr = List.Count(List.Distinct(List.Transform({1..100000},(x)=>DateTime.LocalNow()))),//<>1
    tbl = Table.RowCount(Table.Distinct(Table.FromList({1..100000},(x)=>{DateTime.LocalNow()}))),//<>1
    acc =List.Count(List.Distinct(List.Accumulate({1..1000},{},(x,y)=>x&{DateTime.LocalNow()})))//<>1
in
acc

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

Но при этом фиксированные варианты дадут:

let
    tr = List.Count(List.Distinct(List.Transform({1..100000},(x)=>DateTime.FixedLocalNow()))),//1
    tbl = Table.RowCount(Table.Distinct(Table.FromList({1..100000},(x)=>{DateTime.FixedLocalNow()}))),//1
    acc =List.Count(List.Distinct(List.Accumulate({1..1000},{},(x,y)=>x&{DateTime.FixedLocalNow()})))//1
in
    acc

Т.е. видим, что действительно на каждом шаге ровно одно уникальное датавремя.

Если кому-то принципиальны доли секунды и он уже побежал переписывать все свои запросы – остановитесь! И посмотрите следующий код:

let
    now = DateTime.LocalNow(),
    tr = List.Count(List.Distinct(List.Transform({1..100000},(x)=>now))),//1
    tbl = Table.RowCount(Table.Distinct(Table.FromList({1..100000},(x)=>{now}))),//1
    acc =List.Count(List.Distinct(List.Accumulate({1..1000},{},(x,y)=>x&{now})))//1
in
    acc

Т.е. если вы, как и я, являетесь противниками множественных вызовов и используете переменные (в данном случае now), то проблемы у вас нет – функция вызывается один раз при вычислении переменной. Заодно это немного (процентов на 5%) ускоряет вычисления – мелочь, а приятно.

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

let
    now = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),3)),
    now1 = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),3)),
    now2 = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),3)),
    now3 = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),3))
in
    now3

Как-то так. Нет «правильных» или «неправильных» функций, есть вполне конкретное их поведение, которое нужно учитывать при написании кода )))

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

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

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

Xml.Document vs Xml.Tables, рекурсивный обход и слетевшая кодировка
#АнатомияФункций - Xml.Document

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

let
    f=(x)=>Text.FromBinary(Text.ToBinary(x,866)),
    g=(x)=>[a=Table.Buffer(x[[Name],[Value]]),
            b=Table.SelectRows(a,(r)=>r[Value] is table),
            c=Table.SelectRows(a,(r)=>not (r[Value] is table)),
            d=if Table.RowCount(b)=0 then a else c & @g(Table.Combine(Table.ToList(b,h)))][d],
    h=(x)=>Table.TransformColumns(x{1},{"Name",(r)=>x{0}&"/"&r}),
    
    from = Folder.Files("C:\Users\muzyk\Desktop\XML_файлы")[[Name],[Content]],
    tr = Table.TransformColumns(from,{{"Name",f},{"Content",(x)=>Record.FromTable(g(Xml.Document(x)))}}),
    nms = List.Distinct(List.Combine(List.Transform(tr[Content],Record.FieldNames))),
    to = Table.ExpandRecordColumn(tr,"Content",nms)
in
to

Ну а что тут к чему смотрите, как всегда, на Ютубе (https://youtu.be/OsfOCQZvBRM)

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

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

Удаление пустых строк (а на списках точно быстрее?)
#АнатомияФункций – List.RemoveMatchingItems, List.RemoveNulls, List.Repeat, Record.FromList

Всем привет!
В чате задали вопрос про удаление пустых строк, а именно: является ли мышкоклацный код слишком навороченным:

let
    laiyuan = Excel.CurrentWorkbook(){[Name="biao"]}[Content],
    jieguo = Table.SelectRows(laiyuan, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    jieguo

Ответ – нет, вполне прозрачный и логичный код, достаточно универсальный.
Но вот если в таблице пустые строки содержат только null (а именно так было у автора вопроса), то действительно, можно и подсократить:

let
    laiyuan = Excel.CurrentWorkbook(){[Name="biao"]}[Content],
    jieguo = Table.SelectRows(laiyuan,(j)=>List.RemoveNulls(Record.ToList(j))<>{})
in
    jieguo

А сократив, стоит задуматься о вычислительной эффективности и, например, переписать так:

let
    laiyuan = Excel.CurrentWorkbook(){[Name="biao"]}[Content],
    liebiao = List.Buffer(List.Repeat({null},Table.ColumnCount(laiyuan))),
    jieguo = Table.SelectRows(laiyuan,(j)=> Record.ToList(j)<> liebiao)
in
    jieguo

А ещё лучше так:

let
    laiyuan = Excel.CurrentWorkbook(){[Name="biao"]}[Content],
    jilu = Record.FromList(List.Repeat({null},Table.ColumnCount(laiyuan)),Table.ColumnNames(laiyuan)),
    jieguo = Table.SelectRows(laiyuan,(j)=>j<>jilu)
in
    jieguo

Ну а насколько лучше получился код и вообще, что тут к чему смотрите , как всегда, на Ютубе

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

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

Удаляем текст в скобках (без регулярок и СМС)
#АнатомияФункций – приёмы

Всем привет!
Сегодня разбираем задачку «Как можно в столбце удалить все значения в скобках?».

Для начала решим «строго по условию»:

let
    f=(x)=>[a=List.Transform(List.Split({-1}&List.RemoveLastN(Text.PositionOfAny(x,{"(",")"},Occurrence.All),1),2),(x)=>{x{0}+1,x{1}-x{0}-1}),
            b=Text.Combine(Splitter.SplitTextByRanges(a)(x))][b],
    
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
to

Делим строго по скобкам… но как-то сложно и медленно.
ОК, что насчёт рекурсии?

let
    f=(x)=>[a=Text.PositionOf(x,"("),
            b=if a = -1 then x else @f(Text.RemoveRange(x,a,Text.PositionOf(x,")")-a+1))][b],
    
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
    to

Так, как минимум, быстрее, да и поприкольнее.

Вот только надо ли оно всё?
Если больше не к чему привязаться – надо, а в конкретном случае можно:

let
    f=(x)=>Text.Combine(List.Transform(Text.Split(x,", "),(x)=>Text.Split(x,"("){0}),", "),
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
    to

let
    f=(x)=>Text.Combine(List.Select(Text.SplitAny(x,"(,"),(x)=>not Text.Contains(x,")")),","),    
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
    to

let
    f=(x)=>Text.Combine(List.Alternate(Text.SplitAny(x,"(,"),1,1,1),", "),    
    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
    to

Бонус на формулах и объяснение того, что тут к чему, как всегда, смотрим на Ютубе
Лайк, коммент, подписка приветствуются )))

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

Приматываем РЕГУЛЯРКИ (RegExp)
#АнатомияФункций – приёмы

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

let
    f=(x)=>Web.Page("<script>document.write('"&x&"'.replace(/\([^()]+\)/g,''))</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text],

    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    to = Table.TransformColumns(from,{"Столбец1",f})
in
to

Код ни разу не громоздкий, через регулярки…
Так почему я назвал это костылями?
А потому что на них бегать неудобно. И на самом деле код должен выглядеть как-то так:

let
    f=(x)=>Web.Page("<script>document.write('"&x&"'.replace(/\([^()]+\)/g,''))</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text],

    from = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    lst = List.Buffer(Text.Split(f(Text.Combine(from[Столбец1],"@")),"@")),
    add = Table.AddIndexColumn(from,"i"),
    to = Table.CombineColumns(add,{"i","Столбец1"},(x)=>lst{x{0}},"Столбец1")
in
    to

Ну а почему и зачем так – смотрите на Ютубе
Спойлер – там пригорает )))

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

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

Xml.Document – рекурсивный обход полей
#АнатомияФункций - Xml.Document, рекурсия

Всем привет!
В чат закинули задачку на рекурсивный обход полей внутри Xml-документа.
Собственно, код:

let
    lst = {"Name", "Value", "Attributes"},
    f=(x,y)=>[
    tr = Table.TransformColumns(x,{"Attributes",(x)=>if x is table then Record.FromTable(x) else []}),
    nms = List.Buffer(List.Distinct(List.Combine(List.Transform(tr[Attributes],Record.FieldNames)))),
    nms1 = List.Buffer(Table.ColumnNames(tr)),
    nms2 = List.Transform(nms,(x)=>if List.Contains(nms1,x) then x&Text.From(y) else x),
    exp = Table.ExpandRecordColumn(tr,"Attributes",nms,nms2),
    exp1 = Table.ExpandTableColumn(exp,"Value",lst,List.ReplaceValue(lst,"Name","Name"&Text.From(y),Replacer.ReplaceValue)),
    res = if List.NonNullCount(exp1[Attributes]) = 0 then exp1 else @f(exp1,y+1)][res],
    
    
    from = Xml.Document(File.Contents("C:\путь\Пример файла.xml"), 1251),
    to = f(from,1)

in
    to

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

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

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

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

Всем привет!
По запросу страждущих решил записать серию видосов про самые основы написания кода на М.
Текущий план такой:
1 - переменные
2 - функции
3 - среды

Пункты плана по мере выхода видосов будут превращаться в ссылки.
Насколько это "на пальцах" покажет количество отписчиков от канала.
А так первое видео уже залито.

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

Надеюсь, было полезно.
Всех благ!
@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

Списки против записей-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