Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

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

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

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


Решение 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 пока очень скудная, а примеров в интернете пока еще слишком мало. Будем надеяться, что ситуация в скором времени изменится в лучшую сторону.

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


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 7 комментариев
  1. Наталья:

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

  2. Карина:

    Добрый день!
    Подскажите, пожалуйста, а можно ли вытащить слово из строки? Например, XÆA-12 - имя сына Илона Маска, вытащить слово "имя"?

  3. Alex:

    Подскажите, пжлст, как через PQ вытянуть из строки дробное число по маске, типа "5 знаков после запятой".
    Например, надо из строки вытащить только 0.12345 или 10.54321 или 567.24689 или 1234.35716
    Это единственный признак, по которому можно отфильтровать.

    • Alex, без обид, но Вы сами смогли бы ответить на свой вопрос хотя бы без применения PQ, не видя примера данных, из которых надо число вытянуть? Я вот не могу и не буду даже пытаться, т.к. не видя откуда чего получать выдумывать ответы неблагодарное дело. Угадать что там в реальности шансов очень мало, а я привык ценить свое время.
      Лучше всего будет создать тему на форуме(PowerQuery, PowerPivot, PowerBI и запросы), приложив пример данных и показав, какой ожидаете результат.

      • Alex:

        Дмитрий, там фильтрация только по признаку "дробное число с 5 знаками после запятой". Все остальное - не имеет значения. Кстати, уже нашел решение - Регулярные выражения в Power Query.

  4. Alexandr:

    Здравствуйте. Можно как нибудь прописать это: {"А".."я"," ","A".."z"}), но только чтобы удалились любые символы кроме цифр?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти