Table.ToList, множественные замены, словари на записях или как всё-таки быстрее?
#АнатомияФункций - Table.ToList

Всем привет!
В чате всплыла задачка на множественную замену значений из текста.
Формулировка – есть текст с перечислением определённых значений через запятую, необходимо добавить ещё один столбец, с таким же перечислением, но с заменой значений на другие из таблицы соответствий. ОК.
Логика решения (в моей интерпретации) – разделить текст по разделителю, в полученном списке произвести замены, собрать список обратно в текст с разделителем. Первоначально решил через List.ReplaceMatchingItems (хорошая функция, мы ее ещё разберём), но словарь на записях оказался быстрее. На этом можно было бы и завершить, НО…
В названии заявлена Table.ToList – функция, которая возвращает список из строк таблицы, применив к списку значений каждой строки функцию преобразования. А мы уже выяснили, что Table.FromList работает быстрее, чем Table.FromRows. Вот и возникло предположение, что и Table.ToList может оказаться быстрее Table.ToRows.
Поэтому разбираем пример:
let
tbl1 = #table({"ID","txt"},{{"1","A,B"},{"2","B,C,D"},{"3","C,D,E,F"}}),
tbl2 = #table({"cat","txt"},{{"A","aa"},{"B","bb"},{"C","cc"},{"D","dd"},{"E","ee"},{"F","ff"}}),
dict = Record.FromTable(Table.RenameColumns(tbl2,{{"cat","Name"},{"txt","Value"}})),
f=(x as list)=>x&{g(x{1})},
g=(x)=> Text.Combine(List.Transform(Text.Split(x,","),(y)=>Record.Field(dict,y)),","),
to = Table.FromList(Table.ToRows(tbl1),f,{"ID","txt","new"}),
to1 = Table.FromRows(Table.ToList(tbl1,f),{"ID","txt","new"}),
to2 = Table.FromList(Table.ToList(tbl1,(x)=>x),f,{"ID","txt","new"}),
to3 = Table.FromList(Table.ToList(tbl1,f),(x)=>x,{"ID","txt","new"})
in
to3

tbl1, tbl2 – исходная таблица и таблица подстановки
dict – делаем словарь в виде записи (обращаем внимание на переименование столбцов, иначе Record.FromTable не будет работать)
f –функция, принимающая на вход список (значения всех столбцов строки) и добавляющая к нему дополнительный элемент – результат преобразования второго значения функцией g
g – логика этой функции уже описана выше – разделили текст по запятой, сделали замену по списку, собрали обратно через запятую

А теперь начинаем собирать результирующую таблицу (скорости выполнения указаны для тестовой выборки в 100k строк):
to – делим таблицу через ToRows, собираем через FromList с функцией f – это мой первоначальный вариант, отработал в среднем за 1,185 секунды
to1 – делим через ToList с функцией f, а собираем через FromRows – отработало за 1,563 секунды – чуда не случилось, FromRows – медленная
to2 – делим через ToList с функцией (x)=>x (обсуждалась тут), а собираем через FromList с функцией f – 1,184 секунды – чуууть быстрее первоначального варианта
to3 – "финт ушами" – разбираем через ToList с функцией f, а собираем через FromList с (x)=>x – и получаем… 1,169 секунды - прирост скорости не космический, но приятный.
Проверил в нескольких вариантах на выборках разного объёма с аналогичным результатом. Вывод – разбирать и собирать таблицы эффективнее через Table.ToList и Table.FromList, функцию преобразования при этом эффективнее подсовывать при разборке. Как-то так.

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

List.ReplaceMatchingItems vs Record.Field или какой транслит работает быстрее?
#АнатомияФункций - List.ReplaceMatchingItems, Record.Field

Всем привет!
Пока обсуждали сборку-разборку таблиц выяснили, что при малом числе вариантов замены (до 7 -8) List.ReplaceMatchingItems оказывается быстрее, чем Record.Field. Другое дело, что есть определенные ухищрения – например, использование Text.Upper/Text.Lower или Comparer.OrdinalIgnoreCase – чтобы не писать все варианты, а оставить только прописные или только строчные буквы.

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

let
    from = #table({"txt"},{{"абв"},{"аБв"},{"АБВ"}}),
    fulllist={{"А","A"},{"а","A"},{"Б","B"},{"б","B"},{"В","V"},{"в","V"}},
    partlist={{"А","A"},{"Б","B"},{"В","V"}},
    fullrec=[А="A",а="A",Б="B",б="B",В="V",в="V"],
    partrec=[А="A",Б="B",В="V"],
    to1=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.ReplaceMatchingItems(Text.ToList(x),fulllist))}),
    to2=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.ReplaceMatchingItems(Text.ToList(x),partlist,Comparer.OrdinalIgnoreCase))}),
    to3=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.ReplaceMatchingItems(Text.ToList(Text.Upper(x)),partlist))}),
    to4=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.Transform(Text.ToList(x),(y)=>Record.Field(fullrec,y)))}),
    to5=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.Transform(Text.ToList(x),(y)=>Record.Field(partrec,Text.Upper(y))))}),
    to6=Table.TransformColumns(from,{"txt",(x)=>Text.Combine(List.Transform(Text.ToList(Text.Upper(x)),(y)=>Record.Field(partrec,y)))})
in
    to6

Итак, по шагам
from – исходная таблица, в которой транслитерируем столбец txt
fulllist – список списков полная версия, т.е. описаны замены для букв как в нижнем, так и в верхнем регистре
partlist – то же самое, но оставлены только буквы в верхнем регистре
fullrec – словарь-запись, с полным перечнем возможных вариантов
partrec – сокращённый словарь-запись только для верхнего регистра
to1 – решение «в лоб» - разобрали текст на символы, сделали замену по списку, собрали обратно в текст
to2 – используем сокращённый список, поэтому добавили третий аргумент - Comparer.OrdinalIgnoreCase
to3 – а здесь текст на входе сразу перевели в верхний регистр
to4 – решение «в лоб» с полным словарём, но на записях
to5 – используем сокращённый словарь, при запросе поля записи переводим символ в верхний регистр
to6 – также сокращённый словарь, но текст уже на входе перевели в верхний регистр

Тестирование проводилось на таблице в 160k строк, разумеется с полными списками (fulllist и fullrec – по 66 элементов, partlist и partrec – по 33).

Результаты буду выражать в процентах от времени лидера:
to1 и to2 – 155%,т.е. сокращение словаря вдвое, но с Comparer.OrdinalIgnoreCase, выигрыша по времени не дало, и в целом с длинным списком List.ReplaceMatchingItems медленная функция
to5 – 131% - словарь на записях, но введение текстового преобразования при каждом вызове функции реально замедляет работу
to3 – 123% - список мы сократили вдвое, тестовое преобразование применили сразу ко всему тексту - как следствие получили достаточно быстрый результат (Пух, это ответ на твой вопрос)
to6 – 105% - текст перевели в верхний регистр за один заход, запись сократили вдвое – но этого оказалось не достаточно для победы, немножко, но не достаточно
to4 - 100% - как вы уже догадались – решение «в лоб» на записях оказалось самым шустрым.

Конечно, ещё можно экспериментировать, но ряд выводов напрашивается сам собой:
- транслитерацию делать на записях. (точка)
- если регистр важен – стараемся использовать Text.Upper/Text.Lower и применять ко всему тексту сразу
- словари на списках, превышающие 10 элементов, также не используем – тут записи вне конкуренции

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

& = Combine или почему конкатенация иногда слияние…
#АнатомияФункций - .Combine

Всем привет!
Разберем, как мне казалось, простую тему, а именно – использование & (combination operator) – да-да, я не ошибся - знак амперсанд обозначает именно комбинирование и в зависимости от ситуации оно происходит по-разному.
Начнём с текста:
Аперсанд объединяет два строковых значения в одно и все знают, что это называется конкатенация (сцепка, склеивание)

"a"&"b"//"ab"

Операндов можно использовать несколько с тем же результатом

"a"&"b"&"c"//"abc"

Большое количество строковых значений лучше объединять с помощью функции

Text.Combine({"a".."f"})//"abcdef"

Обращаем внимание, что функция называется Combine и всегда первым аргументом хочет от нас список комбинируемых значений – не забываем про фигурные скобки.
Кроме того, есть второй аргумент

 Text.Combine({"a".."c"},",")//"a,b,c"

который весьма кстати, если мы хотим получить на выходе перечисление

Со списками всё то же самое – происходит последовательное объединение, т.е. конкатенация
{1,2}&{3,4}//{1,2,3,4}
Списки могут быть любой длины и, например, вот так выглядит добавление элемента к списку

 {"a","b","c"}&{"d"}//{"a","b","c","d"}

т.е. добавляя что-то к списку мы конкатенируем два списка, просто второй содержит один единственный элемент.
Аналогично со списками списков:
{{1,2},{3,4}}&{{5,6}}//{{1,2},{3,4},{5,6}}
эта ситуация возникает, когда вы добавляете столбец к таблице или генерируете строки таблицы – к списку списков надо добавлять список – фактически это конкатенация двух списков списков )))

Как и с текстом есть соответствующая функция (обращаем внимание, что комбинируемые списки помещены в список):
List.Combine({{1,2},{3,4}})//{1,2,3,4}

Всё, что было выше – это конкатенация, тогда почему оператор называется по-другому? Да просто ещё есть записи и таблицы – и вот с ними он осуществляет другую операцию – слияние (merge).
Разберем записи. На таком примере разница не чувствуется:
[a=1,b=2]&[c=3]//[a=1,b=2,c=3]
Из двух записей сформировалась одна, в чём тогда подвох?
Да вот он:
[a=1,b=2]&[b=3,c=4]//[a=1,b=3,c=4]
поля в записи должны иметь уникальные имена и при слиянии двух записей с совпадающими именами полей остаётся одно значение, причём из записи справа.

Надеюсь теперь понятно, почему Record.Combine выдаёт такой результат:
Record.Combine({[a=1,b=2],[b=3,c=4],[c=5,d=6]})//[a=1,b=3,c=5,d=6]

Ну и, наконец, таблицы. Тут вообще весело – при слиянии таблиц строки второй оказываются под строками первой, причём совпадающие имена столбцов друг под другом, а несовпадающие оказываются правее. Ещё точнее – сначала идут имена столбцов из левой таблицы, а потом не совпавшие из правой.
Таблицы друг под другом (имена столбцов совпадают):
#table({"a","b"},{{1,2},{3,4}})&#table({"a","b"},{{5,6},{7,8}})

Таблицы друг под другом, но со сдвигом (имена столбцов не совпадают)
#table({"a","b"},{{1,2},{3,4}})&#table({"c","d"},{{5,6},{7,8}})

Таблицы с частично совпадающими именами столбцов

    #table({"a","b"},{{1,2},{3,4}})&#table({"c","b"},{{5,6},{7,8}})

Обратите внимание, что у правой таблицы порядок столбцов поменялся.

Ну и как водится, амперсанд благополучно заменяется функцией
Table.Combine({#table({"a","b"},{{1,2},{3,4}}),#table({"c","b"},{{5,6},{7,8}})})
Как вы догадались – с тем же результатом.

Как-то так. Оператор & не просто так называется оператором комбинирования – в зависимости от типа комбинируемых данных результат определяется разными правилами. Это необходимо понять, принять и простить )))

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

Text.Split vs Splitter.SplitTextByDelimiter или когда синтаксис имеет значение
#АнатомияФункций - Text.Split, Splitter.SplitTextByDelimiter

Всем привет!
В разрезе недавних постов про сборку таблиц нужно разобрать вопрос о разделении текста по разделителю. Для решения этой задачи мы имеем функцию Text.Split и семейство функций Splitter. Выглядит это примерно так:

Text.Split("a,b,c",",")//{"a","b","c"}
Splitter.SplitTextByDelimiter(",")("a,b,c")//{"a","b","c"}

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

На выходе получаем идентичный результат, но синтаксис у Text.Split чутка лаконичнее и прозрачнее. Только мы помним, что длина кода на скорость не влияет, тогда о чём пост? Дело в том, что задача разделения текста возникает в разных ситуациях и какую функцию выбрать – вопрос скорее вкуса, нежели каких-то объективных критериев. Но при этом существует две функции, у которых аргумент чётко заявлен как splitter as function – это Table.FromList и Table.SplitColumn. И вот в их случае выбор функции имеет принципиальное значение.

Рассмотрим обе ситуации:
Table.SplitColumn

let
    from = #table({"txt"},{{"1,2,3"},{"4,5,6"},{"7,8,9"}}),
    to1 = Table.SplitColumn(from,"txt",(x)=>Text.Split(x,",")),
    to2 = Table.SplitColumn(from,"txt",(x)=>Splitter.SplitTextByDelimiter(",")(x)),
    to3 = Table.SplitColumn(from,"txt",Splitter.SplitTextByDelimiter(","))
in
    to3

Table.FromList

let
    from = {"1,2,3","4,5,6","7,8,9"},
    to1 = Table.FromList(from,(x)=>Text.Split(x,",")),
    to2 = Table.FromList(from,(x)=>Splitter.SplitTextByDelimiter(",")(x)),
    to3 = Table.FromList(from,Splitter.SplitTextByDelimiter(","))
in
    to3

from – источник – таблица или список
to1 – использована пользовательская функция на основе Text.Split
to2 – использована пользовательская функция на основе Splitter.SplitTextByDelimiter
to3 – напрямую использован Splitter.SplitTextByDelimiter (обращаю внимание - именно этот вариант получается мышкоклацем, так что он иногда выдает очень правильные варианты)

Результаты, казалось бы, идентичны. НО! Вариант to1 оказывается чуть быстрее, чем to2, а вот to3 – в 1,5-2 раза быстрее двух предыдущих! Не знаю как именно построена логика у этих функций, но вердикт простой – если от нас хотят “splitter as function” и нам действительно нужен сплиттер – даже не думаем и передаем именно его, причем напрямую, а не как пользовательскую функцию – это реально быстрее.

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

Мерка 2.1 - теперь и диапазоны с формулами
#ПолезныеСсылки - Мерка

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

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

List.Alternate, Table.AlternateRows – забавные «чередователи» элементов
#АнатомияФункций - List.Alternate, Table.AlternateRows

Всем привет!
Периодически всплывают задачи, в которых необходимо сделать выборку элементов из списка или строк таблицы – каждый второй элемент, каждая третья строка. Для решения этой задачи используются List.Alternate и Table.AlternateRows.

Начнем с List.Alternate. У функции четыре аргумента – два обязательных и два необязательных:
list – сам список
count – число пропускаемых (удаляемых) элементов
repeatInterval (необязательный) – число элементов, которые остаются между пропускаемыми
offset (необязательный) – число элементов в начале списка, после которых начинаем пропускать.

Выглядит немножко запутанно, поэтому поехали смотреть пример:

let
    lst = {1..10},
    a=List.Alternate(lst,1),//{2,3,4,5,6,7,8,9,10}
    b=List.Alternate(lst,1,2),//{2,3,5,6,8,9}
    c=List.Alternate(lst,1,2,3),//{1,2,3,5,6,8,9}
    d = List.Alternate(lst,3,2,1),//{1,5,6,10}
    ListDelEachN=(l,n)=>List.Alternate(l,1,n-1,n-1),
    ListSelectEachN=(l,n)=>List.Alternate(l,n-1,1,0),
    e = ListDelEachN(lst,3),//{1,2,4,5,7,8,10}
    f = ListSelectEachN(lst,3)//{3,6,9}
in
    f

и разбирать по шагам
a – передали только обязательные аргументы, Alternate честно пропустил один элемент и вернул остальные – т.е. в таком виде это аналог List.Skip(lst,2) или List.Range(lst,2)
b – здесь мы передали дополнительно repeatInterval – т.е указали через сколько эементов надо снова повторить операцию удаления. Логика работы – «один пропустил, два оставил, один пропустил… и т.д.»
с – а это полный синтаксис – указали сколько эементов в начале списка оставить, сколько удалить и через сколько элементов повторить операцию удаления. Т.е. логика работы: «три в начале оставил, один пропустил, два оставил, один пропустил… и т.д.».
Тут просто важно не запутаться и передать аргументы в правильном порядке. Например, шаг d – «один в начале оставил, три пропустил, два оставил, три пропустил и т.д.».

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

ListDelEachN – удаляем каждый n-ый, поэтому первые n-1 оставляем (четвертый аргумент), далее удаляем 1 (второй аргумент), теперь каждый раз пропускаем n-1 элементов (третий аргумент) и снова удаляем до конца списка l (первый аргумент)

ListSelectEachN – оставляем каждый n-ый – это зеркальная ситуация, здесь мы начнём пропускать прямо с начала списка, поэтому четвертый аргумент нам не нужен (т.е. его можно было не писать, но он мне нужен для примеров ниже, поэтому поставил значение по умолчанию - 0), здесь мы каждый раз удаляем n-1 элементов (второй аргумент), далее оставляем 1 (это и есть нужный нам n-ый) и так до конца списка.
e,f – применение написанных функций – можете убедиться в работоспособности

В целом вроде несложно, надо просто привыкнуть к порядку и назначению аргументов. Но вот тут самое время сказать про Table.AlternateRows. У неё тоже четыре аргумента, правда на этот раз все обязательные, они имеют то же назначение, но идут… в другом порядке! (они ещё и называются по-другому, но это на мой взгляд уже мелочи).
Скажем «спасибо» разработчикам и запомним – в Table.AlternateRows сначала число элементов, на которое нужно отступить (offset), потом число пропускаемых (skip), потом число элементов после которых пропускание нужно повторить (take). Ну и пример кода:

let
    tbl = Table.FromColumns({{1..10}},{"a"}),
    TableDelEachN=(t,n)=>Table.AlternateRows(t,n-1,1,n-1),
    TableSelectEachN=(t,n)=>Table.AlternateRows(t,0,n-1,1),
    e=TableDelEachN(tbl,3),
    f=TableSelectEachN(tbl,3)
in
    f

Здесь приведены функции TableDelEachN и TableSelectEachN, удаляющая или наоборот выбирающая n-ые строки таблицы. Сравните их с функциями для списков – всё то же самое, «просто» аргументы в другом порядке.

Как-то так, функции хорошие, реально шустрые, для обозначенных задач прямо незаменимые, просто к тому, что они «немножко» разные нужно привыкнуть… )))

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

маленькое объявление - активный участник наших чатов и просто хороший человек Игорь (@CubRoot) встал на путь поиска путей повышения эффективности кода и созрел для своего первого поста в канале. Посему попрошу поддержать лайком и репостом))) Думаю его вклад будет полезен многим )))

Skip, Range или Remove или как полезно читать Спецификацию.
#АнатомияФункций – Table.Skip, Table. Range, Table.RemoveFirstN, List.Skip, List. Range, List.RemoveFirstN

Всем привет!
Разбирал функции М в Спецификации, а конкретно функцию Table.Range:
Синтаксис :

Table.Range(table as table, offset as number, optional count as nullable number) as table

В описании к функции: Table.Range нашел интересное сходство с отличной функцией Table.Skip


(table as table, optional countOrCondition as any) as table

Ну и вдогонку с функцией:

Table.RemoveFirstN(table as table, optional countOrCondition as any) as table

Все эти функции позволяют «избавить» таблицу от шапки с определенным количеством строк (ну или просто пропустить несколько «мусорных» строк – при парсинге сайта, или pdf, например)

В случае с Text.Skip и Text.RemoveFirstN можно передавать как индекс (сколько строк пропустить), так и функцию (какие строки пропустить), тогда как Table.Range принимает только индекс. Казалось бы разницы особой нет, но лучше сделать текст скорости:
Берем таблицу из 100К строк и убираем из нее шапку в 50k строк

let
from = Excel.CurrentWorkbook(){0}[Content],
rng = Table.Range(from,50000),
skp=Table.Skip(from,50000),
rfN=Table.RemoveFirstN(from,50000)
in
rfN

Самым быстрым оказался RemoveFirstN, Range на 2% медленнее, а Skip – на почти 10% медленнее!.
Возник вопрос: А в чем собственно дело ?
В чате, маэстро @buchlotnik тут же дал ответ:
Skip-то думает, передавать по функции или по индексу, а Range сразу откусывает нужное количество.
Тест с List.Skip/Range/RemoveFirstN подтвердил ответ @buchlotnik в части работы Range и Skip – Skip оказалась медленнее почти на 20%.
Вывод: хотим быстрее – юзаем Range.
Ну и на закуску, в подтверждении мысли Михаила @buchlotnik, вишенка на торте – работа Мерки 2.1 в excel и сравнение скорости на функциях с явной передачей индексов и без них.
Формулы для поиска значения в диапазоне с типом сопоставления (у нас используется ноль в качестве аргумента):

=ВПР(искомое; где ищем; столбец; 0)
=ВПР(искомое; где ищем; столбец;)
=ПОИСКПОЗ(искомое;где ищем;0)
=ПОИСКПОЗ(искомое;где ищем;)

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

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

Надеюсь было полезно.
Спасибо Михаилу @buchlotnik за науку и помощь в написании поста.
@CubRoot

HEX2DEC – или чем ещё полезны Binary и BinaryFormat
#АнатомияФункций – Custom Functions

Всем привет!
В чате всплыла тема преобразования шестнадцатеричного представления чисел в десятичное. Не самая частая ситуация, но всё равно неприятно, когда вместо символов кириллицы получаем «C4» или какой-нибудь «FF».
Типовое решение задачи есть, сразу его и разберем:

let
    from = {"C4", "EB", "FF", "20", "F2", "E5", "F5", "2C", "20", "EA", "F2", "EE", "20", "E2", "20", "F2", "E0", "ED", "EA", "E5", "29", "29", "29"},
    f=(x)=>Expression.Evaluate("0x"&x),
    to = Text.FromBinary(Binary.FromList(List.Transform(from,f)),1251)
in
    to

from - на вход прилетел список символов в шестнадцатеричной кодировке (точнее текст кодов этих символов)
f – функция преобразования, основанная на функции Expression.Evaluate. Здесь используется тот факт, что префикс "0x" обозначает шестнадцатеричную кодировку – мы добавляем его к строковому значению и просим вычислить полученное – на выходе получаем обычное десятичное число.
to – далее список полученных кодов преобразовали в список чисел, получили из него бинарник и прочитали как кириллический текст (кодировка 1251)

Всё очень лаконично, но ходят слухи, что из-за Expression.Evaluate компьютеры просто вешаются. Автор не разделяет такой точки зрения, поскольку на больших массивах время выполнения составит 200% от самого быстрого из известных мне вариантов (в два раза медленнее, но не на порядки, как обычно утверждают). Тем не менее, ускориться можно.
Перепишем функцию f вот так:

f=(x)=>[a=[0=0,1=1,2=2,3=3,4=4,5=5,6=6,7=7,8=8,9=9,A=10,B=11,C=12,D=13,E=14,F=15],
            b=List.Reverse(Text.ToList(x)),            c=List.Accumulate(b,[i=0,n=0],(s,c)=>[i=s[i]+1,n=s[n]+Number.Power(16,s[i])*Record.Field(a,c)])[n]][c]

разберём её шаги –
a – словарь из записи (в сети можете найти массу вариаций – на перечислении, списках списков и т.д.)
b – преобразование текста в последовательность символов в обратном порядке
c – ну и преобразование полученного списка в число – из записи получаем численное значение и умножаем на соответствующую степень 16 (здесь через Record.Field, в сети есть варианты через List.Positions, List.PositionOf, List.Generate и т.д.), ну и суммируем. В данном случае глобально использован List.Accumulate, но опять же в сети можете найти через List.Sum(List.Transform… и даже через рекурсию.
Получилось достаточно громоздко, но по скорости – это уже 130% от самого шустрого. Т.е. мы действительно ускорили процесс, причем в полтора раза. Другое дело, что, как вы поняли, такой подход уже многократно описан и мой по скорости не сильно выигрывает, поскольку использует тот же принцип.

Сегодня же я хочу показать совсем другой вариант, скорость которого и составляет эталонные 100%:

f=(x)=>BinaryFormat.Byte(Binary.FromText(x,BinaryEncoding.Hex))

вот так, в одну строчку )))
Суть простая – мы получаем бинарник напрямую из его строкового представления (Binary.FromText) ну и читаем полученный байт (каждый символ в шестнадцатеричном представлении отвечает за 4 бита, два символа дают 8 бит или 1 байт).
Кто-то, разумеется, возмутится – ведь это может быть и бОльшее число (т.е. более двух шестнадцатеричных знаков), функции выше с ним бы справились, а эта нет. Согласен, но в этой ситуации поступаем, например, так:

f=(x)=>BinaryFormat.UnsignedInteger32(Binary.FromText(Text.PadStart(x,8,"0"),BinaryEncoding.Hex))

т.е. с помощью Text.PadStart дополняем текст нулями до 8 символов и читаем не байт, а четырёхбайтное целое (или до 16 символов и тогда читаем восьмибайтное) – на скорости это практически не сказывается, зато получаем универсальную функцию и в одну строчку.

Никаких «финтов ушами», никаких специфических приёмов – просто стандартная библиотека – любите её, в ней много интересного.

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

Table.TransformColumnTypes – обновляем типы вручную, но пачками
#АнатомияФункций - Table.TransformColumnTypes

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

По этому поводу напишем немножко кода и разберем почему так:

let
    from = Table.FromRecords({[Наименование="тапки",Категория="обувь",ЦенаОпт=1000,ЦенаРозн=2000,Количество=100,СуммаОпт=100000,СуммаРозн=200000,Продавец="Вася",ДатаПоступления=44562,ДатаПродажи=44835,ДатаОтгрузки=44866,Примечание="обычные тапки"]}),
    nms = Table.ColumnNames(from),
    dlst = List.Select(nms,(i)=>Text.Contains(i,"Дата")),
    nlst = List.Select(nms,(i)=>List.Contains({"Цена","Количество","Сумма"},i,(x,y)=>Text.Contains(y,x))),
    tlst = List.Difference(nms,dlst&nlst),
    f=(x,y)=>List.Transform(x,(i)=>{i,y}),
    transflst = f(dlst,Date.Type)&f(nlst,Number.Type)&f(tlst,Text.Type),
    to = Table.TransformColumnTypes(from,transflst)
in
    to

from – на вход прилетела таблица, здесь не очень большая, просто чтоб пост не загромождать, но и дат может быть больше и всякие цены с НДС – короче включите фантазию
nms – получили список имён всех столбцов
dlst – из общего списка получаем только имена столбцов с датами
nlst – также из общего списка получаем имена столбцов с числовыми значениями (сам приём обсуждался тут и основываясь на этом принципе вы можете адаптировать код под ваши ключевые слова)
tlst – ну и все столбцы не с датами и не с числами мы хотим сделать текстовыми – тут нам в помощь List.Difference - из всех имён оставили только те, которые не вошли в dlst и nlst.
Ок, теперь вспомогательная функция f – она принимает два аргумента: x – список, y – тип, и преобразует наш список в список списков вида {название,тип} (это мы тоже на самом деле уже обсуждали )
transflst – на основе наших списков с помощью вспомогательной функции собираем списки списков преобразований и объединяем их (как работает амперсанд со списком списков обсуждали тут)
to – применили наш список преобразований типов к таблице – и всё!

Да, кому-то может показаться, что это несколько громоздко, но на самом деле если сравнить код выше с тем, что обычно содержится в шаге «Изменённый тип», то окажется, что написано всё более чем компактно )))

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

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

List.Combine+Table.FromList vs AddIndexColumn+AddColumn+Unpivot+Pivot + что там ещё можно наклацать мышкой
#Невсейогуртыодинаковополезны

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

Исходные данные - таблица вида:

#table({"1","2","3"},{{"01.02.22","02.02.22","03.02.22"},{100,200,300},{"04.02.22","05.02.22","06.02.22"},{500,700,900}})

Ее требуется привести к формату:

#table({"1","2"},{{"01.02.22",100},{"02.02.22",200},{"03.02.22",300},{"04.02.22",500},{"05.02.22",700},{"06.02.22",900}})

Предложение автора ролика в коде (исходный диапазон представлен в виде таблицы excel. Ее можно самостоятельно по примеру выше составить на листе):

    get_data = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
    tab_idx =Table.AddIndexColumn(get_data,"Индекс",0,1, Int64.Type),
    tab_mod =Table.AddColumn(tab_idx,"Остаток от деления",each Number.Mod([Индекс], 2),type number),
    tab_row_num =Table.AddColumn(tab_mod,"row_num",each if [Остаток от деления] = 0 then [Индекс] else [Индекс] - 1),
    tab_remove_cols = Table.RemoveColumns(tab_row_num, {"Индекс"}),
    tab_unpivot = Table.UnpivotOtherColumns(tab_remove_cols,{"Остаток от деления", "row_num"},"Атрибут","Значение"),
    tab_pivot = Table.Pivot(Table.TransformColumnTypes(tab_unpivot,{{"Остаток от деления", type text}},"ru-RU"),
    List.Distinct(Table.TransformColumnTypes(tab_unpivot,{{"Остаток от деления", type text}},"ru-RU")[#"Остаток от деления"]),"Остаток от деления","Значение"),
    cols_select = Table.SelectColumns(tab_pivot,{"0", "1"})

Подход в представленном коде: добавление столбца индекса, нахождение остатков по модулю и далее ресурсоемкие функции Pivot и Unpivot.
Данное решение имеет право на жизнь и ни в коем случае не оспаривается.
Есть ли альтернатива данному подходу?
Сходу решение:
from = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
lst1=List.Combine(Table.ToRows(Table.AlternateRows(from,1,1,1))),
lst2=List.Combine(Table.ToRows(Table.AlternateRows(from,0,1,1))),
zp=List.Zip({lst1,lst2}),
to=Table.FromList(zp,(x)=>x)
Логика кода: Берем четные и нечетные списки по строкам таблицы, комбайн и через ZIP определяем в Table.FromList.
Проверка скорости на Мерке показала, что альтернативное решение как минимум в 3 раза по скорости выше, чем код автора ролика на Youtube.

Решение было показано Михаилу (@buchlotnik) и от маэстро PQ последовал ответ в виде еще одного решения:

from = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
to = Table.FromList(List.Combine(List.Transform(List.Split(Table.ToList(from,(x)=>x),2),List.Zip)),(x)=>x)

Согласитесь, лаконично, логично и красиво.

Ожидаемо, что по скорости данное решение не оставило шансов предыдущим двум. Можете сами убедиться на 56k строк:

Запрос       Среднее      От         До     Общее
Buch           0,59       0,53       0,7    14,84
CubRoot        0,93       0,86       1,09   23,27
Youtube        2,84       2,67       3,14   71,02

О чем этот пост: Решения через "мышку" имеют право на жизнь, но если Вы хотите ощутить в полной мере, как дрожит двигатель М, берите в руки механику – используйте расширенный редактор, читайте Спецификацию и предлагайте свои оригинальные решения.
Вместе мы сделаем мир М чище )

Надеюсь было полезно
Спасибо Михаилу @buchlotnik за помощь в написании статьи.
@CubRoot

List.TransformMany vs. Table.CombineColumns или параллельный сплит нескольких столбцов
#АнатомияФункций - List.TransformMany, Table.CombineColumns

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

let
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZLBDsIwCIZfpel5aYDSWX0V9eCmvoEn47u7QeeoiXOJXn4G+6HlS/d3f7gBRxa9iHaireh5rjOKkmjvd/69aNv5Kgq+UZvOz2orvUiuCsVrz4y9aCrDdrGx11VjHsYlN84AOCFr5JIPP5yvmsBs0Q2ttAkQAwFF9xpCAdpS0gptA2SpaA4YENXhH82PDOPVtFwMz7OBdlokGV0VVpDkDyQngMjL2GBglAwRzAG2kv8NiB6ZzDcU2xIKdlVYgSL9iGJ8HBYF5emx/AFFNrZ2vnepTDssAUmuCiuAtB+AUP7yJjDAZlyc/OP4BA=="),Compression.Deflate))),
    f=(x)=>List.Zip(List.Transform(x,(i)=>if Text.Contains(i,"#(lf)") then Text.Split(i,"#(lf)") else {i})),
    cmb = Table.CombineColumns(from,{"дни","даты"},f,"tmp"),
    exp = Table.ExpandListColumn(cmb, "tmp"),
    to = Table.SplitColumn(exp,"tmp",(x)=>x,{"дни","даты"})
in
    to

from – вот такая таблица на входе – столбцы «должность», «фио», «номер»; а вот столбцы «дни» и «даты» нужно разделить по разрыву строки, причём оба сразу в новые строки.

f - поскольку сплитить будем сразу оба столбца, обрабатывать будем их как список. Сначала смотрим что происходит внутри List.Transform: (i)=>if Text.Contains(i,"#(lf)") then Text.Split(i,"#(lf)") else {i}) – если текст содержит разрыв строки делаем сплит по этим разрывам, иначе – оставляем список из одного исходного значения. Таким образом, по каждому столбцу получим список значений (с одним или несколькими элементами) и далее эти списки скармливаем List.Zip – как мы помним, она сошьет элементы построчно.

cmb – скомбинировали столбцы с помощью нашей функции – в столбце “tmp” теперь содержится список списков (пары значений построчно)

Что делать дальше уже разбирали тут:
exp – развернули списки в отдельные строки
to – развернули строки в отдельные столбцы

Вроде просто и логично, тогда о чём пост? Да о том, что на списках быстрее )))

Шаги from и f оставляем на месте, а вот дальше меняем стратегию:

    nms = Table.ColumnNames(from),
    lst = Table.ToList(from,(x)=>x),
    tr = List.TransformMany(lst,(x)=>f(List.LastN(x,2)),(x,y)=>List.FirstN(x,3)&y),
    to = Table.FromList(tr,(x)=>x,nms)

nms – получили названия столбцов исходной таблицы

lst – разобрали таблицу в строки (функция, которая ничего не делает)

tr – и теперь дважды преобразуем полученный список строк:
(x)=>f(List.LastN(x,2)) – применяем нашу функцию f к последним двум элементам (получаем список списков);
(x,y)=>List.FirstN(x,3)&y – собираем строки из первых трёх элементов по строке и объединяем их с каждой парой из полученного списка списков. На выходе получаем сразу все строки

to – осталось только обратно собрать таблицу

По сути, всё то же самое, но во втором случае мы вместо табличных преобразований применили списочные, на этом можно получить выигрыш по производительности на 15-20 %.

Многие говорят о том, что «не видят» в своих задачах применения для List.TransformMany – данный пример должен дать пищу для размышлений.

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

Всем привет!
Маленькое объявление.
Поскольку визуальное изображение работы прокуренного аналитического мозга нашло своих приверженцев, начинаем новую рубрику на канале - #ВесёлыеКартинки
Дисклеймер: Цветовая гамма изображений не имеет ничего общего с моей профессиональной деятельностью 😉

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

List.TransformMany
#ВесёлыеКартинки

Визуальное отображение последнего поста про TransformMany

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

Record.FieldOrDefault для GroupKind.Local или как заменить FillDown
#АнатомияФункций - Record.FieldOrDefault, GroupKind.Local

Всем привет. Подогнали в чате задачку (тут последний стенд - там и мышкоклац, и моё решение на списках с последующим FillDown). Я бы решил и забыл, но Игорь (@CubRoot) попытался примотать Table.Group и даже по скорости меня обогнал (потом правда выяснилось, что тестирование было некорректным, но было поздно - я уже всё переписывал) - поэтому пришлось засучить рукава и немножко поиграть в оптимизацию. Разберем самый эффективный из полученных вариантов:

let
    from = Table.FromColumns({{"насосы","н-1","н-2","н-3","подносы","п-1","п-2","п-3","пылесосы","п-1","п-2","п-3"}},{"категория"}),
    lst = {"насосы","подносы","пылесосы"},
    rec = Record.FromList(List.Repeat({1},List.Count(lst)),lst),
    f=(s,c)=>Record.FieldOrDefault(rec,c,0),
    gr = Table.Group(from,"категория",{"товар",each List.Range([категория],1)},GroupKind.Local,f),
    to = Table.ExpandListColumn(gr, "товар")
in
    to

from – вот такая таблица, в которой смешаны категории и товары

lst – список категорий

rec – изюминка решения – делаем запись, в которой названия полей – категории, а вот значения – единицы

f – функция для Table.Group – проверяет наличие соответствующего поля в записи – если находит – вернёт 1, иначе – 0 (т.е мы сразу получаем 0 или 1 для пятого аргумента )

gr – ну и группируем по единственному столбцу, агрегацию делаем по нему же, пропуская первую строку (это строка самой категории). Обращаю внимание, что использовано Range (почему – обсуждали тут ). При этом функция f нам сразу отдаёт числовое значение, поэтому обошлись без if или Number.From

to – осталось только раскрыть списки в строки по столбцу «товар»

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

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

Text.AfterDelimiter, Text.BeforeDelimiter, Text.BetweenDelimiters – получаем фрагмент из текста
#АнатомияФункций - Text.AfterDelimiter, Text.BeforeDelimiter, Text.BetweenDelimiters

Всем привет!
Подкинули тут задачку – «вытащить из текста значение в скобках… только внутри скобок могут быть ещё скобки, а вот их надо оставить». Вроде не очень сложно, но по этому поводу разберем как работают Text.*Delimiter*.

Сразу пример, а потом разберем по шагам:

let
    txt="какой-то текст (текст в скобках (и текстом в ещё (и ещё) одих скобках) снова текст) и текст вне скобок",
    a = Text.AfterDelimiter(txt,"("), //текст в скобках (и текстом в ещё (и ещё) одих скобках) снова текст) и текст вне скобок
    b = Text.AfterDelimiter(txt,"(",1), //и текстом в ещё (и ещё) одих скобках) снова текст) и текст вне скобок
    c = Text.AfterDelimiter(txt,"(",{0,RelativePosition.FromEnd}), //и ещё) одих скобках) снова текст) и текст вне скобок
    d = Text.BeforeDelimiter(txt,")"), //какой-то текст (текст в скобках (и текстом в ещё (и ещё
    e = Text.BeforeDelimiter(txt,")",{0,RelativePosition.FromEnd}), //какой-то текст (текст в скобках (и текстом в ещё (и ещё) одих скобках) снова текст
    f = Text.BetweenDelimiters(txt,"(",")"), //текст в скобках (и текстом в ещё (и ещё
    g = Text.BetweenDelimiters(txt,"(",")",0,{0,RelativePosition.FromEnd}),//какой-то текст
    h = Text.BetweenDelimiters(txt,"(",")",0,{0,RelativePosition.FromStart}),//текст в скобках (и текстом в ещё (и ещё
    i = Text.BetweenDelimiters(txt,"(", ")",0,Text.Length(Text.Select(txt,")"))-1), //текст в скобках (и текстом в ещё (и ещё) одих скобках) снова текст
    j = Text.BetweenDelimiters(txt,"(", ")",Text.Length(Text.Select(txt,"("))-1,0),//и ещё
    k = Text.AfterDelimiter(Text.BeforeDelimiter(txt,")", {0, RelativePosition.FromEnd}),"(")//текст в скобках (и текстом в ещё (и ещё) одих скобках) снова текст
in
    k

a – просто получили текст после разделителя
b – получили текст после второго вхождения разделителя (не забываем про нумерацию с нуля)
с – получили текст после последнего вхождения разделителя (0 обозначает первый, а FromEnd – с конца, также обратите внимание на фигурные скобки)
d – текст до разделителя (до первого вхождения)
e – текст до последнего вхождения разделителя

Ну, ок, вроде всё понятно – указываем разделитель, указываем при необходимости номер вхождения, но нам-то надо вынуть текст между скобок (от первой открывающей до последней закрывающей)– поэтому продолжаем:
f – получили текст от первой открывающей скобки до первой закрывающей
g – а вот тут не понятно, верно? Проблема в том, что позицию первого разделителя мы можем указать с начала или с конца текста, а вот для второго разделителя отсчёт идет не по тексту целиком, а от первого разделителя. Т.е. от первой открывающей скобки поиск пошёл в начало текста и получили то, что получили
h – и если поиск развернуть – получим то же самое, что и в шаге f

Печально, но делаем вывод – раз нельзя привязать положения обоих разделителей к исходному тексту, придётся считать.
i – собственно решение изначальной задачи – берём первую открывающую скобку, а номер последней закрывающей определяем как Text.Length(Text.Select(txt,")"))-1 – т.е. просто оставили в тексте только закрывающие скобки и получили его длину (не забыли -1 так как нумерация с нуля)
Ну а если бы задача стояла наоборот – получить текст из самых внутренних скобок:
j – вычисляем последнюю открывающую скобку и берём текст до первой закрывающей

В примере ещё маячит шаг k – и он здесь не просто так. Это к тому, что если нужен текст между разделителями, совершенно не обязательно использовать BetweenDelimiters – можно же использовать комбинацию AfterDelimiter +BeforeDelimiter – и она на данной задаче работает шустрее (причём процентов так на 10 на массиве в 10k). Почему? Да потому что для штатной функции нам потребовались дополнительные вычисления. Пишите как вам удобнее, но не забывайте про эффективность )))

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

ФИЛЬТР.XML - xpath
#ExcelFunctions

Всем привет!
Все думаю знают, что я упоротый эксельщик, поэтому периодически буду складывать сюда полезности, про которые "в книжках не пишут".
Во вложении мой краткий мануал по использованию аргумента xpath в функции ФИЛЬТР.XML - это конечно не замена регулярок, но позволяет многое.

Пы.Сы. Не пугайтесь - M остается основной тематикой канала. Вечером обязательно будет привычный пост - сегодня разберем Folder.Contents.

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

Folder.Contents – собираем данные из папки, но выборочно
#АнатомияФункций - Folder.Contents

Всем привет!
Как-то мы уже разбирали сбор данных из папки с помощью Folder.Files. Если не читали – гляньте хоть одним глазом, потому что сейчас будем немножко развивать мысль.

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

Вариант 0 – нужны только файлы из корневой папки
Представьте себе ситуацию, когда в папке «Отчёты» у вас лежат аналитические таблицы и есть подпапки «Исходники», «Архив» с кучей промежуточных версий, кусков информации и т.д. Можно, конечно, отфильтровать, ориентируясь на [Folder path], а можно написать так:

from = Folder.Contents("C:\Users\buchlotnik\Desktop\tst"),
filtr = Table.SelectRows(from, each ([Extension] <> ""))

Фишка в том, что Folder.Contens вернёт точно такую же таблицу, как и Folder.Files, НО! Вместо того, чтобы проходить по всем подпапкам и вынимать оттуда файлы, она вернёт для каждой подпапки строку, где Extension="" и Content содержит не binary, а table – т.е. результат применения Folder.Files к подпапке но в свёрнутом виде.
Поэтому в коде выше мы просто отфильтровали все подпапки и оставили только файлы.
Можно и по-другому:
from = Folder.Contents("C:\Users\buchlotnik\Desktop\tst"),
filtr = Table.SelectRows(from, each [Extension] = ".xlsx")

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

Вариант1 – нужны конкретные подпапки
Обратная ситуация – есть папка, в которой лежат какие-то файлы (чьи-то сводки, аналитика и т.п.) и есть подпапки по регионам (продуктам/менеджерам/филиалам – нужное подчеркнуть), где также лежит куча файлов, а вам нужно в каждом регионе найти подпапку «Продажи» и забрать оттуда самый свежий файл...
Ну что? Фильтр по [Folder path], группировка по нему же и потом Table.Max? Да, так можно, а можно и по-другому:

let
    f=(x)=>[a=x{[Name="Продажи"]}[Content],
            b=Table.SelectRows(a,each [Extension]=".xlsx"),
            c=Table.Max(b,"Date created")[Content],
            d=Excel.Workbook(c,true){0}[Data]][d],
    
    from = Folder.Contents("C:\Users\ buchlotnik \Desktop\tst"),
    filtr = Table.SelectRows(from, each ([Extension] = ""))[[Name],[Content]],
    tr = Table.TransformColumns(filtr,{"Content",f}),
    to = Table.ExpandTableColumn(tr, "Content", {"продукт", "сумма"})
in
    to

from – обратились к папке
filtr – оставили только подпапки (Extension=””), попутно оставили только интересующие нас столбцы – Name и Content
tr – совершили магию над столбцом Content с помощью функции f
to – и раскрыли нужное нам содержимое… Всё просто )))

Осталось только разобраться с «магией» – смотрим на функцию f:
a – выбрали содержимое подпапки «Продажи» (поле Content содержит всю информацию о файлах, внутри неё)
b – оставили только файлы xlsx
с – выбрали последний с помощью Table.Max
d – ну и обратились к нему – в данном случае взяли информацию с первого листа (вы же помните, что обращение, на самом деле, может быть любой сложности)

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

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

Поиск ключевых слов в тексте
#АнатомияФункций - Text.Contains, Text.Upper

Всем привет!
Подкинули мне недавно ссылку, я покипел и успокоился, но сегодня всплыло обсуждение Text.Upper, поэтому снова накатило…

Итак, решаем архисложную задачу – есть таблица, в ней текстовый столбец, и есть список ключевых слов – необходимо проверить наличие ключевых слов в тексте и совпавшие вывести через запятую. Дополнительно практически неразрешимой задачу делает необходимость поиска без учёта регистра…

Что бы там ни говорили, я не противник мышкоклаца - я противник неоптимального кода. Поэтому тут не будет двадцатиминутного видео или статьи на несколько страниц, просто решим задачу:

let
    tbl=#table({"Артикул","Описание"},{{1,"деталь для Audi"},{2,"что-то для KIA или ford"},{3,"для BMW (и для audi подойдет)"}}),
    tbl1=#table({"Марка"},{{"AUDI"},{"BMW"},{"KIA"},{"FORD"}}),
    dict = List.Buffer(tbl1[Марка]),
    f=(x)=>Text.Combine(List.Select(dict,(i)=>Text.Contains(Text.Upper(x),i)),", "),
    to = Table.AddColumn(tbl,"New", each f([Описание]))
in
    to

по шагам:
tbl – исходная таблица, искать будем по столбцу «Описание»
tbl1 – список ключевых слов – ну как список – таблица, взятая с листа
dict – первое, что делаем – превращаем таблицу в список, не забываем его забуферить – нам к нему придётся обращаться многократно
f – функция для поиска – принцип простой – проверяем список ключевых слов, выбираем из него встретившиеся и собираем полученные ключевые слова в текст через разделитель. Теперь посмотрим на функцию отбора – здесь у нас Text.Contains, содержащий внутри Text.Upper. Зачем? Да просто Comparer.OrdinalIgnoreCase, который обычно рекомендуют, штука медленная, а переведя текст в верхний (или в нижний) регистр мы сильно упрощаем и ускоряем обработку
to – использовали нашу функцию для добавления столбца

Всё… Пять строчек кода.

На примере с одного известного сайта этот код обошёл по скорости мышкоклацный всего-то в 6 раз (на 100k строк).
При этом ещё 4% производительности можно получить, если написать последний шаг так:

to = Table.FromList(Table.ToList(from,(x)=>x&{f(x{1})}),(x)=>x,Table.ColumnNames(from)&{"New"})

(мы это уже обсуждали тут)

В решении нет ни одной «хитрой» функции или какого-то специального приёма. Тот же Upper (или Lower) абсолютно аналогично используется в SQL или php, например.
Поэтому перед тем, как начинать мучать мышь, предлагаю всё же задуматься – может и правда стоит уже открыть расширенный редактор и что-то там написать самостоятельно. Ну хотя бы, повторить, не подсматривая, решение выше. Немножко практики и руки сами начнут автоматом тянуться к клавиатуре – а там и до поста в танке недалеко…

Такая вот пятничная философия )))

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

Множественная замена в тексте
#АнатомияФункций – приёмы

Всем привет!
Пора закрыть тему замен в тексте. Мы уже приматывали "словарь на записях" для замены значения целиком и для посимвольных замен, но бывают ситуации, когда необходимо осуществить замену внутри текста, причём имеется ряд вариантов. Тут уже не обойтись без Text.Replace и я не знаю более шустрого варианта, чем запихнуть это в List.Accumulate. Поэтому просто разберем пример:

let
    tbl = #table({"текст","число"},{{"мама мыла раму",1},{"отмыла мамочка рамочку",2}}),
    tb2 = #table({"что","на что"},{{"мам","пап"},{"мыла","пил"},{"рам","пив"}}),
    dict = List.Buffer(Table.ToList(tb2,(x)=>x)),
    f = (x)=>List.Accumulate(dict,x,(s,c)=>Text.Replace(s,c{0},c{1})),
    to = Table.TransformColumns(tbl,{"текст",f})
in
    to

tbl – исходная таблица, в которой нужно преобразовать столбец текст
tb2 – таблица для замен
dict – преобразуем таблицу замен в список и не забываем его забуферить
f – функция для преобразования текста – используем List.Accumulate, в качестве первого аргумента используем список замен (список списков, если точнее), второй аргумент – сам текст, ну и функция – просто Text.Replace
to – применяем нашу функцию к столбцу (а можно и к столбцАМ)

Всё… Подключились к таблицам, сделали из одной словарь, сверстали функцию в одну строку и применили её. Никаких лишних телодвижений, индексов и прочего ))) Пользуйтесь!

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

Record.From… - или варианты получения словаря на записях
#АнатомияФункций – приёмы

Всем привет! Сегодня в личке узнал, что джойны работают в разы быстрее словаря на записях. Немножко расстроился, удивился, потом разобрался и по этому поводу пост )))

Итак, напоминаю задачу – из таблицы

#table({"Должность","Оклад","Премия"},{{"Игрок",1000,100},{"Тренер",2000,5000},{"Врач",100,1}})

Нужно получить запись

[Игрок={1000,100},Тренер={2000,5000},Врач={100,1}]

Которую мы в дальнейшем используем для подстановки через Record.Field или Record.FieldOrDefault

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

f=(table)=>List.Accumulate(Table.ToRows(table),[],(s,c)=>Record.AddField(s,c{0},List.Skip(c)))

которую можно немного ускорить:

f=(table)=>List.Accumulate(Table.ToList(from,(x)=>{x{0},List.Skip(x)}),[],(s,c)=>Record.AddField(s,c{0},c{1}))

Оба варианта вполне рабочие, даже шустрые, но на объёмах словаря до 100 позиций. Причина – сам по себе аккумулятор работает со списком последовательно, поэтому никаких параллельных вычислений не допускает и на больших объёмах просто падает (поэтому делать словарь на 5k позиций через него не надо – это и правда в разы медленнее джойнов)

Ок, что есть в альтернативе – пойдём через таблицы (Record.FromTable):

f=(table)=>[nms = Table.ColumnNames(table),
            comb = Table.CombineColumns(table,List.Skip(nms),(x)=>x,"Value"),
            res = Record.FromTable(Table.RenameColumns(comb,{nms{0},"Name"}))][res]

на объемах до 100 – это вообще самый шустрый вариант, к 1000 выравнивается с вариантами ниже, ну и потом потихоньку отстаёт
Другой вариант я уже показывал ранее:

f=(table)=>Record.FromTable(Table.FromList(Table.ToList(table,(x)=>{x{0},List.Skip(x)}),(x)=>x,{"Name","Value"}))

Также крайне шустрый и ощутимо отстаёт от лидеров только на объёмах в 100k и более.

Ну и последняя парочка вариантов – разумеется на списках (Record.FromList). Они самые шустрые на больших объемах (от 10k):

f=(table)=>Record.FromList(Table.ToList(table,(x)=>List.Skip(x)),Table.Column(table,Table.ColumnNames(table){0}))

здесь немножко криво получены имена полей – через обращение к первому столбцу, но поскольку пересборки таблицы не происходит – это всё равно шустро
Но на удивление более быстрым (и чемпионом на объёмах от 1000) стал вариант:

f=(table)=>Record.FromList(Table.ToList(table,(x)=>List.Skip(x)),Table.ToColumns(table){0})

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

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

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