Попытка намбер ван.
#ВсякиеКоннекторы
Всем добра.
Потребовалось по работе лазить в API к одному из интернет-магазинов, и с радостью обнаружил что в #shared ничего вообще нет на тему кодирования хэша для формирования сигнатуры запроса к ресурсу. А вот у ресурса сигнатура имеется, и надо что-то делать. После некоторых изысканий выяснил что кое-что в PQ на эту тему все таки есть, но не в стандарте, а в наборе функций для создания собственных коннекторов. Засучив рукава, поставил VS с нужными надстройками и собрал вот такое чудо. Если кому надо, пользуйтесь, как собрать такой коннектор в инете и на сайте МС инструкций навалом.
Коннектор протестирован на боевом API все отлично выгружается. При установке шлюза в стандартном режиме коннектор отлично работает и после публикации отчета в облаке.
Если тема интересна, то обращайтесь в личку, объясню что там да как.

Чем заменить Table.AddColumn? – эпичный челлендж
#АнатомияФункций – Table.FromList и многие другие
Всем привет!
Мы уже разбирали, что на списках быстрее ))). Так вот, история получила продолжение. Сегодня в чате состоялся челлендж. @MaximZelensky – спасибо за идею и организацию!
Суть задачи – повторить в точности результат выполнения функции Table.AddColumn. В точности означает, что нужно не просто получить таблицу с новым столбцом, это должна быть функция, принимающая те же аргументы, что и штатная, при этом типы столбцов у возвращаемой таблицы в любой ситуации должны получиться такими же, как и при работе штатной функции. По ссылке выше можете посмотреть результаты – все (все, Карл!) участники оказались быстрее, чем штатный вариант почти в два раза! (важное дополнение - в ходе пристальных тестов выяснено, что стандартная функция работает шустрее, если её дополнительно обернуть в Table.Buffer, кастомная таких изысков не требует, что приятно).
Ну а лучшим по скорости и точности оказался, разумеется, гибрид идей – его и разберем:

(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table =>
[
    //кусок от buchlotnik
    rec=Table.ToRecords(table),
    gen=(x)=>Record.FieldValues(x)&{columnGenerator(x)},
    newtyp = if columnType = null then Type.FunctionReturn(Value.Type(columnGenerator)) else columnType,
    //кусок от Maxim Zelensky
    TypeAsRecord = Record.AddField(Type.RecordFields(Type.TableRow(Value.Type(table))), newColumnName, [Type = newtyp, Optional = false]),
    NewTableType = type table Type.ForRecord(TypeAsRecord, false),
    //и итог послечелленджевого обсуждения
    to = Table.FromList(rec,gen,NewTableType)    
][to]

()=> обратите внимание на аргументы – они просто скопированы из справки - мы играем по правилам 🙂
rec – превратили таблицу в список записей - записи важно сохранить, потому что , columnGenerator штатной функции работает с полями записи
gen – функция генерации строки таблицы – получает на вход запись, превращает её в список значений и добавляет к нему результат выполнения columnGenerator над этой записью
newtyp – проверяем, передан ли четвертый аргумент, если да – используем его, если нет – определяем тип данных, возвращаемых columnGenerator

Шаги от Максима:
TypeAsRecord – получаем типы столбцов исходной таблицы в виде записи и добавляем к ней информацию о новом столбце и его типе
NewTableType – создаем новый тип таблицы с информацией уже обо всех столбцах
(Максим, ещё раз спасибо, чего я там нагородил через Table.Schema и #shared – прям стыдно 😳)

Ну и осталось немного:
to – генерация новой таблицы из списка записей с использованием функции Table.FromList: gen - второй аргумент должен возвращать список - наша функция этому условию полностью удовлетворяет, третьим аргументом сразу передаем полученной таблице информацию о типе
(кто забыл как такое читать - [ ][to] - вспоминаем)

Всё! Задачка решена. Просто, элегантно, и чёрт побери, шустро!
Курите решение, читайте спецификацию, надеюсь на следующем челлендже участников будет больше 😉

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

Expression.Evaluate или как импортировать код из текстового файла или с сайта?
#АнатомияФункций – Expression.Evaluate

Всем привет!
Очередной раз воспользовался одним приёмом, ну и решил о нём написать.
Начнём издалека – с простой задачки. Вот загрузили вы файл, а там в ячейках любезно расписаны суммы "123+234+345", "1234+234" и т.д. И очень хочется превратить это в числа. Одно из решений такое:

Expression.Evaluate("123+234+345") //702

Т.е. функция Expression.Evaluate принимает текстовый аргумент как выражение и вычисляет его.
Выражение может быть более сложным:

Expression.Evaluate("123+234+345=702")//true

Т.е. мы понимаем, что в выражении могут быть не только арифметические операторы.

И это используется, например, когда надо текст вида "1-5,8,10-12" превратить в список последовательных элементов:

Expression.Evaluate("{"&Text.Replace("1-5,8,10-12","-","..")&"}")//{1,2,3,4,5,8,19,11,12}

В этой ситуации после подстановки выражение будет выглядеть как "{1..5,8,10..12}" и после вычисления даст нам искомый список.

Достаточно удобно. И можно попытаться даже написать функцию для создания списка чисел от 1 до x:

f = (x)=>Expression.Evaluate("{1..x}")

Но в таком виде она не сработает – "имя x отсутствует в текущем контексте". Причина – x в тексте выражения – это лишь буква, а нам надо в явном виде заявить, что эта буква обозначает именно передаваемый аргумент:

f = (x)=>Expression.Evaluate("{1..x}",[x=x])

Здесь x до знака равенства – это имя в выражении, а после знака равенства – тот самый аргумент, с которым мы вызываем функцию. Как только вы осознаете этот факт, придёт понимание, что собственно в тексте могут быть и функции:

f = (x)=>Expression.Evaluate("List.Sum({1..x})",[x=x,List.Sum=List.Sum])

Так работает с точно такой же логикой - List.Sum до знака равенства – это просто имя из выражения, а после знака равенства – собственно функция, которую мы присваиваем данному имени.

И вот тут мы и подходим к основной задаче поста – если у нас есть текстовый файл, содержащий текст запроса – можем ли мы его импортировать не как текст, а именно как запрос? И ответ – да, можем. Другое дело, что вот так, как выше, перечислять все функции из текста запроса утомительно и крайне не универсально. Что же делать? Да вот что:

f = (x)=>Expression.Evaluate("List.Sum({1..x})",#shared&[x=x])

Здесь внутренняя переменная #shared возвращает содержимое глобальной среды – т.е. все функции, типы, константы и т.д., которые только знает M, в виде записи (record). Мы только добавили к ней ещё и x - аргумент нашей функции.
Таким образом, использование #shared позволяет превратить текст запроса непосредственно в код, который можно выполнить.

И теперь приём, о котором я сказал в самом начале – на GitHub у меня лежит парочка функций, и ту же UnZip я использую довольно часто. Конечно, можно просто скопировать. Но что если завтра возникнет мысль и я её перепишу на более быструю – очень не хочется переделывать все файлы, куда я её уже запихнул. Поэтому несколько более сложный вариант:

Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/UnZip.pq")),#shared)

Web.Contents – получает информацию с сайта (raw.githubusercontent.com – очень удобная штука – сразу отдает непосредственно текст функции – там не требуется выцарапывать её из разметки страницы). Далее Text.FromBinary переводит полученную информацию в текст, а Expression.Evaluate + #shared превращают текст в код. Это гарантирует, что файл, куда я поместил подобный запрос, работает с актуальной версией функции.

Понятно, что совершенно не обязательно держать код на GitHub – это может быть просто текстовый файл, который вы держите на диске или в облаке. Это не обязательно должно быть функцией – возможно вы регулярно используете запрос для курсов валют или к производственному календарю. Такие запросы тоже приходится периодически корректировать – и лучше делать это один раз, нежели во всех своих файлах, ну а как – мы только что разобрали.

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

ДОбыча иерархии – ковыряемся в xml через M
#АнатомияФункций – Xml.Document

Всем привет! Несколько раз всплывал вопрос как вытащить из файла не только данные, но и уровни иерархии. Иерархия при этом может быть в виде структуры, а может быть и в виде отступов, которые непонятно как ловить (привет 1С). На самом деле нам в обоих случаях надо добраться до xml-разметки листа и получить оттуда нужную информацию. В первом комменте под постом будет пример файла (однолистовая выгрузка) с обоими вариантами, попробуем его спарсить:

let
    unzip=Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/UnZip.pq")),#shared),
    bin = Binary.Buffer(File.Contents("C:\Users\User\Downloads\Структура Item_.xlsx")),
    sh=Excel.Workbook(bin,false){0}[Data],
    xml=Xml.Document(unzip(bin){[FileName="xl/worksheets/sheet1.xml"]}[Content]){0}[Value]{[Name="sheetData"]}[Value],
    f=(x)=>x{[Name="outlineLevel"]}?[Value]?,
    g=(x)=> x{0}[Attributes]{[Name="s"]}?[Value]?,
    tr=Table.TransformColumns(xml,{{"Attributes",f},{"Value",g}}),
    lst=List.Sort(List.Distinct(tr[Value]),(x)=>Number.From(x)),
    dict=Record.FromList(List.Positions(lst),lst),
    tr1=Table.TransformColumns(tr,{"Value",(x)=>Record.Field(dict,x)}),
    lst1=Table.ToList(tr1,(x)=>List.LastN(x,2)),
    lst2=Table.ToList(sh,(x)=>x),
    to=Table.FromList(List.Zip({lst2,lst1}),List.Combine,Table.ColumnNames(sh)&{"величина отступа","уровень иерархии"})
in
    to

По шагам:
unzip – тащим функцию-разархиватор с моего гитхаба (принцип описан здесь, а если хотите держать функцию у себя – заберите её тут)
bin – подключаемся к бинарному содержимому файла и помещаем его в буфер. Суть в том, что нам нужно обратиться к нему дважды – отдельно за данными, отдельно – за иерархией
sh – получили данные с листа традиционным способом
xml – получили данные о том же листе, но через разметку., конкретно sheetdata – обратите внимание, что это таблица, содержащая информацию о строках – содержимое и атрибуты.

Теперь напишем две функции :
f – получает информацию об outlineLevel – это и есть уровень строки в иерархии, если она определена структурой
g – а здесь посложнее, дело в том, что 1С-ые отступы являются атрибутом не строки, а отдельной ячейки. В данном случае мы обращаемся к свойствам первого столбца конкретной строки и забираем оттуда s – величину отступа (если нужен не первый столбец заменяем {0} на другую нужную позицию)
tr – ну и применяем, функцию f к атрибутам строки, а функцию g – к значениям строки – на выходе имеем таблицу с соответствующей построчной информацией.

Следующие три шага не являются обязательными, но позволяют превратить величину отступа в уровень иерархии
lst – получили список уникальных значений и отсортировали по возрастанию
dict – собрали из него словарь
tr1 – заменили отступы на уровни через словарь

lst1, lst2, to - дело осталось за малым – данные и информацию об иерархии надо объединить - в данном случае сделано на списках через построчный zip

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

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

ДОбыча иерархии 2 – боремся с пустыми строками
#АнатомияФункций – Xml.Document

Всем привет! В предыдущем посте мы рассмотрели подход к чтению интересующей нас информации из xml-разметки файла. При этом возможно 2 проблемы:
1 – у любого листа есть его dimension (в xml этот параметр называется именно так) – область, в которой, по мнению Excel, находятся данные (в первом каменте пример – данные начинаются с D5, хотя здоровому человеку очевидно, что с D6) – при обращении к листу pq загружает именно эту область
2 – в данных возможны пустые строки, соответственно информация о них в разметке не хранится

Что ж, на примере вытаскивания структуры решим и эти проблемы:

let
    unzip=Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/UnZip.pq")),#shared),
    bin = Binary.Buffer(File.Contents("C:\Users\muzyk\Desktop\Структура Item_trouble.xlsx")),
    xml=Xml.Document(unzip(bin){[FileName="xl/worksheets/sheet1.xml"]}[Content]){0}[Value]{[Name="sheetData"]}[Value][Attributes],
    val=List.Transform(xml,(x)=>x{[Name="outlineLevel"]}?[Value]?),
    nms=List.Transform(xml,(x)=>x{[Name="r"]}?[Value]?),
    dict=Record.FromList(val,nms),
    sh = Excel.Workbook(bin,false){0}[Data],
    add = Table.AddIndexColumn(sh, "Уровень", Number.From(nms{0}),1),
    to=Table.TransformColumns(add,{"Уровень",(x)=>Record.FieldOrDefault(dict,Text.From(x))})
in
    to

По шагам:
unzip, bin – всё как в прошлый раз
xml – поскольку вынимаем только структуру, сразу загружаем таблицу Attributes для каждой строки
val – получаем для каждой строки значение её outlineLevel
nms – получаем для каждой строки её номер (посмотрите на этот шаг при парсинге файла-примера – можете убедиться, что нумерация не сквозная)
dict – соберем из номеров и уровней словарь на записях
sh – как и в прошлый раз получаем данные первого листа
add – а вот пересборку делаем по-другому – а именно – добавляем столбец индекса, но не с 1, а с первой строки в dimension
to – подцепляем данные из словаря через Record.FieldOrDefault, поскольку не обо всех строках у нас имеется информация

Собственно, всё. Принцип не поменялся – получаем данные и информацию из разметки, просто пришлось немного по-другому собрать их воедино.

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

Expression.Evaluate или построчное вычисление выражений из ячейки
#АнатомияФункций - Expression.Evaluate

Всем привет!
В своё время мы уже разбирали функцию Expression.Evaluate и выяснили, что можно вычислять выражения вида "123+234+345", что можно написать функцию (x)=>Expression.Evaluate("…x…",[x=x]), и вообще превратили кусок текста с гитхаба в код с помощью #shared.

Но тут в чате подкинули задачку немножко иного плана – а именно, вычислить результат выражения, записанного в ячейке, но вместо числовых значений – названия соответствующих полей.
ОК, давайте смотреть:

let
    from = #table({"дата","текст","выражение","поле1","поле2"},
    {{#date(2023,1,1),"Вася","-поле1/поле2",11,22},
    {#date(2023,2,2),"Коля","поле1-поле2",0,33},
    {#date(2023,3,3),"Петя","поле1*поле2-поле1",33,44}}),
    f=(rec)=>Expression.Evaluate(rec[выражение],rec),
    to=Table.AddColumn(from,"результат",f)
in
    to

И как бы весь код ))). Суть в том, что при добавлении столбца в функцию f передаётся текущая строка таблицы в виде записи (rec) и мы просим вычислить выражение из соответствующего поля (rec[выражение]), а встречающиеся имена заменить на значения из текущей записи.

Но я бы не писал пост, если бы всё было так просто, в реальном примере у нас были null:

let
    from = #table({"дата","текст","выражение","поле1","поле2"},
    {{#date(2023,1,1),"Вася","-поле1/поле2",11,22},
    {#date(2023,2,2),"Коля","поле1-поле2",null,33},
    {#date(2023,3,3),"Петя","поле1*поле2-поле1",33,44}}),
    
    nms=List.Buffer(Table.ColumnNames(from)),
    f=(rec)=>[  a=Record.ToList(rec),
                b=List.Transform(a,(x)=>if x=null then 0 else x),
                c=Record.FromList(b,nms),
                d=Expression.Evaluate(rec[выражение],c)][d],
    to=Table.AddColumn(from,"результат",f)
in
    to

в этой ситуации код выше не отработал бы как надо, поскольку любые арифметические операции со значением null дают null. Поэтому пришлось немножко усложнить код:
nms – названия всех столбцов таблицы – нам это потребуется для создания записей на каждой строке – поэтому не забыли List.Buffer
f – функция от записи, которая делает следующее:
a – получили список значений всех полей,
b – заменили значения null на 0
c – заново собрали запись, но уже с нулями
d – отдали её в expression.Evaluate
to – применяем вновь полученную функцию к нашей таблице и получаем корректный результат.

Как-то так. Второй аргумент Expression.Evaluate сильно развязывает руки, надо просто передавать в него корректную запись.

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

buchOfficePack - анонс, теперь не отверчусь
#АнатомияФункций - buchOfficePack

Всем привет!
Небольшая задачка, которую я пилил вчера, доступна в рамках следующего запроса:

Expression.Evaluate(Text.FromBinary(Binary.Buffer(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/buchOfficePack"))),#shared)

Ну а что это, зачем и почему - рассказываю на Ютубе
Если тема интересная, прошу наследить в каментах под роликом

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

Получаем атрибуты документов Word из папки
#АнатомияФункций - Folder.Files, fxUnzip

Всем привет!
Подкинули недавно задачку – вытащить количество страниц из документов .docx в папке.
Что ж – это повод воспользоваться buchOfficePack.
Код для поиска тут:

let
    f=(x)=>[a=unzip(x){[Name="docProps/app.xml"]}[Value],
            b=Number.From(Xml.Tables(a){0}[Pages])][b],
    
    unzip = Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/buchOfficePack")),#shared)[fxUnzip],
    from=Folder.Files("путь к папке"),
    filtr=Table.SelectRows(from,(r)=>r[Extension]=".docx" and not Text.Contains(r[Name],"~")),
    tbl=Table.SelectColumns(filtr,{"Name","Content"}),
    tr=Table.TransformColumns(tbl,{"Content",f})
in
    tr

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

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