Table.FindText – или ищем текст где-то в таблице
#АнатомияФункций - Table.FindText

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

let
    a = #table( {"a","b","c","d","e","f"},
                {{"text",null,null,null,"текст",null},
                {null,"text",null,null,null,null},
                {null,null,"Наименование","Цена","Количество","Сумма"},
                {null,null,"клавиатура",100,2,200},
                {null,null,"мышь",50,10,500}}),
    b = Table.AddIndexColumn(a, "i"),
    c = Table.FindText(b,"Наименование"){0}[i],
    d = Table.Range(a,c),
    e = Table.PromoteHeaders(d),
    f = Table.ColumnNames(e),
    g = List.PositionOf(f,"Наименование"),
    h = List.Range(f,g),
    k = Table.SelectColumns(e,h)
in
    k

Извиняюсь за многабукаф в шагах – специально расписал подробно.
a – сама таблица – видим наличие мусора в первых двух строках, где там именно начинается шапка мы не знаем, знаем только про слово «Наименование»
b – добавляем столбец индекса, он нам нужен для следующего шага
c – собственно тема поста - Table.FindText ищет текст в таблице, причём работает по принципу Text.Contains -–т.е. если не уверены в регистре можно искать и «аименовани» - найдёт ))) Функция возвращает нам таблицу, состоящую из строк, содержащих данный текст. Но нам не нужна только эта строка – нам нужно найти начало шапки. Поэтому мы и добавили столбец индекса, а теперь из полученной таблицы берём первую строку {0}, а из неё номер строки [i]
d - а теперь просто получили таблицу начиная со строки шапки (обращаю внимание – мы обратились к изначальной таблице, ДО добавления столбца индекса – он нам больше не нужен)

ОК, лишние строки пропустили, теперь столбцы:
e – подняли заголовки
f – получили список заголовков
g – нашли «Наименование» уже в именах столбцов
h – пропустили лишние имена столбцов
k – выбрали нужное

Собственно, всё )))
Если задача возникает часто – можно упаковать в функцию:

f=(table,name)=>
    [a = Table.PromoteHeaders(Table.Range(table,Table.FindText(Table.AddIndexColumn(table, "i"),name){0}[i])),
    b = Table.ColumnNames(a),
    c = Table.SelectColumns(a,List.Range(b,List.PositionOf(b,name,Occurrence.First,Text.Contains)))][c]

Вроде вполне симпатично и не то, чтобы сильно громоздко. Пользуйтесь!

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

Table.Group – Часть 3.3 Пятый аргумент + GroupKind.Global
#АнатомияФункций – Table.Group

Всем привет! Меня тут уличили в неполноте раскрытия темы, поэтому вынужден дать развернутый комментарий. Итак, мы уже разбирали пятый аргумент у Table.Group и я тогда написал, что его мы используем в сочетании с GroupKind.Local. Но на самом деле при большом желании можно примотать и для GroupKind.Global.
Разберем пример:

let
    tbl = #table({"Date","Num"},{{#date(2021,1,1),1},{#date(2020,1,1),2},{#date(2021,3,1),3},{#date(2020,1,12),4},{#date(2022,1,1),5}}),
    f=(x,y)=>Value.Compare(Date.Year(x),Date.Year(y)),
    to = Table.Group(tbl,"Date",{"Sum",(t)=>List.Sum(t[Num])},GroupKind.Global,f)
in
    to

tbl - имеем таблицу – дата/значение и хотим сделать группировку по годам.
to – собственно делаем группировку, сразу в один шаг, с использованием функции f
f – пятый аргумент как всегда пишем от двух аргументов. В случае GrouKind.Local мы возвращали 0 или 1 в зависимости от того, надо начинать новую группу или нет. Но в том-то и дело, что там строки анализируются последовательно, а вот в случае глобальной группировки функция должна определить именно порядок переданных ей аргументов. Самое простое – используем Value.Compare - данная функция вернёт -1 0 или 1 когда x меньше, равен или больше y соответственно.

Ну и действительно в таком варианте группировка будет корректной. Другое дело, что даты в столбце Date останутся датами, хотя группировка сделана именно по годам. А главное, что мы уже обсуждали в рамках пользовательской сортировки, Value.Compare – медленная функция. И с ростом объема данных «тупить» будет всё сильнее.
Можно ли это исправить? Ну в целом да – перепишем функцию сравнения:

f=(x,y)=>Date.Year(x)-Date.Year(y)

Вместо сравнения мы рассчитываем разницу и она будет меньше, равна или больше нуля в зависимости от соотношения между x и y. В таком варианте получим прирост по скорости процентов так на 20 – 25 (замерял на таблице в 100k строк) – что, конечно, ощутимо.
И кому-то может показаться, что это вот прям круто – группировать в один шаг, но есть маааленькое НО, из-за которого я данный подход не описывал. А состоит оно вот в чём:

let
    tbl = #table({"Date","Num"},{{#date(2021,1,1),1},{#date(2020,1,1),2},{#date(2021,3,1),3},{#date(2020,1,12),4},{#date(2022,1,1),5}}),
    tr = Table.TransformColumns(tbl,{"Date", Date.Year}),
    to = Table.Group(tr,"Date",{"Sum",(t)=>List.Sum(t[Num])})
in
    to

Стандартный подход в два шага
tr – преобразовали даты в годы
to – сгруппировали по преобразованному столбцу
На той же выборке работает в два раза быстрее.

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

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

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

Table.PositionOf – подстановка по нескольким условиям с диапазонами или когда FillDown бессилен
#АнатомияФункций - Table.PositionOf

Всем привет!
В чате подкинули интересную задачку – поэтому есть что обсудить.
Сначала рассмотрим лайтовый вариант – есть таблица соответствий категорий некоторому признаку, причём подразумевается некоторый диапазон от/до. Ну и есть таблица, в которую по значению признака надо подставить категорию. Рассмотрим классический подход к решению:

let
    dct = #table({"ширина","категория"},{{0,"узко"},{100,"средне"},{250,"широко"}}),
    tbl = #table({"что","ширина"},{{"стул",60},{"кресло",80},{"стол",150},{"диван",220},{"сервант",280}}),
    cmb = tbl&dct,
    srt = Table.Sort(cmb,{"ширина"}),
    fll = Table.FillDown(srt,{"категория"}),
    to = Table.SelectRows(fll,each [что]<>null)
in
    to

dct – таблица с категориями, здесь для простоты оставлена только нижняя граница (т.е. ширина от…)
tbl – таблица, куда подставляем
cmb – объединили обе таблицы
srt – отсортировали по нашему признаку
fll – сделали заполнение вниз
to – оставили только интересующие нас строки.
Вместо ширины бывает производительность, предельное напряжение, дата производства и т.д.
В общем поковыряйте приём – он крайне полезен.

НО вчера было про другое – подстановка нужна по ДВУМ параметрам. И вот тут выясняется, что сортировка нам не поможет (в качестве упражнения пытливые умы могут проверить).
Приходится искать по исходной таблице по двум параметрам. И вот тут хочу предложить воспользоваться Table.PositionOf – она аналогична List.PositionOf , просто про таблицу )))
Поехали:

let
    dct=#table({"длина","толщина","категория"},{{0,0,"фитинг детский"},{0,2,"фитинг"},{0,5,"фитинг усиленный"},{10,0,"патрубок учебный"},{10,2,"патрубок"},{10,8,"патрубок напорный"},{100,0,"труба китайская"},{100,5,"труба"},{100,12,"труба напорная"}}),
    tbl=#table({"что","длина","толщина"},{{"деталь1",7,1.5},{"деталь2",24,2.2},{"деталь3",80,9.9},{"деталь4",120,15},{"деталь5",140,0.8}}),
    lst=List.Buffer(dct[категория]),
    f=(x)=>lst{Table.PositionOf(dct,x,Occurrence.Last,g)},
    g=(x,y)=>x[длина]<=y[длина] and x[толщина]<=y[толщина],
    to = Table.AddColumn(tbl,"категория",f)
in
    to

dct, tbl – словарь и таблица соответственно
lst – сами категории забрали в отдельный список (обращение по индексу в списке быстрее, чем к таблице)
f – функция, обращающаяся к списку категорий по индексу. Индекс находим с помощью Table.PositionOf –берем последнюю строку, соответствующую условию (Occurrence.Last), а соответствие условию проверяем функцией g
g – функция от двух аргументов – в данном случае это записи: x – искомая, y – запись из таблицы (т.е. конкретная строка в таблице для поиска). Само условие прописываем как сопоставление отдельных полей записей (главное не запутаться, что на первом месте - что ищем, а на втором где; ну и не забываем между условиями добавлять оператор and)
to –а дальше просто применили нашу функцию в момент добавления столбца к таблице.

Как-то так. Вроде вполне лаконично, а на небольших словарях – очень даже шустро. Так что пользуйтесь,

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

Text.Split vs Text.BeforeDelimiter или причём тут списки и почему на них опять быстрее
#АнатомияФункций - Text.Split, Text.BeforeDelimiter

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

let
    from = #table({"txt"},{{"раз"},{"раз; два"},{"раз; два; три"}}),
    f=(x)=>Text.BeforeDelimiter(x,"; "),
    to = Table.TransformColumns(from,{"txt",f})
in
    to

Т.е. у нас есть специальная функция – Text.BeforeDelimiter (её мы уже разбирали тут ).
Код вышел простой и лаконичный, обращаем внимание, что не во всех значениях встретился разделитель, и в этом случае функция вернула нам значение целиком.

В общем всё круто, всё работает, но тогда о чём пост? Да вот, собственно, о чём:

let
    from = #table({"txt"},{{"раз"},{"раз; два"},{"раз; два; три"}}),
    f=(x)=>Text.Split(x,"; "){0},
    to = Table.TransformColumns(from,{"txt",f})
in
    to

Здесь мы используем Text.Split – она возвращает список. Соответственно мы вынимаем первый элемент. Идея в том, что при наличии разделителя, первый элемент списка и будет искомым значением; а при отсутствии разделителя функция вернёт список из одного элемента (исходного значения), который мы и получим на выходе.
Зачем такие сложности при наличии специализированной функции? Да просто так быстрее ))) Промеры будут в первом комментарии под постом – с ростом объема анализируемых данных выигрыш по скорости составляет полтора/два раза – неплохо, а?

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

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

Table.RenameColumns – переименовываем столбцы просто, переименовываем пачкой или зачем там ещё и третий аргумент?
#АнатомияФункций - Table.RenameColumns

Всем привет!
Разберём вопрос переименования столбцов – для этого у нас есть Table.RenameColumns:

let
    from = #table({"a","b","c"},{{1,2,3}}),
    to = Table.RenameColumns(from,{"a","A"})
in
    to

Тут всё просто – передали список {что, на что} поменять.

Если переименований нужно несколько – используем список списков:

let
    from = #table({"a","b","c"},{{1,2,3}}),
    to = Table.RenameColumns(from,{{"a","A"},{"b","B"}})
in
    to

Тоже вроде несложно. Определённая сложность возникает, когда таких преобразований много и они у нас содержатся в отдельной таблице:

let
    from = #table({"a","b","c"},{{1,2,3}}),
    tbl = #table({"что","на что"},{{"a","A"},{"b","B"},{"c","C"}}),
    to = Table.RenameColumns(from,Table.ToList(tbl,(x)=>x))
in
    to

В этой ситуации мы просто превратили таблицу в список списков через Table.ToList (да, я помню про Table.ToRows, а вы помните, что она почему-то не столь шустрая).

Ну и наконец, зачем нам третий аргумент? Вариант первый – у вас не просто таблица переименования, а набор вариантов, которые встречаются в разных файлах:

let
    from = #table({"a","b","c"},{{1,2,3}}),
    tbl = #table({"что","на что"},{{"a","A"},{"aa","A"},{"aaa","A"},{"b","B"},{"c","C"}}),
    to = Table.RenameColumns(from,Table.ToRows(tbl),MissingField.Ignore)
in
    to

Здесь мы использовали MissingField.Ignore, чтобы не вылетала ошибка на отсутствующих столбцах.
В более специфичном варианте можно использовать MissingField.UseNull:

let
    from = #table({"a","b","c"},{{1,2,3}}),
    tbl = #table({"что","на что"},{{"a","A"},{"b","B"},{"c","C"},{"d","D"}}),
    to = Table.RenameColumns(from,Table.ToRows(tbl),MissingField.UseNull)
in
    to

Здесь не просто произошло переименование, но и был добавлен недостающий столбец.

Как-то так – третий аргумент необязателен, просто иногда упрощает программирование или жизнь )))

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

СЧЁТЕСЛИ, СУММЕСЛИ и прочие радости на M
#АнатомияФункций – приёмы

Всем привет! За последние пару недель уже несколько раз писал в чат один и тот же по сути код. Запрос обычно звучит «как написать СЧЁТЕСЛИ?» или «как сделать что-то вроде СУММЕСЛИ?». Ответ – «сгруппировать» как правило народ не устраивает, поскольку надо сохранить все строки. Хорошо, дадим «правильный» ответ – «сгруппировать, а потом словарь на записях» ))) Вот этот ответ и разберём:

let
    from = #table({"A","B","C"},{{"x",1,2},{"x",3,4},{"y",5,6},{"y",7,8},{"y",9,10}}),
    f=(x)=>Table.RowCount(x),
    dict = Record.FromTable(Table.RenameColumns(Table.Group(from, "A", {"Value",f}),{"A","Name"})),
    to = Table.AddColumn(from,"New",(r)=>Record.Field(dict,r[A]))
in
    to

по шагам:
from - имеем таблицу, в которой по каждой позиции из столбца А нужно получить сколько раз она встречается в таблице
Чтобы получить агрегацию самое простое таблицу сгруппировать, поэтому
f – пишем функцию, которая заменит СЧЁТЕСЛИ – в данном случае Table.RowCount
и далее
dict – осуществляем группировку, причём агрегированный столбец называем Value, а тот, по которому группировали – Name – нам это нужно, чтобы получить словарь (разбиралось тут)
to – на последнем шаге используем словарь для добавления нового столбца

Думаю, возник вопрос – а зачем я вообще вынес шаг f? Да мне просто лень всё переписывать целиком, у нас же тут есть варианты:

f=(x)=>List.Sum(x[B]) // СУММЕСЛИ по столбцу В
f=(x)=>List.Sum(x[C]) // СУММЕСЛИ по столбцу С
f=(x)=>List.Max(x[C]) // МАКСЕСЛИ по столбцу С
f=(x)=>List.Min(x[B]) // МИНЕСЛИ по столбцу В
f=(x)=>List.Average(x[C]) // СРЗНАЧЕСЛИ по столбцу С

и так далее…

Код выходит вроде несложный, допускает доработку напильником и не только - экспериментируйте! )))

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

Скользящее среднее или причём тут накопленная сумма
#АнатомияФункций – приёмы

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

let
    from = #table({"a"},List.Zip({{1..100000}})),
    lst = List.Buffer(from[a]),
    m = 10,
    n = List.Count(lst),
    gen = List.Generate(()=>[i=0,l={},a=List.Average(l)],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,l=if i<m-1 then {} else List.Range(lst,i-m+1,m),a=List.Average(l)],
                        (x)=>x[a]),
    to = Table.FromColumns(Table.ToColumns(from)&{gen},Table.ColumnNames(from)&{"new"})
in
    to

по шагам:
from – исходная таблица
lst – столбец, по которому считаем среднее (не забыли про буфер)
m – ширина окна усреднения
n – число элементов в списке (засунуть в генератор можно, но так быстрее)
gen – генерация скользящего среднего – в записи [i – счётчик, l – список для усреднения, вычисляемый через List.Range, a – собственно среднее]
to – добавляем новый столбец к имеющейся таблице.

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

let
    from = #table({"a"},List.Zip({{1..100000}})),
    lst = List.Buffer(from[a]),
    m = 10,
    n = List.Count(lst),
    gen = List.Generate(()=>[i=0,s=lst{i}],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,s=x[s]+lst{i}],
                        (x)=>x[s]),
    gen2 = List.Repeat({null},m-1)&{0}&List.RemoveLastN(gen,m),
    zip = List.Transform(List.Zip({gen,gen2}),(x)=>(x{0}-x{1})/m),
    to = Table.FromColumns(Table.ToColumns(from)&{zip},Table.ColumnNames(from)&{"new"})
in
    to

Первые четыре шага такие же
gen – а вот здесь не выпендриваемся и просто рассчитываем накопленную сумму – такой генератор работает существенно быстрее
gen2 – добавляем в только что сгенерированный список null-ы и отрезаем последние m элементов
zip – а теперь суть – в первом генераторе мы хватали диапазон из списка и находили по нему среднее, т.е. находили сумму элементов, а потом делили на их количество, т.е. каждый элемент у нас участвовал в суммировании несколько раз. Вместо этого в данном случае мы просто сдвинули накопленную сумму на m элементов вправо (или вниз – кто как воспринимает одномерные массивы), объединили исходный и полученный списки через List.Zip и нашли разницы соответствующих элементов – это и есть сумма по конкретному диапазону усреднения (М – математика))), осталось только поделить на число элементов и примотать столбец к таблице как и в первом варианте.

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

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

ЧИСТРАБДНИ на М или практическое применение List.Generate
#АнатомияФункций - List.Generate
Всем привет!

В продолжение прошлого поста про генерацию дат давайте обсудим вопрос генерации списка только рабочих дней, ну и соответственно реализации экселевской ЧИСТРАБДНИ (NETWORKDAYS)

Комплексный пример выглядит так:

let
    f=(ot as date,do as date, optional holidays as list) as number=>
            [ a=List.Generate(()=>ot,(x)=>x<=do,(x)=>Date.AddDays(x,1)),
              b=List.Select(a,(x)=>Date.DayOfWeek(x,Day.Monday)<5),
              c=List.RemoveMatchingItems(b,holidays),
              d=List.Count(if holidays=null then b else c)][d],

    hol = {#date(2023,2,24),#date(2023,1,2),#date(2023,1,3),#date(2023,1,4),#date(2023,1,5),#date(2023,1,6),#date(2023,1,7),#date(2023,5,8),#date(2023,2,23),#date(2023,3,8),#date(2023,5,1),#date(2023,5,9),#date(2023,6,12),#date(2023,11,6)},
    from=#table(type table [ot=date,do=date],{{#date(2023,1,1),#date(2023,12,31)},{#date(2023,1,1),#date(2023,3,31)},{#date(2023,4,1),#date(2023,6,30)},{#date(2023,7,1),#date(2023,9,30)},{#date(2023,10,1),#date(2023,12,31)}}),
    to = Table.AddColumn(from,"networkdays",(r)=>f(r[ot],r[do],hol),Int64.Type)
in
    to

Разбираем по шагам:
f – функция, эквивалентная ЧИСТРАБДНИ – о ней чуть ниже
hol – список праздников,
from – исходная таблица
to – результирующая – вроде всё прозрачно.

Теперь сама функция f.
Вводим три аргумента: ot – дата начала, do – дата окончания, holidays – необязательный аргумент, список праздников

Шаги внутри функции:
a – генерация списка дней от даты начала до даты окончания – сделано через AddDays – это эффективнее, чем +#duration(1,0,0,0)
b – выбираем только рабочие (номер дня в неделе меньше 5, считая с понедельника, напоминаю, что нумерация идёт с нуля)
c – удаление из полученного списка праздничных дней – сделано через List.RemoveMatchingItems – это шустрее, чем List.Difference
d – последний шаг, возвращающий число дней – обращаю внимание – идёт проверка на наличие третьего аргумента: если передан список выходных – подсчитываем список из шага c, иначе – подсчитываем список из шага b
В принципе, если вам нужно именно сгенерировать список дат рабочих дней – останавливайтесь на шаге c.

Как-то так. Код достаточно короткий и на мой вкус логичный – пользуйтесь!

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

СЖПРОБЕЛЫ, только круче, или причём тут Text.SplitAny
#АнатомияФункций - Text.SplitAny

Всем привет!
В продолжение сегодняшнего обсуждения в чате решим одну задачку. Итак, как видно из названия поста, нам нужно реализовать функцию СЖПРОБЕЛЫ – мы раньше уже обсуждали очистку текста , но проблема в том, что Text.Trim, Text.TrimStart и Text.TrimEnd не удаляют лишние пробелы в середине текста, а СЖПРОБЕЛЫ – удаляет. Что ж, решим эту проблему:

let
    tbl = #table({"txt"},{{"   мама   мыла     раму"},{"   мама     #(lf)     мыла   #(tab)#(tab)#(lf)  раму"}}),
    to = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.Split(x," "),(x)=>x<>"")," ")}),
    to1 = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.SplitAny(x," #(00A0)#(lf)#(tab)"),(x)=>x<>"")," ")}),
    to2 = Table.TransformColumns(tbl,{"txt",(x)=>Text.Combine(List.Select(Text.SplitAny(x,""),(x)=>x<>"")," ")}),
    f = (txt,optional splitby,optional combby)=>Text.Combine(List.Select(Text.SplitAny(txt,""&(splitby??"")),(x)=>x<>""),combby??" "),
    to3 = Table.TransformColumns(tbl,{"txt",f}),
    to4 = Table.TransformColumns(tbl,{"txt",(i)=>f(i," ")}),
    to5 =Table.TransformColumns(tbl,{"txt",(i)=>f(i,null,"#(lf)")})
in
    to5

По шагам:
tbl – исходная таблица с не очень чистым текстом
to – преобразовываем текстовый столбец. Логика простая – разделяем текст по пробелу (Text.Split), при этом получаем список, два пробела подряд также будут разделены и в список попадёт "" (пустая строка), убираем пустые строки (List.Select) и собираем обратно текст через пробел (Text.Combine). Для первой строки в таблице задача решена, но вторая содержит неразрывные пробелы, табуляции, переносы строк – можно ли избавиться и от них тоже? Можно:
to1 – просто используем Text.SplitAny, которой во втором аргументе передадим помимо пробела ещё и другие разделители
Но сегодня выяснилось, что можно и проще:
to2 – снова используем Text.SplitAny, просто в этот раз вторым аргументом передаём пустую строку – и в этой ситуации она сама прекрасно делит текст по пробельным символам и непечатным символам, что на мой вкус удобно
f – ну и подытожим написанием условно универсальной функции. Обязательный аргумент txt – обрабатываемый текст, необязательные – splitby и combby – разделители по которым надо делить текст и разделитель, через который надо обратно собрать текст соответственно.
to3 – просто используем функцию
to4 – делим только по пробелу
to5 – делим по пробельным и нечитаемым символам, а собираем через разрыв строки (обращаю внимание на синтаксис – второй аргумент передан как null)

Как-то так. Вроде несложно, но пришлось пописать, зато получилась СЖПРОБЕЛЫ с блекджеком и … необязательными аргументами. Пользуйтесь!

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

TableInterpolate – заполняем пропуски, но уже в таблице и пачкой
#АнатомияФункций - CustomFunctions

Всем привет!
В прошлый раз мы написали функцию ListInterpolate, которая позволила заполнить пропуски в списке с помощью прогрессии. Другое дело, что обычно пропуски встречаются в табличных данных (встречались данные со счётчиков электроэнергии, пробег автомобиля, вырабатываемая мощность агрегатов, даже биржевые котировки))), и требуется заполнение нескольких столбцов. По этому поводу напишем TableInterpolate:

(tbl,lst)=>
let
    nms = List.Buffer(Table.ColumnNames(tbl)),
    tr = List.Transform(lst,(x)=>List.PositionOf(nms,x)),
    col=List.Buffer(Table.ToColumns(tbl)),
    f=(x,y)=>List.ReplaceRange(x,y,1,{ListInterpolate(x{y})}),
    acc=List.Accumulate(tr,col,f),
    to = Table.FromColumns(acc,Value.Type(tbl))
in
    to

Функция принимает на вход два аргумента:
tbl – исходная таблица,
lst – список столбцов для преобразования.

Разбираем по шагам:
nms – получили список названий столбцов исходной таблицы,
tr – список названий столбцов для преобразования превращаем в список их позиций в таблице
col – саму таблицу преобразуем в список списков по столбцам
f – ну и напишем функцию для аккумулятора – она заменяет конкретный элемент списка (по индексу) им же, но с применённой ListInterpolate
acc – а теперь собираем все вышенаписанные шаги в аккумуляторе: идем по списку интересующих нас номеров столбцов и применяем к ним интерполятор
to – осталось только собрать таблицу обратно (обратите внимание на последний аргумент – данные о типах столбцов получаем из исходной таблицы, т.е. информация не будет потеряна).

Ну и как это применять:

let
    tbl = #table(type table [a=date,b=number,c=text,d=number,e=text,f=number],
        {{#date(2023,1,1),1,"мама",1,"папа",1},
        {#date(2023,1,2),null,"мыла",null,"пил",null},
        {#date(2023,1,3),null,"раму",3,"пиво",null},
        {#date(2023,1,4),3,"мама",null,"папа",null},
        {#date(2023,1,5),null,"мыла",null,"пил",3},
        {#date(2023,1,6),5,"раму",5,"пиво",5}}),
    lst = Table.ColumnsOfType(tbl,{type number}),
    to = TableInterpolate(tbl,lst)
in 
    to

В данном случае
tbl – исходная таблица
lst – нужные столбцы – здесь я просто взял все числовые; вы же можете написать список руками или, например, использовать List.Select
to – применяем функцию

Как-то так – пара несложных функций и решена вполне себе заковыристая задача. Пользуйтесь!

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