Рекурсия, Folder.Contents или побеждаем слишком длинные имена файлов
#АнатомияФункций - Folder.Contents

Всем привет!

Продолжаем тему рекурсии. Сегодня побеждаем слишком длинные имена файлов, а для этого вместо Folder.Files используем Folder.Contents и пишем рекурсивный обход содержимого.

let
    from = Folder.Contents("C:\Users\muzyk\Desktop\папка1"),
    f=(x)=>[a=Table.SelectRows(x,(r)=>r[Extension]<>""),
            b=Table.SelectRows(x,(r)=>r[Extension]="")[Content],
            c=if b={} then x else @f(List.Accumulate(b,a,(s,c)=>[a=try Table.RowCount(c) otherwise 0,
                                                                    b=if a=0 then s else s&c][b]))][c],
    to=f(from)
in
to

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

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

Получаем форматы для ячеек - жирный шрифт, жёлтая заливка и т.д.
#АнатомияФункций - Xml.Tables, Xml.Document

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

let
    file = File.Contents("путь/файл.xlsx"),
    unz = fxUnzip(file),
    book = Table.SelectRows(Excel.Workbook(file,false),(r)=>r[Kind]="Sheet"),
    style = Xml.Tables(unz{[Name="xl/styles.xml"]}[Value]),
    cellstyle=Table.Buffer(style{[Name="cellXfs"]}[Table]{0}[xf]),
    font=style{[Name="fonts"]}[Table]{0}[font],
    fill=style{[Name="fills"]}[Table]{0}[fill],
    n=1,
    sheet=book{n-1}[Data],
    sheetdata=Xml.Document(unz{[Name="xl/worksheets/sheet"&Text.From(n)&".xml"]}[Value]){0}[Value]{[Name="sheetData"]}[Value],
    f=(x)=>Number.From(x{0}[Attributes]{[Name="s"]}?[Value]?),
    tr=Table.TransformColumns(sheetdata,{{"Attributes",(x)=>x{[Name="r"]}[Value]},{"Value",f}}),
    dict=Record.FromList(tr[Value],tr[Attributes]),
    min=List.Min(List.Transform(tr[Attributes],Number.From)),
    add=Table.AddIndexColumn(sheet,"row",min),

    tr1=Table.TransformColumns(add,{"row",(x)=>Record.FieldOrDefault(dict,Text.From(x))}),
    tr2=Table.TransformColumns(tr1,{"row",(x)=>if x=null then null else cellstyle{x}}),
    tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fontId"]?),b=if a=null then null else font{a}[b]][b]}),
    skip = Table.PromoteHeaders(Table.Skip(tr3,(r)=>r[Column7]=null)),
    to = Table.SelectRows(skip, each ([Column8] = ""))
    /*цвет шрифта - tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fontId"]?),b=if a=null then null else font{a}[color]{0}[#"Attribute:rgb"]?][b]}) 
    цвет заливки - tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fillId"]?),b=if a=null then null else fill{a}[patternFill][fgColor]?{0}?{0}?[#"Attribute:rgb"]?][b]})*/
in
to

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

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

MCMLXXXVIII=1988 или преобразуем римскую запись числа в нормальную
#АнатомияФункций - List.Zip

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

let
    unroman=(txt)=>[
    dct=[I=1,V=5,X=10,L=50,C=100,D=500,M=1000],
    lst=Text.ToList(txt),
    tr=List.Transform(lst,(x)=>Record.Field(dct,x)),
    zip=List.Zip({tr,List.Skip(tr)&{0}}),
    tr2=List.Transform(zip,(x)=>if x{0}<x{1} then -x{0} else x{0}),
    to = List.Sum(tr2)][to],
    
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    to = Table.AddColumn(from,"arab",(r)=>unroman(r[r]))
in
to

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

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

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

Всем привет!
Подкинули интересную задачку на группировку – ничего сложного, просто преобразование по условию и разное для первой и последующих строк. Чего-то накатило сделать через Table.ReplaceRows. По этому поводу код:

let
    from = Excel.CurrentWorkbook(){[Name="headcount"]}[Content],
    f=(x)=>[a=Table.Sort(x,{"headcount_Ставки, ед изм",Order.Descending}),
            b=Table.TransformColumns(a,{"Ставки_Основное место",(x)=>"нет,внешний"}),
            c=Table.ReplaceRows(b,0,1,{b{0}&[Ставки_Основное место="да,внешний"]}),
            d=if List.Contains(x[Ставки_Основное место],"да") then x else c][d],
    gr = Table.Group(from,{"Ставки_Позиция","Период_int","Горизонт_маркер"},{"tmp",f}),
    to=Table.Combine(gr[tmp])
in
    to

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

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

Record.HasFields или проверка наличия защиты листов книги
#АнатомияФункций - Record.HasFields

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

let
    f=(x)=>[
    file = Binary.Buffer(x),
    book = Table.SelectRows(Excel.Workbook(file,false),(r)=>r[Kind]="Sheet"),
    xml = Table.SelectRows(fxUnzip(file),(r)=>Text.Contains(r[Name],"xl/worksheets/sheet"))[Value],
    f=(x)=>Record.HasFields(Xml.Tables(x){[Name="sheetProtection"]}?[Table]?{0}? ?? [],{"Attribute:hashValue","Attribute:sheet","Attribute:sort","Attribute:autoFilter"}),
    lst = List.Transform(xml,f),
    tbl = Table.FromColumns(Table.ToColumns(book)&{lst},Table.ColumnNames(book)&{"protection"})][tbl],
    folder=Folder.Files("путь к папке")[[Folder Path],[Name],[Content]],
    tr = Table.TransformColumns(folder,{"Content",f}),
    exp = Table.ExpandTableColumn(tr,"Content",{"Item","protection"}),
    to = Table.SelectRows(exp,(r)=>r[protection]=false)
in
to

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

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

Словари на записях против Table.Buffer
#АнатомияФункций – приёмы

Всем привет!
Мне опять попалось на глаза странное видео. Ещё раз убедился, что не зря не приветствую рекламу ютуб-каналов в чате.
Пока у меня пригорало записал видос.
Интересующиеся найдут там про Table.Buffer, яркий пример про (x)=> вместо each _ и даже про DateTime.ToText вместо даже не буду говорить чего.

А вообще задачка про на словари на записях (неожиданно, правда?).
Свой код привожу:

let
    tbl=Excel.CurrentWorkbook(){[Name="даты"]}[Content],
    tr = Table.TransformColumns(tbl,{"month",(x) as text=>DateTime.ToText(x,"yyyy MMMM")}),
    f=(x)=>List.Transform({0..Duration.Days(x{2}-x{1})},(y)=>Text.From(Date.From(Number.From(x{1})+y))),
    cmb = Table.CombineColumns(tr,List.Skip(Table.ColumnNames(tr)),f,"tmp"),
    exp = Table.ExpandListColumn(cmb,"tmp"),
    dict=Record.FromList(exp[month],exp[tmp]),
    from = Excel.CurrentWorkbook(){[Name="база"]}[Content],
    to = Table.AddColumn(from,"month",(x)=>Record.Field(dict,Text.From(Date.From(x[Дата]))))
in
to

А вот не свой не привожу – я против распространения порнографии.

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

Упомянутый в видео мой курс по pq

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

Решаем через Splitter
#АнатомияФункций – приёмы

Всем привет!

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

let
    lst=List.Buffer({"0".."9"," "}),
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][Custom],
    f=(x)=>[a=Splitter.SplitTextByEachDelimiter({"/"},null,true)(x),
            b=Splitter.SplitTextByEachDelimiter({" "},null,true)(a{0}),
            c=Splitter.SplitTextByCharacterTransition((x)=>not List.Contains(lst,x),lst)(b{0}),
            d=Combiner.CombineTextByDelimiter("")(Splitter.SplitTextByDelimiter(" ")(List.Last(c))),
            z={x,b{1}&"/c",Number.From(d)}][z],
    to = Table.FromList(from,f)
in
to

Ну и жду реакции – оно вообще интересно или нет? Потому как если интересно – буду писать развернутый пост на тему.

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

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

Splitter 0 - обзор
#АнатомияФункций - Splitter

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

Мы уже выяснили
- что не функциями Text едиными можно пользоваться при разделении текста
- выяснили, причём неоднократно, что делить текст можно не только по разделителю
- касались вопросов быстродействия при использовании как самих функций, так и в их окружении
-ну и в целом говорили о том, что вообще-то можно по-разному и это дела вкуса/привычки/личных предпочтений

По этому поводу хочется сделать комплексный обзор всех функций класса Splitter.

Привожу план, отдельные пункты которого по мере написания постов будут превращаться в гиперссылки:
1. Splitter.SplitByNothing – смотрим на сплиттер, который «ничего не делает» )
2. Splitter.SplitTextByWhitespace – обсуждаем, что такое Whitespace и зачем нужен аргумент quoteStyle
3. Splitter.SplitTextByDelimiter, Splitter.SplitTextByAnyDelimiter – обсуждаем, только ли в количестве разделителей разница (спойлер – нет)
4. Splitter.SplitTextByEachDelimiter – разбираемся зачем он такой нужен (кто плачет по регуляркам – он нужен)
5. Splitter.SplitTextByCharacterTransition – часто фигурирует в коде, поэтому просто закрепим как/зачем/почему
6. Splitter.SplitTextByRepeatedLengths, Splitter.SplitTextByLengths – делим текст по длинам фрагментов
7. Splitter.SplitTextByPositions, Splitter.SplitTextByRanges – делим текст по позициям элементов в нём

Как-то вот так, план амбициозный, ставим лайк, если одобряем.

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

Splitter 1 - Splitter.SplitByNothing или сплиттер, который "ничего не делает"
#АнатомияФункций - Splitter.SplitByNothing

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

Splitter.SplitByNothing()  //просто сплиттер как функция
Splitter.SplitByNothing()("abc")  //вызов его от аргумента "abc"

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

Splitter.SplitByNothing()("abc")  //{"abc"}
Splitter.SplitByNothing()(123)  //{123}
Splitter.SplitByNothing()({1,2,3})   //{{1,2,3}}

И вот теперь вопрос – зачем нужен сплиттер, который не меняет исходное значение?
В качестве примера рассмотрим задачу: в таблицу после столбца “d” нужно вставить ещё три столбца ("x","y" и "z") , заполненные нулями.
Стандартное решение через Table.SelectColumns выглядит как-то так:

let
    from = #table({"a".."h"},List.Repeat({{1..8}},5)),
    nms = Table.ColumnNames(from),
    lst = {"x","y","z"},
    tbl = Table.SelectColumns(from,List.RemoveLastN(nms,(x)=>x<>"d")&lst&List.LastN(nms,(x)=>x<>"d"),MissingField.UseNull),
    to = Table.TransformColumns(tbl,List.Transform(lst,(x)=>{x,(i)=>0}))
in
to

Но ведь можно использовать и другую логику – разделить этот самый столбец "d"… просто не разделяя его:

let
    from = #table({"a".."h"},List.Repeat({{1..8}},5)),
    to = Table.SplitColumn(from,"d",Splitter.SplitByNothing(),{"d","x","y","z"},0)
in
    to

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

let
    from = #table({"a".."h"},List.Repeat({{1..8}},5)),
    to = Table.SplitColumn(from,"d",(x)=>{x},{"d","x","y","z"},0)
in
    to

Или даже так

let
    from = #table({"a".."h"},List.Repeat({{1..8}},5)),
    to = Table.SplitColumn(from,"d",(x)=>{x,0,0,0},{"d","x","y","z"})
in
    to

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

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

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

Splitter 2 - Splitter.SplitTextByWhitespace, или сплиттер, "делящий по белому пробелу"
#АнатомияФункций - Splitter.SplitTextByWhitespace

Всем привет!
Продолжаем разбор сплиттеров. Сегодняшний наш пациент

Splitter.SplitTextByWhitespace(optional quoteStyle as nullable number) as function

Делит строковое значение по whitespace character (пробельному символу) и имеет необязательный аргумент quoteStyle.
Обращаю на это внимание – пробельный символ – это не просто пробел (U+0020), это ещё и горизонтальная табуляция (U+0009), перенос строки (U+000A), возврат каретки (U+000D), неразрывный пробел (U+00A0) – всего 25 символов (по крайней мере в соответствии со стандартом Юникода).
Поэтому

Splitter.SplitTextByWhitespace()("мама мыла раму") //{"мама","мыла","раму"}
Splitter.SplitTextByWhitespace()("мама#(tab)   мыла#(tab)раму") //{"мама","мыла","раму"}
Splitter.SplitTextByWhitespace()("мама#(00A0)  мыла #(00A0)раму") //{"мама","мыла","раму"}
Splitter.SplitTextByWhitespace()("мама#(cr,lf)мыла#(lf,cr,tab)раму") //{"мама","мыла","раму"}

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

Теперь quoteStyle - представлен в двух вариантах: QuoteStyle.Csv и QuoteStyle.None. Первый используется по умолчанию, т.е.

Splitter.SplitTextByWhitespace()

Это то же самое, что

Splitter.SplitTextByWhitespace(QuoteStyle.Csv)

Рассмотрим разницу на примере:

Splitter.SplitTextByWhitespace()("ООО ""Рога и копыта"" Москва") //{"ООО","Рога и копыта","Москва"}
Splitter.SplitTextByWhitespace(QuoteStyle.None)("ООО ""Рога и копыта"" Москва") //{"ООО","""Рога","и","копыта""","Москва"}

Т.е. в случае QuoteStyle.Csv, когда встречается кавычка, весь последующий текст оставляется в неизменном виде, пока не встретится следующая кавычка; сами кавычки при этом удаляются.
А в случае QuoteStyle.None кавычки воспринимаются просто как один из символов и если между ними есть пробельные символы – по ним произойдет разделение.

Вот такой, на самом деле весьма полезный сплиттер, если понимать, что он делает и зачем нужен необязательный аргумент )))
Ну а как получить список из всех 25 пробельных символов, не зачитываясь стандартом Юникода, смотрите в обзоре на Ютубе
(и да, я в курсе, что можно было просто залезть в англоязычную википедию - но это скучно и без сплиттеров, вот ))))

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

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

Splitter 3 - Splitter.SplitTextByDelimiter, Splitter.SplitTextByAnyDelimiter или когда порядок имеет значение…
#АнатомияФункций - Splitter.SplitTextByDelimiter, Splitter.SplitTextByAnyDelimiter

Всем привет!
Продолжаем разбор сплиттеров. Сегодняшние пациенты:

Splitter.SplitTextByDelimiter(delimiter as text, optional quoteStyle as nullable number) as function
Splitter.SplitTextByAnyDelimiter(delimiters as list, optional quoteStyle as nullable number, optional startAtEnd as nullable logical) as function

Первый делит текс по указанному разделителю, второй – по набору разделителей.
Вместо кучи слов смотрим на пример:

let
    from = #table({"num","txt"},{{1,"мама мыла раму"},{2,"мама  мыла раму"},{3,"мама,мыла, раму"},{4,"мамаоченьмылачастораму"}}),
    to = Table.SplitColumn(from,"txt",Splitter.SplitTextByDelimiter(" ")),
    to1 = Table.SplitColumn(from,"txt",Splitter.SplitTextByAnyDelimiter({" "})),
    to2 = Table.SplitColumn(from,"txt",Splitter.SplitTextByAnyDelimiter({" ","  "})),
    to3 = Table.SplitColumn(from,"txt",Splitter.SplitTextByAnyDelimiter({"  "," "})),
    to4 = Table.SplitColumn(from,"txt",Splitter.SplitTextByAnyDelimiter({"  "," ",", ",","})),
    to5 = Table.SplitColumn(from,"txt",Splitter.SplitTextByAnyDelimiter({"  "," ",", ",",","очень","часто"}))
in
    to5

to, to1 - поделилось только по пробелу
to2,to3 - сравниваем результат, выясняем, что порядок имеет значение
to4, to5 – просто демонстрация, что набор разделителей может быть разным

Что касается дополнительных аргументов, тоже есть кейс:

let
    from = Excel.CurrentWorkbook(){[Name="Таблица"]}[Content],
    to = Table.AddColumn(from,"Номер",(x)=>List.Last(Splitter.SplitTextByDelimiter("ИИН/БИН ",QuoteStyle.None)(x[#"Наименование бенефициара / отправителя"]))),
    to1 = Table.AddColumn(from,"Номер",(x)=>List.Last(Splitter.SplitTextByAnyDelimiter({"ИИН/БИН "},null,true)(x[#"Наименование бенефициара / отправителя"])))
in
    to1

to -решили проблему через quoteStyle, to1 – через startAtEnd

Ну и на закуску просто прикольный пример, тоже про сплиттеры )))

let
    from = Excel.CurrentWorkbook(){[Name="Таблица"]}[Content],
    f=(x)=>Function.Invoke(Record.FromList,List.Zip(List.Split(Splitter.SplitTextByAnyDelimiter({", "," "})(x),2))),
    tr = Table.TransformColumns(from,{"Данные",f}),
    nms = List.Distinct(List.Combine(List.Transform(tr[Данные],Record.FieldNames))),
    to = Table.ExpandRecordColumn(tr, "Данные",nms)
in
to

А что тут к чему смотрите в обзоре на Ютубе

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

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

Splitter 4 - Splitter.SplitTextByEachDelimiter или когда количество имеет значение…
#АнатомияФункций - Splitter.SplitTextByEachDelimiter

Всем привет!
Продолжаем тему сплиттеров, сегодняшний пациент:

Splitter.SplitTextByEachDelimiter(delimiters as list, optional quoteStyle as nullable number, optional startAtEnd as nullable logical)

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

let
    from = Excel.CurrentWorkbook(){[Name="Таблица110"]}[Content],
    f=(x)=>Splitter.SplitTextByEachDelimiter({" "},null,true)(x){1},
    to = Table.AddColumn(from,"new",(r)=>f(r[#"Наименование бенефициара / отправителя"]))
in
    to

Также можно задать список разделителей и делить текст по мере их нахождения в нём:

let
    from = Excel.CurrentWorkbook(){[Name="Таблица5"]}[Content][сотрудник],
    to = Table.FromList(from,Splitter.SplitTextByEachDelimiter({". "," ",", ",", ",", "}))
in
to

Демонстрацию работы вышеприведённого кода смотрите в обзоре на Ютубе

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

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

Splitter 5 - Splitter.SplitTextByCharacterTransition или List vs Text
#АнатомияФункций - Splitter.SplitTextByCharacterTransition

Всем привет!
Продолжаем тему сплиттеров. До этого мы делили текст по разделителю (подстроке), который удалялся в результате разделения. Теперь рассматриваем варианты, когда нужно разделить, но сохранить все символы.
Сегодняшний пациент

Splitter.SplitTextByCharacterTransition(before as anynonnull, after as anynonnull) as function

Два аргумента - before и after - определяют наборы символов по переходу с которых на которые нужно делить. Каждый аргумент может быть представлен либо списком символов, либо функцией от символа, возвращающей true/false. Ну и смотрим пример:

let
    from = {"GC2SB230","OF+65HB","GC2SB250","GC2KP250S"},
    /* надо получить {"GC2SB","OF+","GC2SB","GC2KP"} 
    - т.е. удалить последний набор цифр и всё, что после него*/
    
    to = List.Transform(from,(x)=>Text.Combine(List.RemoveLastN(Splitter.SplitTextByCharacterTransition({"A".."Z","+"},{"0".."9"})(x),1))),
    
    to1 = List.Transform(from,(x)=>Text.Combine(List.RemoveLastN(Splitter.SplitTextByCharacterTransition((x)=>not List.Contains({"0".."9"},x),{"0".."9"})(x),1))),
    
    lst = List.Buffer({"0".."9"}),
    to2 = List.Transform(from,(x)=>Text.Combine(List.RemoveLastN(Splitter.SplitTextByCharacterTransition((x)=>not List.Contains(lst,x),lst)(x),1))),
    
    txt = "0123456789",
    to3 = List.Transform(from,(x)=>Text.Combine(List.RemoveLastN(Splitter.SplitTextByCharacterTransition((x)=>not Text.Contains(txt,x),(x)=>Text.Contains(txt,x))(x),1)))
in
to3

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

to1 – поступаем умнее – делим по переходу с не цифр на цифры – здесь заменили один список функцией, такое разделение будет идти шустрее

to2 – вспомнили, что «..» - это оператор перечисления, и чтобы он не вычислялся большое количество раз, один раз получили список цифр и запихнули его в буфер – ещё бонус к скорости
и раньше я бы на этом закончил…

НО!
to3 – а кто, собственно, сказал, что проверять нужно именно список… Возьмём текст из 10 цифр и осуществим проверку на нём…
… а что из этого получилось смотрите в обзоре на Ютубе

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

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

List.TransformMany или как ускорить запрос
#АнатомияФункций - List.TransformMany

Всем привет!
Решил записать разбор по поводу сегодняшнего обсуждения в чате - пилим полное декартово через List.TransformMany и разбираемся, почему исходный запрос "тупит" - вышло немножко долго, но на мой взгляд небезынтересно.
Итоговая версия кода:

let
    from = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    f=(x)=>[a=Text.SplitAny(x{0},"()"),
            aa=Text.Split(a{1},", "),
            b=Text.SplitAny(x{1},"()"),
            c=a{0},
            d=List.Last(b),            
            f=(x)=>Text.Split(b{1},"/"),
            g=(x,y)=>[a=c&x&" "&y,b={a,a&" "&d}][b],
            e=List.TransformMany(aa,f,g)][e],
    to = Table.FromList(List.Combine(Table.ToList(from,f)),(x)=>x,{"Номенклатура","ИД"})
in
to

А всяческие телодвижения, сравнения и комментарии – смотрим в 17:00 на Ютубе

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

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

Splitter 6 - Splitter.SplitTextByRepeatedLengths, Splitter.SplitTextByLengths или когда длина имеет значение…
#АнатомияФункций - Splitter.SplitTextByRepeatedLengths, Splitter.SplitTextByLengths

Всем привет!

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

Splitter.SplitTextByRepeatedLengths(length as number, optional startAtEnd as nullable logical)
Splitter.SplitTextByLengths(lengths as list, optional startAtEnd as nullable logical)

Оба сплиттера вообще не ориентируются на подстроки и конкретные символы – их интересует только необходимая длина подстроки.

Смотрим примеры:

-приводим в божеский вид МАС-адреса (разбираем строку по 2 символа)

let
    from = #table({"MAC"},{{"001A2B3C4D5E"},{"0815752B9ADC"},{"096C2ABD7534"}}),
    f=(x)=>Combiner.CombineTextByDelimiter("-")(Splitter.SplitTextByRepeatedLengths(2)(x)),
    to = Table.TransformColumns(from,{"MAC",f})
in
to

- делаем «красивое» текстовое представление чисел (обращаем внимание на второй аргумент – разбираем по тройкам с конца)

let
    from = #table({"num"},{{1},{12},{123},{1234},{12345},{1234567},{12345678}}),
    f=(x)=>Text.Combine(Splitter.SplitTextByRepeatedLengths(3,true)(Text.From(x))," "),
    /*хотя можно и Number.ToText(x,"N0")*/
    to = Table.TransformColumns(from,{"num",f})
in
    to

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

let
    from = #table({"tel"},{{"+79871234567"},{"89876543210"}}),
    f=(x)=>[a=Splitter.SplitTextByLengths({2,2,3,3},true)(x),
            b=Text.Format("+7 (#{0}) #{1}-#{2}-#{3}",a)][b],
    to = Table.TransformColumns(from,{"tel",f})
in
to

Как-то так – функции нечасто используемые, но в отдельных случаях прям мастхев)

… демонстрация работы с комментариями, как всегда, на Ютубе

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

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

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