List.Accumulate – как написать «пустой» второй аргумент
#АнатомияФункций – List.Accumulate

Всем привет!
Давно зрело и вот нашёлся информационный повод.
Итак, возвращаемся к нашему List.Accumulate (помним, что медленный, но иногда удобный).
List.Accumulate(list as list, seed as any, accumulator as function) as any

Второй аргумент – seed - опорное значение, которое может быть любым, но сегодня мы его хотим сделать пустым (или нулевым, но не null чтобы всё работало).
Допустим нужно просуммировать нечётные числа в списке:
List.Accumulate({1,2,3,4,5},0,(s,c)=>if Number.IsOdd(c) then s+c else s)
Тут у нас простая арифметика, поэтому seed - 0 - просто ноль, с которым всё суммируем

Собираем через пробел значения из списка, чья длина больше или равна трем символам
List.Accumulate({"лучшие","запросы","оптом","и","в","розницу"},"",(s,c)=>if Text.Length(c)>2 then Text.Combine({s,c}," ") else s)
На выход подаём строку, поэтому seed – ""- пустая строка, с которой всё конкатенируем

Аналогичная логика со списками – опять выбираем нечётные числа, но теперь списком:
List.Accumulate({1,2,3,4,5},{},(s,c)=>if Number.IsOdd(c) then s&{c} else s)
Здесь опорное значение должно быть списком, к которому мы всё добавляем, поэтому seed – {} – пустой список (также обращаем внимание на синтаксис добавления нового элемента к существующему списку – скобочки там не просто так)

А вот ситуация c записями (с ней сталкиваемся, например, при парсинге, когда значения нескольких полей идут как одно строковое):
List.Accumulate({{"длина",100},{"ширина",50},{"высота",10}},[],(s,c)=>Record.AddField(s,c{0},c{1}))
На вход подается список списков (поле – значение), но на выходе хотим запись – значит seed – [ ] – пустая запись. Также обращаю внимание, что добавление осуществляем не через конкатенацию, а через функцию (проблема аналогично той, которую описывал ранее). Ну и помним, что при аккумулировании списка списков – c – это тоже список и можно обращаться к отдельным его элементам, как в примере.

Ну и всё это вроде и так понятно, осталось только разобраться с таблицами. Вчера в миру был пример. Нужно получить полное декартово произведение – как это делать через джоин уже описывал Пух. Вопрос теперь в другом – а как сджойнить несколько таблиц? Смотрим код:
let
lst={"Город","Год","Продукт"},
f=(x)=> Excel.CurrentWorkbook(){[Name=x]}[Content],
g=(x,y)=>Table.Join(x,{},f(y),{}),
to = List.Accumulate(lst,#table({},{{}}),g)
in
to
По шагам:
lst - список таблиц (можно его и запросом получить, я в курсе, но было бы не так наглядно)
f – вспомогательная функция – подключение к конкретной таблице (оптимизаторам молчать! можно вообще без имён, одним подключением, отфильтровав только таблицу самого запроса... ДА МОЖНО и закрыли тему)
g – функция для аккумулятора (мы же помним, что функции стоит выносить) – тут к таблице x (кому удобнее – пишите s или state – это не принципиально), джойнится таблица f(y) – т.е. таблица из файла с именем y
to – ну и сам аккумулятор – смотрим на seed - #table({},{{}}) – примерно так выглядит пустая таблица – пустой список имён столбцов и пустой список списков значений строк – это не единственный вариант - мне просто нравится, когда много фигурных скобок 😉
Собственно всё – к пустой таблице последовательно джойнятся все необходимые - задачка решена.
Так что аккумулируя что-то с нуля определитесь с типом данных на выходе и выбирайте соответствующий seed - тогда всё получится.

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

LinearTrend – функция, которой нет, но очень хочется
#АнатомияФункций – статистические функции
Всем привет!
Очередной раз в чате был задан вопрос про статистические функции в M.
Они есть, все в категории List: .Average, .Count, .Covariance, .Median, .Min, .Max, .Mode, .Modes и .StandardDeviation. Набор стандартный, вполне достойный, но уж больно часто спрашивают про оценку трендов – а вот её не подвезли. Я не собираюсь никого мучать линейной алгеброй и решением задачи в общем виде в матричной форме, поэтому просто приведу вариант решения для парного линейного случая:

LinearTrend = (x as list, y as list) as record =>//предполагается y = a + b*x
[   
    x2 = List.Transform(x,(y)=>y*y),
    y2 = List.Transform(y,(x)=>x*x),
    xy = List.Transform(List.Zip({x,y}),(x)=>x{0}*x{1}),
        
    n = List.Count(x),
    sumx = List.Sum(x),
    sumy = List.Sum(y),
    sumx2 = List.Sum(x2),
    sumy2 = List.Sum(y2),
    sumxy = List.Sum(xy),
    
    a = if n>2 then (sumx2*sumy-sumx*sumxy)/(n*sumx2- sumx*sumx) else y{0},//ОТРЕЗОК()
    b = if n >1 then (n*sumxy-sumx*sumy)/(n*sumx2- sumx*sumx) else 0,//НАКЛОН()
    syx = if n>2 then (sumy2-a*sumy-b*sumxy) else 0,
    sy = sumy2-sumy*sumy/n,
    s = if n >2 then Number.Sqrt(syx/(n-2)) else 0,//СТОШYX()
    R2 = 1-syx/sy,//R^2 - функции нет, на графиках есть 
    r = Number.Sign(b)*Number.Sqrt(R2)//КОРРЕЛ()
][[n],[a],[b],[s],[R2],[r]]

По шагам:
x,y – абсцисса и ордината соответственно – списками
x2,y2,xy – для решения задачи нам требуются квадраты по обеим координатам и попарные произведения
n – число элементов (взято по абсциссе, в предположении, что списки одинаковой размерности)
sumx, sumy, sumx2, sumy2, sumxy – суммы – величин, их квадратов, попарных произведений
a,b – пересечение и наклон соответственно (в комментах к коду названия соответствующих функций в Excel)
syx – остаточная сумма квадратов для модели
sy – сумма квадратов отклонений ординаты от среднего
s – остаточное стандартное отклонение
R2 – коэффициент детерминации (он фигурирует на графиках, его же вычисляет ЛИНЕЙН())
r – коэффициент корреляции – посчитан из R2, просто чтоб было, если нужен только он – не надо пользоваться этой функцией – посчитайте ковариацию – оно проще и быстрее, поверьте

Ну и всё – параметры получаем в виде записи. Вроде громоздко, но ничего сложного.
Если тема актуальна – пишите в каментах, будем двигать статистику в М.

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

Table.AddColumn и типизация столбцов – Часть 1
#ГостевойТанк - дополнение от @MaximZelensky

По мотивам и в дополнение к челленджу по переписыванию Table.AddColumn

При загрузке данных из PQ в модель данных Power Pivot в Excel или в модель данных Power BI критически важно, чтобы столбцы таблиц имели правильный тип (иначе все нетипизированные столбцы будут восприняты как текстовые, вне зависимости от их содержимого).

Ок, предположим, мы решили создать в таблице столбец с именем "new" и заполнить его единичками. Когда мы создаем новый столбец через интерфейс PQ, автоматически создается код такого вида: Table.AddColumn(TableName, "new", each 1). Новый столбец, созданный таким образом, будет иметь по умолчанию тип данных any - то есть будет нетипизирован. Так происходит потому, что функция each 1 не имеет заранее определенного типа возвращаемого значения, а априори анализировать результат расчёта для каждой строки - дорого и неэффективно.
Но что делать, если нам важен тип данных (мы хотим загрузить этот столбец в модель)? Можно ли сразу указать, какого типа этот новый столбец? Мы же знаем его заранее (1 это явно число, других вариантов результата у нашей функции нет). Для этого нас есть несколько способов.

Способ 0
После создания шага применяем преобразование типа столбца через интерфейс или в коде при помощи Table.TransformColumnTypes - наиболее прямой, но расточительный способ. В этом случае, кроме собственно указания типа для столбца, происходит принудительное преобразование значений в нем к указанному нами типу, и если по какой-то причине преобразовать значение в указанный тип нельзя, появится ошибка ячейки. Замедление производительности мы увидим уже на средних объемах. Однако есть случаи (за рамками этого поста), когда такая операция необходима и чрезвычайно полезна.

Способ 1
Мы можем явно указать тип данных, который возвращает функция-генератор. Например перепишем функцию с единичками вот так (не через диалоговое окно, а в строке формул):
(row) as number => 1
Новый столбец автоматически получит тип number - потому что так сказано в определении функции ( as number). И даже более того - если мы попробуем обхитрить PQ и вместо единичек вернуть текстовое значение, такая попытка вызовет ошибку ячейки:
(row) as number => "1" // Expression.Error: Не удается преобразовать значение "1" в тип Number
Так что, если мы точно знаем, что результат может быть определенного типа, мы можем смело использовать такой подход, и даже отлавливать ошибки типизации.
К сожалению, первый способ имеет ограничения – какие и как с ними бороться будет рассказано во второй части.

@MaximZelensky

Table.AddColumn и типизация столбцов – Часть 2
#ГостевойТанк - дополнение от @MaximZelensky

Продолжение - первая часть

Способ 2
К сожалению, первый способ имеет ограничения - мы можем использовать оператор назначения типа as только с примитивными типами - такими как text, number, date и т.п., либо их nullable версиями: nullable text, nullable number и т.п.
Однако, если мы хотим указать, что единичка - не просто число, а целое число, мы не можем написать в определении функции as Int64.Type - такая попытка вызовет ошибку шага Expression.SyntaxError: Недопустимый идентификатор типа. Проблема в том, что Int64.Type - это не примитивный (базовый) тип, а так называемый фасет (подтип), с которым не хочет работать оператор as.
В таком случае мы можем задействовать 4-й (опциональный) аргумент функции Table.AddColumn - указание типа нового столбца:
Table.AddColumn(TableName, "new", each 1, Int64.Type)
В этом случае вне зависимости от того, указали ли мы, какой тип данных возвращает фукнция-генератор, новый столбец получит тот тип, который указан в 4-м аргументе - целое значение.
При этом очень важно отметить:
* Проверка соответствия указанного типа реальному типу значений не будет производиться на этапе создания столбца (ни на уровне ячейки, ни на уровне шага). Мы можем написать Table.AddColumn(TableName, "new", each 1, type text), и новый столбец будет иметь тип "Текст", но преобразование 1 в "1" не будет произведено.
* При загрузке данных в модель (как минимум в Power BI), тем не менее, будет произведена проверка соответствия типа данных столбца типу данных значений в его ячейках, и мы очень легко можем увидеть сообщение об огромном количестве ошибок при загрузке.
Поэтому использовать 4-й аргумент нужно очень аккуратно - только будучи уверенным, что тип значений в новом столбце не будет противоречить указанному.

У второго способа есть также одно важное преимущество - мы можем задавать там не только примитивные или фасетные типы, но и сложные составные типы.
Например, у нас есть столбец со списком годов и мы хотим для каждого года создать списки "первых чисел месяцев", чтобы затем развернуть эти списки в новые строки:
YearsTable = #table(type table [Year=Int64.Type], List.Zip({{2019, 2020, 2021}})),
AddMonths = Table.AddColumn(YearsTable, "MonthStart", each List.Transform({1..12}, (m) as date =>#date([Year], m, 1)))
ExpandMonths = Table.ExpandListColumn(AddMonths, "MonthStart")
Несмотря на то, что мы попробовали прописать as date во внутренней функции, это нам не особо помогло - после разворачивания списков новый столбец имеет тип any - такой тип у основной функции-генератора each. Если мы попробовали бы вместо each использовать (row)=> (по первому способу выше), то максимум, который нам был бы позволен - (row) as list =>. В этом случае тип нового столбца (до разворачивания) получил бы тип "список" (это будет заметно по иконке в названии столбца), но на этом и всё - после разворота, несмотря на то, что мы пытались задать тип date, новый (развернутый) столбец будет иметь тип any.

Здесь на выручку приходит тот самый Способ 2 - использование 4-го аргумента:
AddMonths = Table.AddColumn(YearsTable, "MonthStart", each List.Transform({1..12}, (m) as date =>#date([Year], m, 1)), type {date})
Использовав сложный составной тип type {date}, мы явно указали программе, что новый столбец содержит список дат. Теперь даже после разворачивания мы увидим, что столбец типизирован как "даты".

@MaximZelensky

Table.Max – или многоликий второй аргумент
#АнатомияФункций – Table.Max

Всем привет!
Сегодня в чате всплыл вопрос про Table.Max – и в личке спросили зачем я ее так странно пишу. Ответ прост – многие вещи я по привычке пишу через функции. Но вот как МОЖНО писать, думаю стоит прокомментировать развернуто.
Тестовый стенд:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("fdJJisMwEIXhq4haG+MqyXLiqzS9yNTzvA25e0RZtuu5kSCLYPh4Tv56uNKBRm7oSOO+oZN+P9NI0om4ThwzNXRJDzpu0yc9ZnegW2PcTp1snGTHfnFHdMO0B4Ps2M+D0nKGJ4RRYbDOOw6zC4s7o+vV9RvXZydxedELuqAubt4zZue7Nk2qe0Ln1Q2FvTTGMrlndKJuV9gzIV7Qsbp9Yc+EeJ2dlMLbvbXDG7opvC/9vrXDO7ocfigMmhAfCGN10IT4RNf/Pxj8Q+cQX+hC9WBMiG90vnowJsQPOqkfzBriFx3XD2YN8Ue3xzs="),Compression.Deflate))),
f = "a",
to = Table.Max(from,f)
in
to
from – исходник
f – условие определения максимума
to – результат Table.Max (обращаю внимание – на выход поступает запись – строка таблицы с максимумом по нашему условию)
Далее для простоты буду менять только шаг f .
Итак, для начала обращаю внимание в коде выше, что в отдельные шаги можно выносить не только функции, но и списки или отдельные значения, да вообще всё, что угодно (вынесение шагов обсуждалось тут).
f = "a" – просто название столбца, на выходе получим запись с максимальным значением по данному полю, причём – последнюю запись (в примере таких несколько).
Днём я написал по-другому:
f = (x)=>x[a]
т.е. написал функцию – обращаю внимание – на вход поступает строка таблицы – запись, и мы просто просим взять конкретное поле – т.е. результат будет таким же. Зачем такие сложности? Да посмотрите на столбец "e" – хотим максимальную дату, а она спрятана в текстовой строке, решение разумеется:
f = (x)=> Date.From(Text.BeforeDelimiter(x[e]," "))
т.е. мы уже не просто берем конкретное поле, но и осуществляем с ним дополнительное преобразование – вынимаем дату (можно конечно сначала соорудить допстолбец и потом брать максимум по нему, а можно вот так сразу)
Ну ок, а что если нас не устраивает вынимание последней строки? Хотим максимум по "a", если несколько значений – максимум по "b" и т.д. Да пожалуйста:
f = {"a","b","c"}
т.е. просто передаем список имен столбцов, в том порядке, в котором определяем максимум. Это важно -
f = {"a","c","b"}
даст уже другой результат (поиграйтесь с примером).

Ну и вернемся к функциям – а что если нужно вынуть дату из "e", а из полученных взять максимум по столбцу "c"? Да как бы тоже несложно:
f = {(x)=> Date.From(Text.BeforeDelimiter(x[e]," ")),"c"}
Т.е. список может состоять не только из названий столбцов, но и включать функции преобразования.

Как-то так – если в справке про аргумент написано as any – это стоит ковырнуть и возможно упростить себе программирование или жизнь )))

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

TableToBinaryText, TableFromBinaryText – или туда и обратно
#АнатомияФункций – Json, Binary и т.п.

Всем привет!
В серии постов про группировку появился пример исходника в виде текста странного вида: "i65Wiik1MDFJAZNGINI4VcnKUAcsbJwGFjaACIPZhqgKjbAotACTyWDSFCKuZGUMUQjVCVEINss4CaHJxAxMJilZmdTGAgA="
Вроде что это, как и откуда уже обсудили в чате, но раз вопросы в личку продолжаются закреплю здесь.
Постановка задачи
Необходимо дополнить пост в телеграме примером таблицы в несколько столбцов и хотя бы десятком строк. Писать такое в явном виде (например, через Table.FromRecords) долго, муторно и вообще может не влезть в сообщение, а прикладывать файл-пример также не очень – у нас здесь пользователи не только, да и не столько excel, сколько pbi, плюс скачивать файлы на работе не у всех есть возможность
Логика решения
Чтобы данные не занимали кучу символов их следует сжать. PQ умеет сжимать только бинарники, значит из таблицы нужно получить бинарник. Напрямую это действие осуществить нельзя, поэтому таблицу надо сначала преобразовать в текстовый формат. Из всего имеющегося в арсенале (мы помним, что таблицу можно рассматривать как список записей) лучше всего подходит Json - текстовый формат, который поддерживает записи. После сжатия на выходе будет бинарник, а нам надо его в воткнуть в сообщение – значит последним этапом мы должны будем получить строковое представление бинарника
Реализация
Функция превращения таблицы в сжатый текст:
TableToBinaryText = (tbl as table) as text=>[
json = Json.FromValue(tbl),
bin = Binary.From(json),
compr = Binary.Compress(bin,Compression.Deflate),
txt = Binary.ToText(compr)][txt]
json – преобразовали таблицу в json (размеченный текст)
bin – получили бинарное представление для текста
compr – осуществили сжатие (выбрано Compression.Deflate – просто потому, что так сжимаются ZIP-ы)
txt – получили строковое представление сжатого бинарника

Ну и процедура получения таблицы – это те же действия в обратном порядке:
TableFromBinaryText = (txt as text) as table =>[
bin = Binary.FromText(txt),
dec = Binary.Decompress(bin,Compression.Deflate),
json = Json.Document(dec),
tbl = Table.FromRecords(json)][tbl]
bin – строку обратно в бинарник
dec – бинарник вернули в несжатое состояние
json – получили из бинарника json – он отобразится как список записей
tbl – собрали из записей таблицу

Вот, собственно, и всё.
Какие практические задачи собрались решать вопрошающие не знаю. По мне так это просто пример, как можно решать свои частные задачи, используя имеющийся арсенал конкретного языка. С другой стороны, если начнете копать в сторону Binary.*, функции распаковки документов word или добывания информации о разметке в excel могут перестать быть китайской грамотой – там же всё строится на распаковке zip-архивов, коими и являются .docx и .xlsx. Так что да – частная задача может вести к изучению весьма интересных функций.

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

Всем привет!
Пух наконец-то смог уделить нам внимание и немножко навел порядок в своих сообщениях.
Поэтому теперь @PQfromtankbot имеет обновленную клавиатуру с расширенным набором категорий.

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

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

Источники мудроты.
#ПолезныеСсылки

Всем добра! Выпрыгнул из сумрака и привел к общему знаменателю старый пост (который удалил) чтобы источники лучше искались нашим ботом, а также добавил ссылочку на Бена нашего Грибаудо.
PQ:
01. Спецификация языка PowerQuery M - это фундамент, букварь, без него дальше тяжело;
01a. M language specification - оригинал этого фундамента на английском (@buchlotnik прям настаивает на чтении этой версии);
02. Справочник по языку формул Power Query M;
03. finalytics.pro простым языком для начинающих;
04. blog by Maxim Zelensky простым языком, но не для начинающих;
05. Товарищ Excel кратко, емко;
06. planetaexcel.ru обстоятельно и с примерами;
07. Книга "Скульптор данных в Excel с Power Query";
08. Кен Пульс и Мигель Эскобар. "Язык М для Power Query" русский перевод отличной книги;
09. Приручи данные с помощью Power Query в Excel и Power BI второе издание шикарной книги на русском от Кен Пульс и Мигель Эскобар.
10. BI блог Максима Уварова перевод статей Криса Вебба и разные коннекторы..;
11. The BIccountant грамотный бух рассказывает про BI;
12. Chris Webb's BI Blog Chris Webb's BI Blog Мега дядька рассказывает обо всем на свете. Просто кладезь!;
13. The Environment concept in M for Power Query and Power BI Desktop, Part 1 Концептуальный и обстоятельный разбор с погружением;
14. Промокоды от переводчика Александр Гинько на отличные книги;
15. Power Query Formatter Красота спасет мир... и мои глаза.

DAX:
01. Основные сведения о DAX в Power BI Desktop опять фундамент;
02. Articles - SQLBI Мега дядьки про все подряд;
03. Patterns – DAX Patterns Мега дядьки про расчеты стандартных показателей в аналитике. ABC и вот это вот все;
04. BI блог Антона Будуева. Разбор формул DAX по-русски;
05. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel;
06. Подробное руководство по DAX;
07. Шаблоны DAX;
08. DAX Formatter by SQLBI Красота спасет мир... и мозг тех, кто читает ваши формулы.

Как подключиться к файлам на Яндекс.Диске
#ВсякиеКоннекторы
И снова здрасьте. На хайпе по теме импортозамещения все чаще граждане перетаскивают свои данные на отечественные облака. И далее встаёт вопрос, а как их оттуда затащить в Power Query? Решением поделился добрый человек @IlyaNazarov.
Пока что сам я не тестировал. По результатам дополню пост собственными впечатлениями.

Table.FromList – пять аргументов счастья
#АнатомияФункций - Table.FromList

Всем привет!
Всё руки не доходили написать про одну из моих любимых функций.
Сначала читаем справку:

Table.FromList(list as list, optional splitter as nullable function, optional columns as any, optional default as any, optional extraValues as nullable number) as table

Обращаем внимание, что обязательным является только первый аргумент – list. Проверяем:

Table.FromList({"a,b,c,1","d,e,f,2","g,h,i,3"})

Работает, заодно узнаем, что по дефолту она ещё и текст по запятой делит, т.е. следующий код выдаст то же самое:

Table.FromList({"a,b,c,1","d,e,f,2","g,h,i,3"},Splitter.SplitTextByDelimiter(","))

Тогда зачем вообще писать splitter? Ну во-первых разделитель не всегда запятая:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3"},Splitter.SplitTextByDelimiter(";"))

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

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3"},(x)=>Text.Split(x,";"))

или например

Table.FromList({"abc1","def2","ghi3"},Text.ToList)

Ну ОК, а вот такая ситуация

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"))

Даст ошибку в третьей строке. Причина - таблица собирается на основе первой строки, а в третьей столбцов оказалось больше.
Решение - добавить третий аргумент (columns):

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),6)

Т.е. в третий аргумент поместили целевое количество столбцов (в общем виде это не обязательно константа – его можно и вычислить).

Также обращаем внимание, что отсутствующие значения были заменены на null, но если нужно что-то другое - привлекаем четвертый аргумент (default):

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),6,"-")

Обратная ситуация – нам нужны только первые три столбца:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),3,"-",ExtraValues.Ignore)

Т.е. пятый аргумент (extraValues) говорит, что делать с дополнительными значениями - в ситуации выше они проигнорированы, а вот в ситуации ниже – собираются в список:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),4,"-",ExtraValues.List)

Это бывает удобно.

Но что, если мы не хотим дефолтные имена столбцов - да пожалуйста:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),{"а","б","в","г","д","e"})

Т.е. можно передать список имен.

Более того, можно передать названия и типы:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>Text.Split(x,";"),type table [а=text,б=text,в=text,г=number,д=number,е=number])

Внимательные читатели обратят внимание, что типы столбцов поменялись, а вот сами значения остались текстовыми.
Об этом нужно помнить, но поскольку функция разделения реально может быть любой, можно чутка усложнить:

Table.FromList({"a;b;c;1","d;e;f;2","g;h;i;3;4;5"},(x)=>List.Transform(Text.Split(x,";"),(y)=>try Number.From(y) otherwise y),type table [а=text,б=text,в=text,г=number,д=number,е=number])

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

Всех благ!
@buchlotnik

List.Select и все все все
#АнатомияФункций – List.Select

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

List.Select(list as list, selection as function) as list

Всё как обычно – список (list) и функция выбора (selection). Пример даётся простой:

List.Select({1..100}, each _>90)

Сразу вспоминаем, что это можно переписать как функцию

List.Select({1..100}, (x)=>x>90)

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

Например, выводим числа, кратные пяти:

List.Select({1..100}, (x)=>Number.Mod(x,5)=0)

или выводим даты только рабочих дней (с понедельника по пятницу):

List.Select(List.Dates(#date(2022,1,1),365,#duration(1,0,0,0)),(x)=>Date.DayOfWeek(x,1)<5)

Также можно комбинировать условия.
Например, выводим числа, кратные трём ИЛИ пяти:

List.Select({1..100}, (x)=>Number.Mod(x,3)=0 or Number.Mod(x,5)=0)

А так кратные трём И кратные пяти :

List.Select({1..100}, (x)=>Number.Mod(x,3)=0 and Number.Mod(x,5)

(я знаком с математикой, можно было просто проверить делимость на пятнадцать, но это просто пример)

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

List.Select({{1,1},{1,2},{2,1},{2,2}},(x)=>x{0}>1)

В данном случае x – это список и мы проверяем его первый элемент x{0}

Аналогично можно обращаться к списку записей:

List.Select({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]},(x)=>x[a]>1)

В этом случае x – это запись, и мы проверяем конкретное её поле – x[a].

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

List.Select({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]},(x)=>x[a]>1 or x[b]<2)

Ничего не напоминает? Ровно так и работает Table.SelectRows:

Table.SelectRows(Table.FromRecords({[a=1,b=1],[a=1,b=2],[a=2,b=1],[a=2,b=2]}),(x)=>x[a]>1 or x[b]<2)

При выборе строк проверяется каждая из них, но строка – это запись, фактически это и есть работа со списком записей.

Да и в случае Table.SelectColumns без List.Select бывает не обойтись:
let
from = #table({"имя","a1","a2","итого a","b1","b2","b3","итого b"},{{"вася",1,2,3,4,5,6,15},{"петя",1,3,4,5,7,9,21},{"коля",2,4,6,8,10,12,30}}),
lst = List.Select(Table.ColumnNames(from),(x)=>x="имя" or Text.Contains(x,"итого")),
to = Table.SelectColumns(from,lst)
in
to
т.е. lst - из таблицы мы получаем полный список названий столбцов и выбираем «имя» или те, которые содержат «итого».
to - полученный список использовали в SelectColumns.

Как-то так – определяем анализируемую структуру, чтобы понимать, от какого аргумента следует писать функцию, прописываем условие (или комбинацию условий) и получаем нужный результат. List.Select редко используется сам по себе, а вот в связке с другими функциями - весьма полезная штука.

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

Text.Remove, Text.Select, Text.Trim на страже чистоты текста
#АнатомияФункций – всякое про текст
Всем привет!
Разберем задачку, частенько возникающую при парсинге, а именно – имеется текст, а нам надо почистить его от всякого лишнего.
Для этого в арсенале мы имеем Text.Remove, Text.Select, Text.Trim, Text.TrimStart, Text.TrimEnd и даже Text.Clean. Что ж, поехали!
Сначала простенькое:

Text.Remove("<мама <мыла <раму","<")

Здесь мы наблюдаем явно лишний символ <, передаем его вторым аргументом и радуемся жизни

Text.Remove("<мама> <мыла> <раму>",{"<",">"})

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

Text.Remove("<мама/> <мыла/> <раму/>",{"<",">","/"})
Text.Remove("<-ма9ма098/> 67-.908<мыABCла/> <ра123qwerму/>",{"<",">","/",".","-","0".."9","A".."Z","a".."z"})

тут вторым аргументом уже сущее безумие - отдельные символы, диапазоны символов - можно ли это как-то упростить?
Конструкция “a”..”z” даёт нам все символы с номерами от 97 (код английской строчной a) до 122 (код строчной z). Вооружившись этим знанием можем переписать код:

Text.Remove("<-ма9ма098/> 67-.908<мыABCла/> <ра123qwerму/>",List.Transform({33..127},Character.FromNumber))

Здесь мы во втором аргументе генерим все числа от 33 до 127 и превращаем их в символы (32 не трогаем – это пробел и он нам нужен). Аналогично можно бороться и с непечатаемыми символами (разрыв строки, перевод каретки, табуляция и т.п.):

Text.Remove("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",List.Transform({1..31,33..127},Character.FromNumber))

Вообще для непечатаемых символов (с 1 по 31) есть своя функция - Text.Clean, но раз уж у нас тут и так список всякого – мы их просто добавили.

Ок, но неоднократно говорилось, что чем длиннее анализируемый список, тем медленнее всё работает. Соответственно можно поменять стратегию – не пытаться удалить всё лишнее, а только оставить всё нужное:

Text.Select("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",{"А".."я"," "})

Вполне симпатично, но поборники чистоты кода попросят переписать:

Text.Select("<-ма9м#(lf)а098/> 67-.908<мыA#(cr)BCла/> <ра123qwerму/>",{"А".."я","Ё","ё"," "})

Здесь фишка в том, что Ё и ё в силу исторической несправедливости имеют свои коды, поэтому их указываем дополнительно, через запятую.

Но вы не думайте, что с латиницей проще:

Text.Select("<[mama\] [my\la\] [ra/-mu]/>",{"A".."z"," "})

Неожиданный результат, не так ли? Ну просто так вышло, что [, ], \ ещё несколько символов оказались между прописными и строчными буквами латиницы, поэтому корректно писать так:

Text.Select("<[mama\] [my\la\] [ra/-mu]/>",{"A".."Z","a".."z"," "})

Это всё прекрасно, конечно, но что, если нужно удалить не все символы, а только что-то до или после нужного текста? Тут нам не обойтись без Trim:

Text.TrimStart("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))
Text.TrimEnd("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))
Text.Trim("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber))

Идея точно такая же – передаем список символов, и функция их удаляет. Только
TrimStart - удаляет все символы из списка, идущие подряд с начала строки
TrimEnd - то же самое, но с конца строки
Trim - объединяет в себе работу обеих
Только будьте аккуратны при формировании списков:

Text.Trim("<name = 191.168.0.0 - Михаил (buchlotnik) Музыкин/>",List.Transform({1..127},Character.FromNumber)&{"М","и","н"})

на выходе получится "хаил (buchlotnik) Музык" - Ми в начале и ин в конце попали под раздачу, поскольку эти символы содержались в списке исключаемых. Это собственно, ответ на прошлогоднюю загадку )))
Как-то так – изучайте коды символов и чистите ваши выгрузки от всякого мусора с полным осознанием дела.

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

Производственный календарь.
#ПолезныеСсылки
Всем добра!
Со скуки запилил парсер производственного календаря за произвольный период (настраивается в параметрах) с сайта Консультант плюс. В телегу код не выкладываю - слишком громоздко, поэтому ссылочкой на форум. Всем желающим предлагаю для развития запилить функцию для выгрузки календаря за произвольный период. Выкладывайте в комментах и добавлю в основной пост. Если желающих не будет - сделаю сам, но позже.

Table.TransformColumns – Часть 1 Функция четырёх аргументов
#АнатомияФункций – Table.TransformColumns
Всем привет!
Нашёлся повод, поэтому разбираем Table.TransformColumns. Для начала заглянем в справку:

Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number)

table – наша таблица
transformOperations – список преобразований
defaultTransformation – функция преобразования, применяемая по умолчанию (когда не требуется ставим null)
missingField – аргумент, отвечающий за то, что делать с отсутствующими полями

Ну и поехали на примере - имеем таблицу, в которой некоторые числовые значения как текст, а некоторые null и мы хотим null поменять на 0, а текст превратить в целые числа:

let
    from = Table.FromRecords({
        [A = null, B = 2, C = 3, D = "4"],
        [A = "5", B = 6, C = null, D = 8],
        [A = 9, B = null, C = "10", D = 11]
    }),
    f=(x)=> if x=null then 0 else Number.From(x),
    to = Table.TransformColumns(from,{"A", f})
in 
    to

f – функция проверки на наличие null и преобразования текста в число (зачем выносить в отдельный шаг обсуждалось тут). Каждый столбец обрабатывается отдельно, поэтому пишем функцию от одного аргумента (само значение по столбцу) и ссылки на другие столбцы в этой ситуации недопустимы
to – применение функции к столбцу А – т.е. второй аргумент – это список из названия столбца и функции преобразования. При этом на выходе мы получили числовые значения, но тип столбца не изменился.
Дело в том, что преобразование значения и типизация столбца – это разные вещи:

to = Table.TransformColumns(from,{"A", f, Int64.Type})

Вот теперь и значения, и тип столбца какие нужно.

ОК, но что если нужно менять несколько столбцов:

to = Table.TransformColumns(from,{{"A", f, Int64.Type},{"B", f, Int64.Type},{"C", f, Int64.Type},{"D", f, Int64.Type}})

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

Теперь зададимся вопросом – а можно применить функцию сразу ко всем столбцам? Ну, собственно, для этого и существует третий аргумент:

to = Table.TransformColumns(from,{}, f)

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

Но ведь часто один или несколько столбцов нужно пропустить, а вот к остальным применить функцию:

to = Table.TransformColumns(from,{"A",(x)=>x}, f)

в данном случае столбец A остается без изменений (надеюсь не нужно объяснять, что (x)=>x не делает ничего, просто возвращает переданный ей аргумент), а остальные столбцы изменяются.
Недостаток подхода – можно применить функцию, но сразу обозначить ещё и тип столбца не получается (опять же – см. часть 2)

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

to = Table.TransformColumns(from,{{"A", f, Int64.Type},{"B", f, Int64.Type},{"C", f, Int64.Type},{"D", f, Int64.Type},{"E", f, Int64.Type}},null,MissingField.Ignore)

Видим, что в списке есть столбец E. При этом важно – хоть функцию по умолчанию мы в данном случае не используем, аргумент нельзя просто пропустить – поэтому мы ставим null. А последний аргумент - MissingField.Ignore, позволяет нам избежать ошибки отсутствующего столбца.
Другое дело, если у вас, например, стандартная форма и такой столбец нужен, в этом случае:

to = Table.TransformColumns(from,{{"A", f, Int64.Type},{"B", f, Int64.Type},{"C", f, Int64.Type},{"D", f, Int64.Type},{"E", f, Int64.Type}},null,MissingField.UseNull)

Теперь на выходе мы получили все столбцы, даже отсутствовавшие в исходной таблице.

Как-то так, дополнительные аргументы придуманы не просто так – они реально расширяют возможности.

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

Table.TransformColumns – Часть 2 Причём тут List.Transform
#АнатомияФункций – Table.TransformColumns , List.Transform

Всем привет, продолжаем разбор Table.TransformColumns. В первой части мы столкнулись с громоздкой конструкцией для обработки всех столбцов

let
    from = Table.FromRecords({
        [A = null, B = 2, C = 3, D = "4"],
        [A = "5", B = 6, C = null, D = 8],
        [A = 9, B = null, C = "10", D = 11]
    }),
    f=(x)=> if x=null then 0 else Number.From(x),
    to = Table.TransformColumns(from,{{"A", f, Int64.Type},{"B", f, Int64.Type},{"C", f, Int64.Type},{"D", f, Int64.Type}})
in 
    to

Решение через третий аргумент было неполным, поскольку не позволило присвоить типы столбцам. Как же быть? Просто вспомнить про Table.ColumnNames:

let
    from = Table.FromRecords({
        [A = null, B = 2, C = 3, D = "4"],
        [A = "5", B = 6, C = null, D = 8],
        [A = 9, B = null, C = "10", D = 11]
    }),
    f=(x)=> if x=null then 0 else Number.From(x),
    nms = Table.ColumnNames(from),
  lst = List.Transform(nms,(x)=>{x,f,Int64.Type}),
    to = Table.TransformColumns(from, lst)
in 
    to

Здесь
nms – получили список названий всех столбцов,
lst – преобразовали список названий в список списков, где каждый элемент – это {название, функция, тип}
to – успешно это применили

Если нужно не трогать первый столбец:
nms = List.Skip(Table.ColumnNames(from))
Ну и в целом можно выбирать нужные столбцы через List.Select и далее осуществлять преобразование пачкой.

Вроде несложно, а нервов экономит много, да и код выходит лаконичным и читаемым.

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

Сложно о функциях 1 - Типизация
#АнатомияФункций - Type.ForFunction

Всем привет!
Недавно разбирали Table.TransformColumns и я там немного слукавил, сказав, что только с помощью функции нельзя передать тип столбца. На самом деле при написании функции мы можем указать тип возвращаемого значения, а также тип самого аргумента:

f=(x)=>
f=(x as any)=>
f=(x as any) as number =>

Явно указывать тип очень правильно – всё дело в том, что при использовании функции с объявленным типом, этот тип автоматом применяется и к столбцу. Но есть существенное НО – таким образом можно указать number, text, date и т.п.– так называемые примитивные типы. Но в упомянутом примере нам было нужно целое число (Int64.Type), а ещё часто бывают проценты (Percentage.Type), деньги в конце концов (Currency.Type). Такие типы указать уже не получится, хотя…

let
    from = Table.FromRecords({
        [A = null, B = 2, C = 3, D = "4"],
        [A = "5", B = 6, C = null, D = 8],
        [A = 9, B = null, C = "10", D = 11]
    }),
    NumOrZero = [   func = (x)=> if x=null then 0 else Number.From(x),
                    ftyp = Type.ForFunction([ReturnType = Int64.Type, Parameters = [x = type any]], 1),
                    fres = Value.ReplaceType(func, ftyp)][fres],

    to = Table.TransformColumns(from, {},NumOrZero)
in
    to

разбираем только шаг NumOrZero – это запись, причём возвращается только поле fres (зачем так – читаем тут)
func – функция из предыдущего поста
ftyp – а вот тут мы используем функцию Type.ForFunction, она формирует тип функции, принимая два аргумента:
- signature – это запись из двух полей ReturnType (куда мы запишем возвращаемый тип – и здесь можно использовать Int64.Type) и Parameters (запись типов для аргументов – в данном случае один аргумент x)
- min – минимальное число аргументов для вызова функции (в данном случае один обязательный аргумент – поэтому значение 1)
fres – осталось только применить тип к нашей функции, для этого используем Value.ReplaceType

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

Теперь немного усложним – хорошо и удобно, когда функция маленькая, тогда весь код выше пишется руками и один раз. Но если функция сложная, могут меняться типы аргументов, их количество. Конечно можно взять за правило сначала функцию написать, а потом задавать типизацию; но можно слегка автоматизировать процесс:

NumOrZero = [   func = (x as any)=> if x=null then 0 else Number.From(x),
                    typ = Value.Type(func),
                    params = Type.FunctionParameters(typ),
                    min = Type.FunctionRequiredParameters(typ),
                    ftyp = Type.ForFunction([ReturnType = Int64.Type, Parameters = params], min),
                    fres = Value.ReplaceType(func, ftyp)][fres]

func – всё та же функция, но в ней указан тип для аргумента,
typ – получаем тип нашей функции (из чего он состоит мы только что разобрали),
params – вытащили описание для аргументов
min – вытащили минимальное число аргументов для вызова (FunctionRequiredParameters сама посчитает обязательные аргументы и отбросит те, для которых указано optional)
ftyp – а теперь пересобираем тип для функции, указав нужный ReturnType, а всё остальное в данном случае было сформировано автоматически
fres – ну и применяем к нашей функции сформированный тип.

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

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

Сложно о функциях 2 - Документация
#АнатомияФункций - Type.ForFunction
Всем привет!
Раз уж начали разбирать тему типизации функции, стоит рассмотреть ещё один, весьма важный вопрос. Все думаю знают, что при вызове встроенных функций появляется красивое диалоговое окно с её описанием, примерами использования, возможностью ввести аргументы.
Если вам хотелось сделать подобное для собственных функций, код ниже для вас:

let
    from = Table.FromRecords({
        [A = null, B = 2, C = 3, D = "4"],
        [A = "5", B = 6, C = null, D = 8],
        [A = 9, B = null, C = "10", D = 11]
    }),
    NumOrZero = [   func = (x as any)=> if x=null then 0 else Number.From(x),
            typ = Value.Type(func),
            params = Type.FunctionParameters(typ),
            min = Type.FunctionRequiredParameters(typ),
            ftyp = Type.ForFunction([ReturnType = Int64.Type, Parameters = params], min),
            doc = [
                    Documentation.Name = "NumOrZero", 
                    Documentation.Description = "функция заменяет значение <i><b>null</b></i> нулём и приводит значение к числовому типу", 
                    Documentation.LongDescription = "функция заменяет значение <i><b>null</b></i> нулём и приводит значение к числовому типу", 
                    Documentation.Examples = {
                                                [Description = "числа остаются числами", Code = "=NumOrZero(5)", Result = "5"], 
                                                [Description = "число как текст становится числом", Code = "=NumOrZero(""5"")",Result = "5"], 
                                                [Description = "null превращается в ноль", Code = "=NumOrZero(null)",Result = "0"]
                                            }
                    ],
            ftypwithmeta = Value.ReplaceMetadata(ftyp,doc), 
            fres = Value.ReplaceType(func, ftypwithmeta)][fres],

    to = Table.TransformColumns(from, {},NumOrZero)
in
    to

Мы работаем всё с той же NumOrZero из предыдущего поста. Только в неё добавилось несколько шагов:
doc - это запись, которая содержит элементы описания функции:
Documentation.Name – название функции

Documentation.Description и Documentation.LongDescription – описание функции (обратите внимание на теги – так можно навести красоту)

Documentation.Examples – это список примеров. Каждый пример представляет собой запись из элементов [Description,Code,Result] – т.е. описание конкретного примера, собственно код и результат, который будет в этой ситуации возвращён

Что же делать с этим описанием? Его необходимо добавить в тип нашей функции в виде метаданных
ftypwithmeta – на этом шаге используется Value.ReplaceMetadata, которая и добавляет описание doc к типу ftyp

fres – ну и теперь применяем к нашей функции func тип с добавленными к нему метаданными.

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

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

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

UnZip 1 - Общие замечания и код
#АнатомияФункций – BinaryFormat.*

Всем привет!
В теме про TableToBinaryText уже поднимался вопрос о том, что docx, xlsx, pptx и прочие файлы офиса представляют собой zip-архивы. Соответственно, решение многих задач начинается с распаковки архива.

Для начала несколько вводных замечаний:
- zip-архив представляет собой бинарное содержимое определенной структуры
- бинарное содержимое не имеет разметки и считывается последовательно
- функции BinaryFormat.* аналогичны по использованию сплиттеру (сравните Text.Split(txt,” “) и Splitter.SplitTextByDelimiter(" ")(txt) – т.е. функция с её аргументами как бы пишется отдельно, а анализируемый аргумент во вторых скобках после функции
- под двоичным форматом подразумевается порядок/характер считывания бинарной информации, фактически функция считывания

Теперь к делу (телу))):

(ZIP as binary) =>
let
    ui16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
    ui32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
    f = (x) => try Binary.Decompress(x, Compression.Deflate) otherwise null,
    g = (x) => BinaryFormat.Transform(BinaryFormat.Binary(x),f),
    h = (x) => [    head = BinaryFormat.Record([a = BinaryFormat.Binary(14), size=ui32, c=ui32, name=ui16, extra=ui16])(x),
                    body = BinaryFormat.Record( [flag  = true,
                                                Filename = BinaryFormat.Text(head[name]),
                                                Extras   = BinaryFormat.Text(head[extra]),
                                                Content  = g(head[size])])][body],
    iftrue = BinaryFormat.Choice(BinaryFormat.Binary(26),h),
    iffalse = BinaryFormat.Record([flag = false]),
    getfile = BinaryFormat.Choice(ui32, (x)=> if x = 67324752 then iftrue else iffalse, type binary),
    filelist = BinaryFormat.List(getfile, each [flag] = true)(ZIP),
    remove = List.RemoveLastN(filelist,1),
    to = Table.FromRecords(remove,{"Filename","Content"})
in
    to

Вот такая компактная и весьма шустрая функция. Можно просто скопировать и использовать. Тем же, кому интересно, как она устроена, рекомендую развернутый комментарий – всё просто не влезло в один пост.

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

UnZip 2 - Развернутый комментарий
#АнатомияФункций – BinaryFormat.*
Всем привет!
Это комментарий к коду функции UnZip из предыдущего поста:
ZIP – бинарное содержимое анализируемого файла
ui16 и ui32 – форматы считывания байтов (2 и 4 байта соответственно, с обратным порядком байтов – так оно хранится в архиве)

А теперь начну с конца, так будет понятнее:
to – превратили список записей remove в таблицу
remove – удалили последний элемент списка

filelist – применяем BinaryFormat.List к нашему файлу ZIP. Идея простая – данная функция последовательно читает бинарное содержимое и формирует список, причем для считывания использует первый свой аргумент – getfile, а читает пока выполняется условие во втором аргументе. Проблема в том, что последний элемент, для которого условие уже не выполняется, также включается в список, потому и понадобился remove

getfile – а вот это уже вынос мозга - BinaryFormat.Choice - первый аргумент показывает, что прочитать, а второй – что делать на основании прочитанного. В данном случае ui32 – прочитай четыре байта; если их числовое представление 67324752 (или шестнадцатеричное 04 03 4b 50 в обратном порядке, или 50 4b 03 04 в прямом, или PK\x03\x04 – сигнатура начала файла) то примени функцию iftrue иначе iffalse

iffalse – как бы двоичный формат считывания записи, но по факту он ничего не читает просто возвращает [flag = false] и останавливает процесс

iftrue – а вот здесь сложнее – снова BinaryFormat.Choice, только теперь считываем 26 байт (суммарно в начале файла всегда идет 30 байт информации, просто 4 мы уже считали в getfile, теперь дочитываем остаток) и применяем к ним функцию h (ещё раз напомню – функция определяет как мы будем читать последующее содержимое)

h – функция в виде записи из двух полей; head анализирует наши 26 байтов (a - первые 14 просто прочитали, size – следующие 4, это размер файла, c – следующие 4 и по 2 байта name и extra – порядок важен, это бинарник) – т.е. мы получили информацию о длине имени файла, длине extra поля и размере самого содержимого; body – используем информацию для последующего считывания – имя, extra, содержимое – порядок также важен, причём Filename сразу читаем как текст, а вот с Content ещё нужно повозиться – мы вызываем функцию g

g – мы не просто считываем содержимое, а осуществляем преобразование с использованием функции f

f – ну вот и добрались – напоминаю, что мы читаем архив, а значит его содержимое надо перевести в несжатое состояние – Compression.Deflate потому что это стандарт для zip.

Как-то так, код писать проще, чем объяснить его суть. Юзайте на здоровье!

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

UnZip 3 – Кириллические названия файлов
#АнатомияФункций – BinaryFormat.*

Всем привет!
Хочу логически завершить тему zip-архивов. В офисных документах все файлы имеют названия латиницей и проблемы с их чтением нет; но вот если вы откроете обычный zip, в котором названия файлов представлены кириллицей, то увидите такое: ����_����_�����.
Дело в том, что в zip кириллица (а точнее всё, что не латиница) кодируется в DOS-кодировках. Чтобы решить проблему BinaryFormat.Text нужно передать вторым аргументом правильную кодировку, в случае кириллицы – это cp866.
Ниже приведен код, в котором UnZip дополнена вторым аргументом, он не обязательный, а по умолчанию поддерживается кириллица.
Ну и оформил «по-боевому» - с блекджеком, документацией и типизацией…

[   func = (ZIP, optional cp)=>
        [   ui16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
            ui32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            b = (x) => BinaryFormat.Binary(x),
            c = if cp is null then 866 else cp,
            f = (x) => try Binary.Decompress(x, Compression.Deflate) otherwise null,
            g = (x) => BinaryFormat.Transform(b(x),f),
            h = (x) => [head = BinaryFormat.Record([a = b(14), size=ui32, c=b(4), name=ui16, extra=ui16])(x),
                        body = BinaryFormat.Record( [ FileName = BinaryFormat.Text(head[name],c),
                                                  Extras   = b(head[extra]),
                                                  Content  = g(head[size]),
                                                  flag  = true])][body],
            iftrue = BinaryFormat.Choice(b(26),h),
            iffalse = BinaryFormat.Record([flag = false]),
            getfile = BinaryFormat.Choice(ui32, (x)=> if x = 67324752 then iftrue else iffalse, type binary),
            filelist = BinaryFormat.List(getfile, each [flag] = true)(ZIP),
            remove = List.RemoveLastN(filelist,1),
            to = Table.FromRecords(remove, type table [FileName = text, Content = binary])][to],
    typ = type function (
                ZIP as (type binary meta [Documentation.FieldCaption = "бинарное содержимое (zip,xlsx,docx,pptx и т.п.)"]),
                optional cp as (type number meta [Documentation.FieldCaption = "кодировка", Documentation.SampleValues = {866}])
                         )           
                as table meta [ 
                                Documentation.Name = "UnZip (@buchlotnik)",
                                Documentation.LongDescription = "Функция считывает содержимое zip-архива и возвращает таблицу с полями FileName - имя файла (с путём, если он в подпапке) и Content - бинарное содержимое конкретного файла. Кодировка требуется для корректного распознавания <b>не латинских</b> имён файлов (для офисных документов указывать необязательно), по умолчанию 866 - кодировка DOS <b>с поддержкой кириллицы</b>"        
                                ],
    result = Value.ReplaceType(func,typ)
][result]

Юзайте на здоровье!

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

Получаем все таблицы из файла Word
#АнатомияФункций – Xml.Document

Всем привет!
Захотелось рассмотреть практический аспект применения UnZip – получение всех таблиц из файла Word. По этому поводу функция:

WordTablesList = (file)=>
    [
    getTable = (xml)=>[
        f=(x)=>[a = Table.SelectRows(x,(r)=>r[Name]="tr")[Value],
                b = List.Max(List.Transform(a,Table.RowCount)),
                c = Table.FromList(a,g,b)][c],
        g=(x)=>List.Transform(x[Value],h),
        h=(x)=>Text.Combine(List.Transform(Table.SelectRows(x,(r)=>r[Name]="p")[Value],i),"#(lf)"),
        i=(x)=>[a = Table.SelectRows(x,(r)=>r[Name]="r")[Value],
                b = (x)=> Table.SelectRows(x,(r)=>r[Name]="t")[Value],
                c = List.Combine(List.Transform(a,b)),
                d = List.Select(c,(x)=>Value.Is(x,Text.Type)),
                f = Text.Combine(d)][f],
        to = f(xml)][to],
    
    from = UnZip(file),
    xml = Xml.Document(from{[FileName="word/document.xml"]}[Content]){[Name="document"]}[Value]{[Name="body"]}[Value],
    filtr = Table.SelectRows(xml, each ([Name] = "tbl"))[Value],
    to = List.Transform(filtr,getTable)
    ][to]

from – получили содержимое файла через UnZip
xml – из содержимого получаем конкретно document.xml, в нем document, в нем body
filtr - выбрали только таблицы - tbl (если вам нужны не таблицы, а текст – выбирайте p)
to – вытащили из xml сами таблицы функцией getTable

Теперь подробнее про getTable:
to – итогом работы функции является применение функции f к xml-содержимому
f – на шаге a вынимаем только теги tr – это строки таблицы, шаг b – находим максимальную длину строки (шаг нужен, поскольку таблицы могут быть с объединёнными ячейками), ну и на шаге с собираем таблицу с использованием вспомогательной g
g – возвращает список, но к каждому элементу применяет h
h – собирает текст через разрыв строки (в таблицах тоже бывают абзацы), применив к каждому абзацу функцию i
i– докапывается до текста: a – берет теги "r", b – функция вынимания тега "t", c – получает из каждого элемента "t", d – оставляет только текстовое содержимое, f – собирает куски в единый текст.

Как бы и всё. С непривычки код может и выглядит напряжно, но суть простая – последовательно «матрёшкой функций» разбираем «матрешку xml». Это намного эффективнее, чем мышкоклацать десятки шагов с разворачиванием столбцов, добавлением индексаций, пайвотом и прочей мутью, которые здесь совершенно не нужны.
Так что пишите эффективно, пишите с удовольствием!

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