Как подключиться к файлам на Яндекс.Диске
#ВсякиеКоннекторы
И снова здрасьте. На хайпе по теме импортозамещения все чаще граждане перетаскивают свои данные на отечественные облака. И далее встаёт вопрос, а как их оттуда затащить в 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

List.TransformMany или двойное преобразование
#АнатомияФункций – List.TransformMany

Всем привет!
В чате всплыла задачка, поэтому решил разобрать List.TransformMany.
Читаем справку:

List.TransformMany(list as list, collectionTransform as function, resultTransform as function) as list

collectionTransform имеет сигнатуру (x as any) as list => ..., где x — это элемент в list. resultTransform проецирует форму результата и имеет сигнатуру (x as any, y as any) as any => ..., где x — элемент в list, а y — элемент, полученный путем применения collectionTransform к этому элементу.
Мдя… давайте разбираться.
Допустим имеем список {x1,x2,x3}.
collectionTransform (cT) может просто преобразовать аргумент, а может породить список – и на выход мы получим {cT(x1) ={y1}, cT(x2)={y21,y22},cT(x3)={y31,y32,33}}.
Далее resultTransform (rT) одновременно применяется и к исходным элементам, и к результату cT (к каждому отдельно – это самое важное), поэтому итоговый список будет выглядеть так: {rT(x1,y1),rT(x2,y21),rT(x2,y22),rT(x3,y31),rT(x3,y32),rT(x3,y33)}
Таким образом функция принимает на вход список и возвращает список, но вот количество элементов итогового списка зависит от работы collectionTransform.

Разберем упрощённый, но боевой пример:

let
    from = Table.FromRecords({  [a="раз",b="1-1"],
                                [a="два",b="2-1,2-2"],
                                [a="три",b="3-1,3-2,3-3"]}),
    lst = Table.ToRows(from),
    tr = List.TransformMany(lst,(x)=>Text.Split(x{1},","),(x,y)=>{x{0}}&Text.Split(y,"-")),
    to = Table.FromRows(tr)
in
    to

from – на входе имеем таблицу, с проблемным столбцом b – в нём надо разделить элементы по запятой в отдельные строки, а далее каждый отдельный ещё разделить на два столбца, но уже по дефису. Обычно это делается через Table.TransformColumns, Table.ExpandList.Column, Table.SplitColumn; но сегодня мы про другое:
lst – превратили таблицу в список
tr – выкатываем List.TransformMany. collectionTransform - (x)=>Text.Split(x{1},",") – делим по запятой второй элемент в списке (это был столбец b). resultTransform - (x,y)=>{x{0}}&Text.Split(y,"-") – нам нужно получить элементы строки – это первый элемент из списка x (бывший столбец a) и результат разделения y по дефису. Таким образом, мы сразу получили список из нужного числа строк, причём для каждой строки сразу получили все элементы в ходе разделения
to – осталось только собрать строки обратно в таблицу
Вуаля, задача решена.

В целом функция прикольная, но весьма специфическая. Пихать её во все задачи я бы не стал – она МОЖЕТ добавить скорости, но это НЕ ОБЯЗАТЕЛЬНО - каждый раз нужно экспериментировать.
А вот синтаксис действительно весьма лаконичный и на мой вкус прозрачный. Так что кому зайдет – юзайте на здоровье!

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

Self referencing query или incremental update в Excel
#АнатомияФункций – Excel.CurrentWorkbook()

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

Шаг 0 – исходный запрос (tst)

let
    f=(x)=>Excel.Workbook(x){[Name="Таблица1"]}[Data],
    
    from = Folder.Files("D:\tst")[[Name],[Content]],
    tr = Table.TransformColumns(from,{"Content",f}),
    tbl = Table.ExpandTableColumn(tr, "Content", {"a".."c"})
in
    tbl

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

Шаг 1 – вспомогательный запрос (tmp)

Excel.CurrentWorkbook(){[Name="tst"]}[Content]

Вся суть приёма состоит в том, что при загрузке запроса tst на лист у нас появляется таблица с именем tst и мы подключаемся к ней. Т.е. с точки зрения связей tst тащит данные из папки, а tmp – какую-то таблицу из книги и эти запросы как будто не связаны

Шаг 2 – меняем основной запрос (tst)

let
    f=(x)=>Excel.Workbook(x){[Name="Таблица1"]}[Data],
    
    from = Folder.Files("D:\tst")[[Name],[Content]],
    lst = List.Buffer(List.Distinct(tmp[Name])),
    filtr = Table.SelectRows(from, (x)=> not List.Contains(lst,x[Name])),
    tr = Table.TransformColumns(filtr,{"Content",f}),
    tbl = Table.ExpandTableColumn(tr, "Content", {"a", "b", "c"}),
    cmb = Table.Combine({tmp,tbl})
in
    cmb

ну и теперь колдунство – основные шаги остались те же, просто немножко добавляем:
lst – получили список уже загруженных файлов из tmp
filtr – отфильтровали запрос к папке, оставив только ещё не загруженные файлы
cmb – объединили информацию из вновь загруженных файлов с имеющейся

Всё! Пока в папке не появятся новые файлы, на лист будет грузиться tmp, т.е. имеющаяся информация, при появлении новых файлов – запрос обновится.
Преимущества метода – очевидно это сильно ускоряет, особенно когда много файлов и большой скачиваемый объем. Недостаток – инфу нужно грузить на лист, файл становится большой и есть ограничение в 1 048 575 строк (про шапку не забываем). Конечно, на таких объемах стоит обсуждать что-то более цивилизованное: БД для данных, PBI для обработки… но за неимением графини, имеем горничную )))

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

Excel.CurrentWorkbook(){[Name=…]}[Content] или зачем нам столько скобок?
#АнатомияФункций - Excel.CurrentWorkbook

Всем привет!
Уже разбирали вопрос обращения к полям и элементам списка в функциях, но поскольку регулярно возвращаемся к этому в чате, стоит разобрать пару нюансов.
Пример заявлен прямо в названии

Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]

Что же тут происходит?

Excel.CurrentWorkbook()

Сразу после названия функции идут круглые скобки – в них помещают аргументы. У CurrentWorkbook аргументов нет, поэтому скобки пустые.
Далее идут фигурные скобки – это обращение к элементу списка. Но ведь CurrentWorkbook возвращает нам таблицу. Фишка в том, что таблица – это список записей вида [Name, Content]. Т.е. так написать корректно:

Excel.CurrentWorkbook(){0}

Это вернет нам запись с первым (вы же помните про нумерацию с нуля) именем в текущей книге.

Другое дело, что для таблиц вместо конкретного индекса можно указать условие:

Excel.CurrentWorkbook(){[Name="Таблица1"]}

Это обозначает: верни элемент, у которого поле Name равно "Таблица1".

Для этих же целей можно было использовать и Table.SelectRows, но так короче и чутка шустрее.
Обращаю внимание – название поля пишется без кавычек, а вот строковое значение в кавычках. Бывают ситуации, когда имя поля содержит недопустимые символы (например разрыв строки), тогда его пишут в кавычках, но после решетки, поэтому такой синтаксис также корректен:

Excel.CurrentWorkbook(){[#”Name”="Таблица1"]}

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

Excel.CurrentWorkbook(){[Name="Таблица1"]}?

Знак вопроса указывает вернуть элемент, если он есть, и null, если его нет.

Ну а после фигурных идут квадратные скобки:

Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]

Это обозначает обращение к полю записи.

Название поля также пишем без кавычек или в кавычках, но после решётки:

Excel.CurrentWorkbook(){[Name="Таблица1"]}[#"Content"]

Ну и также, как и с элементами списка, можем использовать знак вопроса:

Excel.CurrentWorkbook(){[Name="Таблица1"]}?[Content]?

Собственно это обозначает: верни из текущей книги таблицу имен, конкретно строку, в которой поле Name равно Таблица1 (если такая строка есть), а конкретно поле Content из этой строки (если оно есть).
На выходе получим либо таблицу, либо null – всяко лучше, чем просто ошибку )))

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

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

Excel.CurrentWorkbook() vs Excel.Workbook() или в чём же разница
#АнатомияФункций - Excel.CurrentWorkbook, Excel.Workbook

Всем привет! Только что разбирали Excel.CurrentWorkbook, но есть ещё одна функция - Excel.Workbook. Из названий понятно, что обе функции предназначены для обращения к содержимому Excel-файлов, но есть ряд существенных отличий, которые стоит разобрать.

Отличие 0 – аргументы
CurrentWorkbook не имеет аргументов, поскольку обращается к текущей книге. Workbook имеет три аргумента – обязательный workbook и необязательные useHeaders и delayTypes. Первым аргументом должно быть бинарное содержимое файла – обычно его получают через Folder.Files или File.Contents, причём в качестве пути для File.Contents можно указать и ссылку на текущую книгу. Таким образом, оба кода дадут содержимое текущей книги:

Excel.CurrentWorkbook()
Excel.Workbook(File.Contents(“путь_к_текущему_файлу”))

Необязательные аргументы разберем ниже

Отличие 1 – возвращаемое содержимое
CurrentWorkbook возвращает таблицу с двумя полями [Name, Content]
Workbook – таблицу с четырьмя полями [Name, Item, Kind, Hidden]
Разница в том, что для CurrentWorkbook в файле существуют только таблицы и именованные диапазоны (именно диапазоны листа, которым присвоено имя; имён с константами, формулами и т.п. это не касается), а вот с помощью Workbook можно обратиться к таблицам (Kind=”Table”), именованным диапазонам (Kind=” DefinedName”) и листам (Kind=”Sheet”).
Здесь важное замечание – имена таблиц не могут совпадать, поэтому прописывая условия отбора для CurrentWorkbook мы указывали только имя, в то время как имена таблиц и листов совпадать могут, поэтому при обращении через Workbook лучше указать имя и тип:

Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]
Excel.Workbook(File.Contents("путь_к_текущему_файлу ")){[Name="Таблица1",Kind="Table"]}[Data]

Обратите внимание, что поля с содержимым отличаются – Content и Data соответственно

Отличие 2 – работа с листами
Это частая задача – собрать данные со всех листов в файле, понятно что она решается только через Workbook. Дополнительно надо отметить – не просто так Workbook возвращает свойство Hidden – она получает доступ ко ВСЕМ листам в книге, просто для скрытых вернет Hidden=true (и для очень скрытых тоже, кто не знает что это – советую погуглить).
Также обращаю внимание на второй аргумент – useHeaders, он по умолчанию false и тогда таблица листа будет с заголовками Column1, Column2 и т.д. Если же поставить useHeaders=true – первая строка таблицы будет использована в качестве заголовков – т.е. можно не прописывать PromoteHeaders отдельным шагом – когда в выгрузке один стандартный лист, это бывает удобно

Excel.Workbook(File.Contents("D:\ECWEW.xlsx")){[Kind="Sheet"]}[Data] //дефолтные заголовки
Excel.Workbook(File.Contents("D:\ECWEW.xlsx"),true){[Kind="Sheet"]}[Data] //заголовки из первой строки

Отличие 3 – типизация столбцов
Ещё одно важное отличие – CurrentWorkbook всегда возвращает столбцы таблицы нетипизированными, а Workbook – наоборот, типизированными. Последний аргумент – delayTypes – по умолчанию false, т.е. «не оставлять не типизированными», эту самую типизацию сохраняет. Когда жалуются, что с листами это не работает, советую проверить второй аргумент – если он не указан (т.е. false), у вас в первой строке текст заголовков, а потом, например, числовые значения – понятно, что такое типизируется как any, а вот так должно работать:

Excel.Workbook(File.Contents("D:\ECWEW.xlsx"),true,false){[Name="Лист1",Kind="Sheet"]}[Data] 

К чему весь этот многобуквенный спич? - Excel.CurrentWorkbook удобно мышкоклацается прямо из файла; с Excel.Workbook надо повозиться – прописать путь, но зато вы реально получаете всё, в том числе скрытое, содержимое файла и бонусом пару аргументов для тонкой настройки.
Да и в целом я сторонник разделения источника данных и его обработчика – поэтому практически всегда использую Excel.Workbook и обращаюсь к файлам с данными извне, заодно избегая проблемы с защитами/правами доступа и прочим.

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

Table.ExpandListColumn и Table.SplitColumn или как раскрыть список в столбцы
#АнатомияФункций - Table.ExpandListColumn, Table.SplitColumn

Всем привет!
Нашёлся инфоповод обсудить раскрытие списков в таблицах.
Собственно, пример:

let
    from = Table.FromRecords({  [a=1,b=2,c={{3,4},{5,6},{7,8}}],
                                [a=9,b=8,c={{7,6},{5,4},{3,2}}],
                                [a=1,b=3,c={{5,7},{9,8},{6,4}}]}),
    exp = Table.ExpandListColumn(from, "c"),
    splt = Table.SplitColumn(exp,"c",(x)=>x,{"c","d"})
in
    splt

from – таблица, в которой столбец c содержит в качестве значения список списков. Задача раскидать элементы в новые строки, а далее по столбцам
exp – первая часть решается легко через Table.ExpandListColumn
splt – а вот со второй частью через мышкоклац обычно сначала извлекают значения в текст с разделителями, а потом сплитят по этому же разделителю – ТАК ДЕЛАТЬ НЕ НАДО – надо сообразить, что Table.SplitColumn ждет от нас функцию, генерящую список значений, но у нас на входе и так список значений, поэтому (x)=>x – т.е. функция просто возвращает имеющийся список, и всё, никаких лишних телодвижений. Вот.

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

Excel.Workbook - собираем все листы из файла
#АнатомияФункций – Excel.Workbook, Table.Combine, Table.ExpandTableColumn

Всем привет!
В продолжение этой темы давайте разберем вопрос – как собрать информацию со всех листов в файле. Мы помним, что для этого нам понадобится Excel.Workbook, а вот дальше есть варианты:

Вариант 0 – нужна только информация с листов

let
    from = Excel.Workbook(File.Contents("путь\файл.xlsx"), true),
    sheets = Table.SelectRows(from, each [Kind]="Sheet"),
    to = Table.Combine(sheets[Data])
in
    to

from – подключение к файлу, второй аргумент true – использование первой строки в качестве заголовков
sheets – выбрали только листы (если вам нужны таблицы - [Kind]="Table")
to – ну и соединили таблицы воедино

Вариант 1 – нужно сохранить названия листов, данные на листах типовые

let
    from = Excel.Workbook(File.Contents("путь\файл.xlsx "), true),
    sheets = Table.SelectRows(from, each [Kind]="Sheet"),
    cols = Table.SelectColumns(sheets,{"Name", "Data"}),
    nms = Table.ColumnNames(cols{0}[Data]),
    to = Table.ExpandTableColumn(cols, "Data",nms)
in
    to

первые два шага те же
cols – выбрали столбцы с названиями листов и данными
nms – получили заголовки столбцов из первой таблицы
to – раскрыли табличный столбец

Вариант 2 - нужно сохранить названия листов, шапки таблиц могут различаться

let
    from = Excel.Workbook(File.Contents("путь\файл.xlsx "), true),
    sheets = Table.SelectRows(from, each [Kind]="Sheet"),
    cols = Table.SelectColumns(sheets,{"Name", "Data"}),
    nms = List.Distinct(List.Combine(List.Transform(cols[Data], Table.ColumnNames))),
    to = Table.ExpandTableColumn(cols, "Data",nms)
in
    to

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

Как бы и всё. Если таблицы не требуют дополнительных преобразований, количество кода минимально (особенно в варианте 0), а пользы приносит много.

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

Table.FromList + GroupKind.Local – шустрое подобие регулярок
#АнатомияФункций - Table.FromList, GroupKind.Local

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

let
    ld = List.Buffer({"0".."9"}),
    ldp = List.Buffer(ld&{"(",")"," ","-"}),
    
    f=(x)=>[a = Table.FromList(Text.ToList(x),(x)=>{x,if List.Contains(ldp,x) then 0 else 1},{"y","z"}),
            b = Table.Group(a,"z",{"i",each Text.Combine([y])},GroupKind.Local)[i],
            c = List.Transform(b,(x)=>Text.Select(x,ld)),
            d = List.Select(c,(x)=>x<>"")][d],
       
    lst = { " +7 (495) 617-61-16б доб. 1503",
            "тел: +7-913-728-64-87 Владимир",
            "(4152), доб. 23-81-47 +7962 215 17 17",
            "8 (423) 2 60-84-84, 143(внутренний)",
            "8 (495) 229-49-69, доб. 114",
            "+7 (383) 286-87-08 ext.24120"},
    to = Table.FromList(lst,(x)=>{x}&f(x),4)
in
    to

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

Ну а теперь о функции.
Для начала заведём два вспомогательных списка – ld и ldp
ld – список цифр (именно символов, кавычки не забываем) - это список символов, которые нас интересуют в конечном результате
ldp – к списку ld добавили символы, которые могут встречаться в номере телефона – скобки, пробелы, дефис - это список символов, которые могут быть объединены в одну группу
Обращаю внимание – списки вынесены в отдельные шаги и помещены в List.Buffer – это важно для ускорения работы

Теперь сами шаги функции:
a – собираем таблицу из списка символов анализируемого значения. На выход получим таблицу из двух столбцов – y это сам символ, а z – будет нулевым, если символ может содержаться в номере и единица – если не может
b – группируем по z, локально, пятый аргумент не пишем (подробно про пятый аргумент разбиралось тут), агрегируем в текст. Таким образом все потенциальные символы из номера телефона будут сгруппированы в отдельные текстовые значения
c – чистим полученные тексты, оставляя только символы из списка ld
d – выбираем не пустые тексты
Собственно, всё. Тонкую настройку работы функции можно осуществить меняя списки ld и ldp, экспериментируйте )))

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

Table.Skip – отделяем лишнее в таблице
#АнатомияФункций - Table.Skip

Всем привет!
По мотивам недавнего обсуждения в чате обсудим вопрос пропуска лишних строк в таблице.
Основной пример:

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("3VO7DsIwDPwV5LlDHTuEsvIZhaEVMIUyZUL8O6qDVAeUSK0YgOXk6+Nsn+32BrurD5cBYTsE76snNSmllHJKbUrXkd6rP9CGfaiZziMySmwFjSALnuTtRuJ6+p66+AR+se+yNqjuY8fHCbmPPoHKjh9OLUPgZkSLq7nVmC8xOfbiVOE0LRT1KjYq7vRCLZRAeLN0tobRli7UIFB2gboa+YPjZTlQJi5MZCFxHvMjdfl5IpWmaZSmze8IFm4BXaJI+SqbQpX2tcrDAw=="),Compression.Deflate))),
    skip=(x)=>Table.Skip(x),
    to = skip(from)
in
   to

Далее будем менять только функцию skip.

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

skip=(x)=>Table.Skip(x,8)

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

Фишка состоит в том, что во второй аргумент можно передать условие:

skip=(x)=>Table.Skip(x,(r)=>r[Column1]=null)

Обращаю внимание – функцию во втором аргументе пишем от записи. Будут пропущены все строки, которые СООТВЕТСТВУЮТ условию. В данном случае пока в Column1 будет встречаться null.
Результат не очень – давайте просто сменим столбец:

skip=(x)=>Table.Skip(x,(r)=>r[Column2]=null)

Теперь результат как надо.
Понятно, что мы не знали сколько строк пропустить, но знали в каком столбце искать. Причём искать можно не только null:

skip=(x)=>Table.Skip(x,(r)=>r[Column1]<>"заголовок")

в данном случае поиск по Column1 закончится на строке, содержащей «заголовок».

Но что, если мы знаем какое слово искать, но не знаем ни строки, ни столбца? Да пожалуйста:

skip=(x)=>Table.Skip(x,(r)=>not List.Contains(Record.ToList(r),"ключ"))

в этой ситуации каждую строку мы превращаем в список и проверяем в нём наличие конкретного значения.

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

skip=(x)=>Table.Skip(x,(r)=>not Text.Contains(Text.Combine(List.Select(Record.ToList(r),(x)=>x is text)),"клю"))

Тут финт ушами состоит в том, что превратив строку в список, мы выбираем только текстовые значения (is text), сцепляем все в одну строку (при этом пропускаем все null) и уже её проверяем на наличие фрагмента. Выходит достаточно шустро.

Как-то так. Простая функция, несложные приёмы, но очень лаконично можно получить нужный результат в одну строку.

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