Рекурсия, 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