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

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

Вторая часть уже на ютубе, начинаем через пару минут )))

На пальцах - серия видео #АнатомияФункций - основы Всем привет! По запросу страждущих решил записать серию видосов про самые основы написания кода на М. Текущий план такой: 1 - переменные 2 - функции 3 - среды Пункты плана по мере выхода видосов будут превращаться в ссылки. Насколько это "на пальцах" покажет количество отписчиков от канала. А так первое видео уже залито. Лайк, коммент, подписка приветствуются ))) Надеюсь, было полезно. Всех благ! @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

Приматываем РЕГУЛЯРКИ (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

Удаляем текст в скобках (без регулярок и СМС) #АнатомияФункций – приёмы Всем привет! Сегодня разбираем задачку «Как можно в столбце удалить все значения в скобках?». Для начала решим «строго по условию»:

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

Удаление пустых строк (а на списках точно быстрее?) #АнатомияФункций – 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

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

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

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