Оставить цифры или текст при помощи PowerQuery

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

Т.е. из текста вида "1.1.1 Выручка от реализации собственных товаров и услуг (Экспорт)" надо получить только цифры уровня - "1.1.1"
И что самое главное - сделать это надо не функциями Excel и макросами(все было бы слишком просто, подобное решение я уже выкладывал на сайте - Как оставить в ячейке только цифры или только текст?) - исключительно средствами языка M в PowerQuery. Это я к тому, что формулы DAX тоже нельзя применять. С виду несложно, но есть проблемы. Даже на скрине видно, что уровни состоят из разного кол-ва цифр и плюс к этому после самого уровня может идти и точка, и пробел и запятая и еще много чего. А теоретически - может вообще ничего не идти, сразу буквы. И в довершение - текст может содержать как русские, так и английские буквы, а внутри обозначения уровней могла быть не только точка, но еще и пробелы: "1. 1.1".
Первое, что пришло в голову - самописная функция, которая искала бы любую букву. Т.е. нашли букву, запомнили её позицию в тексте и вытащили текст только до этой позиции. Однако в результате было найдено несколько решений.

Прежде чем читать далее и пробовать применить, необходимо знать азы работы в Power Query, а точнее: как создавать и редактировать запросы и как вызвать расширенный редактор. Или как минимум знать где он находится: переходим в нужный запрос -Главная -Расширенный редактор:

К статье приложен файл со всеми вариантами, но он может показаться сложным для понимания "сходу", т.к. в одном запросе использованы сразу все варианты преобразований:
Скачать пример:

  Удаление цифр или текста в Power Query.xlsx (57,2 КиБ, 1 586 скачиваний)

Решение 1
Друг после поисков в интернете набрел на статью в блоге Кена Пулса: http://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/
Мне описанное там решение показалось несколько запутанным для решения именно нашей задачи, хотя и не лишенным своих плюсов и совершенно точно весьма оригинальным. На этом примере и разберем как применять все остальные и про какие плюсы и минусы речь.
Основной упор там сделан на строку CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)), которая создает список букв и символов на основании их числовых кодов.

Character.FromNumber - создает текстовое представление символа на основании указанного числового кода. Т.е. из числа 46 будет создана точка(.), а из числа 1040 - заглавная русская буква "А", 1072 - строчная русская буква "а"
List.Transform - грубо говоря преобразует полученный массив значений в тип список(это уже нюансы и можно на этом не акцентировать внимание)

Если поставить эту строку в самое начало кода в расширенном редакторе, то далее можно использовать при вставке пользовательского столбца.
Теперь по шагам.
Предположим, что изначально был создан запрос из Таблицы1 на листе(пример таблицы приведен выше). Просто подключились к таблице и все. Код в расширенном редакторе будет выглядеть примерно так:

let
    //подключаемся к таблице Таблица1
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Данные", type text}})
 in
    #"Измененный тип"

Теперь необходимо здесь добавить в самом начале строку создания списка символов:

let
    //формируем массив символов для замены
    CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
    //подключаемся к таблице Таблица1
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Данные", type text}})
 in
    #"Измененный тип"

И далее уже в обычном редакторе идем на вкладку Добавить столбец -Пользовательский столбец. В окне задаем имя столбца и вписываем следующее:
=Text.Remove([Данные],CharsToRemove)

• [Данные] - это столбец с текстом, из которого необходимо убрать все лишние символы(они перечислены как раз в строке CharsToRemove)
• Text.Remove - текстовая функция языка M, удаляющая из указанного текста([Данные]) все символы, перечисленные в списке(CharsToRemove)

В принципе это практически решает задачу, если список задать следующими символами:
CharsToRemove = List.Transform({0..45,47,58..2000}, each Character.FromNumber(_)),

0..45 - двойной точкой обозначается диапазон последовательности чисел. В данном случае это все числа от нуля до 45. Такая запись значительно сокращает код и экономит время и силы. Если бы не подобная возможность, нам пришлось бы перечислять каждый символ: 0,1,2,3,4,5,6,7,8,9 и т.д. до 45.

Почему я вообще использовал 0..45, в то время как ранее указывал, что русские буквы вообще начинаются с номера 1040? Потому что у нас так же встречаются в тексте и тире, и запятые и еще мало ли какие лишние символы. В то время как в уровне нам нужно оставить лишь точки и цифры. Указав диапазон символов от 0 до 45 я исключил большую часть наиболее часто встречающихся символов(кавычки, знак доллара($), процент(%) и т.д.). Символы 48-57 - это числа от 0 до 9, поэтому их мы аккуратно обходим в перечислении. А далее я применил просто максимальный диапазон символов, чтобы захватить буквы и английского и русского алфавита: 58..2000.
Теперь сразу стал очевидным плюс данного подхода - нам не надо руками забивать все символы, достаточно забить диапазон кодов. Но он же и главный минус, как ни странно. Вот я знаю, что код 1040 - это буква "а" русского алфавита. А вы? Полагаю, что далеко не все знают коды символов и многие даже не догадываются где их посмотреть(сразу скажу, что функция КОДСИМВ в Excel возвращает не те коды).
К тому же такой вариант удалит все перечисленные символы. С одной стороны это отлично, но с другой...Вдруг в тексте после обозначения уровня так же встретится цифра? Например "2.2.2 Премии производственному персоналу (по итогам 3-х месяцев)". В результате получим некорректный уровень 2.2.23, т.к. у нас подхватится цифра 3 из описания.
Поэтому для уверенного решения задачи по корректному отбору именно уровней этот метод не всегда подходит. Равно как и его модификации, созданные мной в ходе решения задачи. Уверен, что кому-то варианты ниже придутся более по душе, т.к. они чуть проще в реализации и на мой взгляд более нагляды. К примеру, если сложно искать нужные коды, но мы знаем, что нам надо удалить весь текст(рус. и англ. буквы и пробелы)? Все просто, берем созданный выше запрос и меняем в нем строку
CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
на такую:
CharsToRemove = ({"А".."я"," ","A".."z"}),
Т.е. просто перечисляем первую и последнюю буквы алфавитов(обращаю внимание - начинать надо с заглавной буквы, а последней ставить строчную, чтобы захватить буквы и нижнего и верхнего региста). Плюс я добавил пробел. Если надо исключить еще какой-либо символ - просто добавляем его в кавычках через запятую. Например, чтобы убрать скобки:
CharsToRemove = ({"А".."я"," ","A".."z", "(", ")"}),
Для тех, кто более тесно работает в Power Query станет очевидным, что можно вообще убрать теперь эту строку и напрямую при создании пользовательского столбца указывать диапазоны символов:
=Text.Remove([Данные],{"А".."я"," ","A".."z"})

Это избавляет от необходимости лезть в Расширенный редактор, что делает такое решение вдвойне удобнее для "неиспорченного кодингом" пользователя. Просто подключились к таблице, перешли на вкладку Добавить столбец -Пользовательский столбец. Вставили туда текст выше и при необходимости лишь изменили имя столбца(вместо [Данные] указать имя нужного столбца).
Точно так же можно избавиться и от чисел в тексте:
=Text.Remove([Данные],{"0".."9","."})
я специально добавил еще точку, чтобы показать, как добавить еще какие-либо символы вместе с цифрами.
И опять следует учитывать, что в данном случае мы удалим цифры во всем тексте, даже если они там совсем не лишние. Т.е. отделить текст от уровней так тоже не получится. Но тем не менее у всех этих решений есть масса применений в различных ситуациях.

Решение 2
Это решение так же предложил друг в результате поиска оптимального решения. Оно основано на возможностях текстовой функции языка M - Text.SplitAny.
Text.SplitAny - данная функция разделяет текст на части(расширенный аналог Текст по столбцам в Excel) если встречает хоть один из перечисленных далее символов и формирует массив из разбитых блоков:
Text.SplitAny([Данные],"абвгдеёжзийклмнопрстуфхцчшщъыьэюя-,")

• [Данные] - это столбец с текстом, из которого необходимо получить уровень.
• "абвгдеёжзийклмнопрстуфхцчшщъыьэюя-," - список символов, по которым надо разделить текст.

Чтобы применить это решение надо подключиться к таблице, перейти на вкладку Добавить столбец -Пользовательский столбец. Вставить туда следующий текст:
=Text.SplitAny(Text.Lower([Данные]),"абвгдеёжзийклмнопрстуфхцчшщъыьэюя-,"){0}
{0} - это указатель конкретный фрагмент разбитого блока. В нашем случае нам нужен самый первый - поэтому указываем {0}, т.к. отсчет начинается с нуля, а первым блоком в разделении будет все до первой буквы/символа из списка символов("абвгдеёжзийклмнопрстуфхцчшщъыьэюя-,").
Т.к. здесь нет цифр, точки и пробела - то первым блоком всегда будет именно уровень. Все выглядит неплохо и полностью решает задачу, за исключением пары моментов. Да, мы не получим неверный результат, даже если внутри текста будет лишняя цифра: "2.2.2 Премии производственному персоналу (по итогам 3-х месяцев)". В результате получим корректный уровень 2.2.2 . Однако строчные и заглавные буквы в PowerQuery различаются, а у нас как раз могут быть буквы в разном регистре. Поэтому нам необходимо добавить к этому списку еще и дублирующий список с заглавными буквами: "абвгдеёжзийклмнопрстуфхцчшщъыьэюя-,АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ". Мало того, что это раздувает текст, так это еще и не всегда просто - забивать последовательно каждую букву алфавита. Плюс надо учесть еще и английский алфавит - случаи-то разные: "абвгдеёжзийклмнопрстуфхцчшщъыьэюяАБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ-,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ".
А применить здесь фокус с диапазонами не получится("А..я"), т.к. диапазоны нельзя перечислять напрямую в текстовой строке. А второй аргумент функции SplitAny именно текст.
Чтобы не дублировать буквы в двух регистрах можно использовать для поиска только нижний регистр, добавив еще одну функцию: Text.Lower([Данные]). Данная функция как раз преобразует текст в нижний регистр. Таким образом получим:
=Text.SplitAny(Text.Lower([Данные]),"абвгдеёжзийклмнопрстуфхцчшщъыьэюя-,"){0}
Вполне приемлемо и отвечает требованиям. Но мне лично не нравится именно необходимость забивать каждую букву отдельно. Плюс, нельзя получить текст именно после уровней - т.е. до уровня разделить получится, а после мы получим кучу разбитых фрагментов на каждую букву в перечислении.
Если скачать пример, то там наглядно видно, что получается и можно поэкспериментировать, изменяя число внутри фигурных скобок.
Я же хотел универсальную функцию, которая поделит текст на две части, найдя хоть один символ из списка. Чтобы можно было решить еще одну классическую задачу: разделение английского и русского текста в артикулах, вроде такого: "12234-RT BRAUN Кофемолка". Split разорвет этот текст на несколько кусков и собрать их будет непросто.

Решение 3
Это решение, которое пришло ко мне в голову изначально - найти любой из указанных символов и отобрать текст до этого символа. Изначально мысли были в сторону цикла, но после недолгого изучения функций M нашел нужное. Все решается при помощи двух простых функций:
Text.Middle([Данные], 0, Text.PositionOfAny([Данные],{"А".."я","A".."z"}))
этот текст также достаточно вставить в окно добавления пользовательского столбца. Заходить в расширенный редактор необходимости нет.
Теперь подробнее про функции:

• [Данные] - это столбец с текстом, из которого необходимо получить уровень. Все как и в функциях выше.
• Text.PositionOfAny - находит числовую позицию любого символа из перечисленных. Т.е. она просматривает каждый символ текста в ячейке столбца [Данные] и если он совпадает хоть с одним из перечисленных в фигурных скобках({"А".."я","A".."z"}) - то возвращает номер позиции этого символа в тексте.
  При этом список символов можно задавать диапазонами: "А".."я" (как это работает я уже описывал в Решении 1). А это означает, что мы можем легко указать буквы русского и английского алфавита без необходимости вводить их одну за другой и добавить какие-то другие символы(через запятую).
• Text.Middle - эта функция берет указанный текст([Данные]) и отбирает из него часть, начиная с указанной позиции и с указанным количеством символов(аналог ПСТР в Excel). Иными словами, если взять текст "привет" и применить к нему функцию таким образом - Text.Middle("привет", 0, 3), то получим текст "при"(отсчет начальных символов начинается с нуля). А если записать так - Text.Middle("привет", 1, 4), то получим "риве".

Таким образом для текста "1.1 Выручка от основной деятельности" это будет выглядеть так:
Text.Middle([Данные], 0, Text.PositionOfAny([Данные],{"А".."я","A".."z"})) =>
Text.Middle("1.1 Выручка от основной деятельности", 0, Text.PositionOfAny("1.1 Выручка от основной деятельности",{"А".."я","A".."z"}))
первой будет найдена буква "В"
Text.Middle("1.1 Выручка от основной деятельности", 0, Text.PositionOfAny("1.1 Выручка от основной деятельности") =>
и как результат возвращена её позиция
Text.Middle("1.1 Выручка от основной деятельности", 0, 6) (т.е. отсчет идет с нуля)
далее будет взято 6 символов от начала текста(с первого символа)
1.1

Но здесь тоже не обошлось без нюансов. Если указать только буквы, то может остаться много "мусора" - лишних символов, которые могут встречаться(скобки, запятые, тире и пр.). Поэтому я добавил функцию удаления лишних символов, но уже после того, как отделили уровень:
=Text.Remove(Text.Middle([Данные], 0, Text.PositionOfAny([Данные],{"А".."я","A".."z"})),{" ","-","(",","}))
Text.Remove - текстовая функция языка M, удаляющая из указанного текста([Данные]) все символы, перечисленные в списке({" ","-","(",","}). Т.е. мы сначала отбираем уровень, а потом очищаем его от лишних символов. Главное не перестараться и не удалить то, что действительно нужно(точки, например).
При этом мы можем сократить список и оставить лишь русские буквы и тогда текст будет поделен на две части: сначала текст до первой русской буквы, а далее - весь текст после. Или тоже самое, но с английскими буквами. Чтобы получить текст, начиная с найденного символа и до конца текста функцию необходимо записать так:
Text.Middle([Данные], Text.PositionOfAny([Данные],{"А".."я","A".."z"}),9999))
все то же самое, но теперь мы сначала ищем позицию любого из указанных символов, а последним аргументом указываем заведомо большее количество символов - 9999, что заставит функцию Text.Middle отобрать весь текст до конца.

Как видно, Power Query предоставляет куда больше возможностей, чем можно подумать на первый взгляд. И при этом нет необходимости изучать все премудрости программирования - достаточно уметь комбинировать наборы функций. Единственно, справка по функциям языка M пока очень скудная, а примеров в интернете пока еще слишком мало. Будем надеяться, что ситуация в скором времени изменится в лучшую сторону.

Так же см.:
Как оставить в ячейке только цифры или только текст?
Работа с текстом