Всем привет!
С сегодняшнего дня начинаем новую рубрику #ГостевойТанк - здесь будут публиковаться посты наших коллег и друзей на злободневные темы PQ

Проблема ёжиков и Алён в сортировке текста
#ГостевойТанк - дополнение от @MaximZelensky

Дополню примеры из поста про List.Sort вот таким:

    List.Sort(
        {
          "Алена", 
          "АЛЕНА", 
          "АЛЁНА", 
          "Алёна"
        } 
    )

Обычно заглавные буквы старше, чем строчные, а в алфавите Е идёт перед Ё, поэтому мы ожидаем такой результат сортировки:

    {"АЛЕНА", "АЛЁНА", "Алена",  "Алёна"}

Однако PQ считает по-другому:
{
"АЛЁНА",
"АЛЕНА",
"Алена",
"Алёна"
}
Как видим, первые два слова стоят в неожиданном порядке. Причина простая - составители кодовой таблицы (где символу сопоставлен числовой код) для кириллицы сначала забыли про букву Ё, и затем в целях обратной совместимости (чтобы не порушить уже разработанное с помощью этой таблицы ПО) ее пришлось вставлять на свободные места - она должна была быть явно старше, чем строчная буква, но места между заглавными и строчными в кириллице не осталось. Поэтому заглавную "Ё" впихнули до "А", а строчную "ё" - после "я". Этой истории уже десятки лет и никто ничего исправлять не будет.
В указанном примере такими тонкостями можно пренебречь, однако давайте отсортируем ежей, ужей и арбузы:
= List.Sort( {"Арбуз", "Ёж", "ежиха", "ёжик", "ежище", "ужик", "Уж" })
Результат будет уже не таким приятным:
{
"Ёж",
"Арбуз",
"Уж",
"ежиха"
"ежище"
"ужик",
"ёжик"
}
Попытка выполнить сортировку в правильном порядке на основании числовых кодов символов здесь возможна (кто смелый - попробуйте и поделитесь результатом), но весьма громоздка и, подозреваю, медленна (ведь Ё/ё может встретиться в любом месте строки, значит, сверку нужно будет производить посимвольно).

Отчасти спасение лежит в стандартной функции-компараторе из библиотеки PQ: Comparer.FromCulture. Эта функция тоже имеет два аргумента, но совсем не те, что Value.Compare:

* указание локали, в которой должно производиться сравнение в стандарте .Net Framework (например, "ru-ru")
* логический параметр, позволяющий игнорировать регистр символов при сортировке). По умолчанию он равен false

А результат вызова этой функции - неименованная функция-сравниватель, которая уже принимает два аргумента для сравнения (как Value.Compare или ваша кастомная функция сравнения).
Сортировка списка с такой функцией выглядит вот так:

= List.Sort( {"Арбуз", "Ёж", "ежиха", "ёжик", "ежище", "ужик", "Уж" }, Comparer.FromCulture("ru-ru"))

/*
{
"Арбуз",
"Ёж",
"ёжик"
"ежиха",
"ежище",
"Уж",
"ужик"
}
*/

Обратите внимание:

* в русской локали сортировка идёт по такому принципу: А, а, Б, б, .... , Е, е, Ё, ё, , ... Я, я.
* Почему-то Ё старше, чем Е, но хотя бы младше, чем Д. (предположения и догадки по этому поводу - в комментариях)

@MaximZelensky

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

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

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

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

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

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

@MaximZelensky

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

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

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

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

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

@MaximZelensky

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