Работа с текстом в Power Query: основные методы и функции

Для кого эта статья:

• аналитики данных и BI-аналитики
• специалисты по обработке текстовых данных
• студенты и начинающие аналитики, стремящиеся освоить Power Query

Обработка текстовых данных — пожалуй, самая распространенная головная боль аналитиков. Неструктурированные строки, непредсказуемые форматы и тысячи исключений превращают простую, на первый взгляд, задачу в многочасовое испытание. Power Query радикально меняет эту ситуацию, предоставляя мощный арсенал инструментов, которые превращают хаос текстовых данных в упорядоченные, готовые к анализу таблицы. От простой замены значений до сложных регулярных выражений — давайте погрузимся в мир текстовых возможностей Power Query и научимся извлекать максимум из ваших данных. 🚀

Вы постоянно сталкиваетесь с неструктурированными данными и теряете часы на их подготовку? Курс «BI-аналитик» с нуля от Skypro научит вас мастерски использовать Power Query для автоматизации обработки текста. Вы освоите не только базовые функции, но и продвинутые методы, которые сэкономят вам десятки часов работы ежемесячно. Станьте экспертом по текстовым трансформациям и выведите свою аналитику на новый уровень!

Обзор текстовых возможностей Power Query

Power Query — это не просто инструмент ETL (Extract, Transform, Load), а полноценная среда разработки для обработки данных, включая мощные механизмы для работы с текстом. В основе этих возможностей лежит язык M, который предлагает обширную библиотеку функций для манипуляции со строками.

Текстовые возможности Power Query можно условно разделить на несколько категорий:

• Базовые трансформации — замена значений, удаление пробелов, изменение регистра
• Структурные преобразования — разделение и объединение текста, извлечение подстрок
• Сложные текстовые операции — работа с регулярными выражениями, форматирование по шаблону
• Интеграция с другими типами данных — преобразование текста в даты, числа и другие типы

Ключевое преимущество Power Query в том, что все текстовые операции не только выполняются с высокой производительностью, но и автоматически применяются к новым данным при обновлении запроса. Это создает полностью автоматизированный конвейер обработки текста. 📊

Антон Козлов, руководитель отдела аналитики

Когда наш отдел получил задачу обработать тысячи неструктурированных отзывов клиентов, я понимал, что Excel формулами здесь не обойтись. В каждом отзыве требовалось выделить тональность, ключевые слова и категорию проблемы. Мы переключились на Power Query, и то, что казалось неподъемной задачей на несколько недель, удалось автоматизировать за три дня. Особенно пригодились функции Text.Contains(), Text.Split() и возможность создания пользовательских функций для классификации текста. Теперь этот процесс полностью автоматизирован — загружаем новые отзывы, нажимаем "Обновить", и получаем готовый структурированный отчет через минуту.

Базовые функции для манипуляции с текстом

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

Эти функции можно применять как через интерфейс Power Query (вкладка "Преобразовать" → группа "Текст"), так и напрямую через редактор расширенного редактора, используя язык M. Второй подход дает больше гибкости и возможностей для комбинирования функций.

Рассмотрим типичную задачу стандартизации адресов электронной почты:

= Table.TransformColumns(
Source,
{{"Email", each 
Text.Lower(
Text.Trim(_)
), 
type text}}
)

Этот код преобразует все email-адреса в нижний регистр и удаляет лишние пробелы, обеспечивая единообразие данных для дальнейшей обработки.

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

= Table.AddColumn(
Source, 
"FileName", 
each 
let 
FullPath = [FilePath],
LastBackslash = Text.PositionOf(FullPath, "\", Occurrence.Last) + 1,
FileWithExt = Text.Middle(FullPath, LastBackslash),
DotPosition = Text.PositionOf(FileWithExt, ".", Occurrence.Last),
NameOnly = Text.Start(FileWithExt, DotPosition)
in
NameOnly
)

Продвинутые методы обработки текстовых данных

Когда базовые функции уже недостаточны, приходит время продвинутых методов. Эти инструменты позволяют решать сложные задачи извлечения, очистки и трансформации данных, с которыми аналитики сталкиваются в реальных проектах. 🧠

1. Регулярные выражения

Power Query поддерживает работу с регулярными выражениями через функции Text.PositionOf и Text.RegexMatch, что открывает практически безграничные возможности для обработки сложноструктурированного текста:

// Извлечение номера телефона в формате +X (XXX) XXX-XX-XX
= Table.AddColumn(
Source, 
"ExtractedPhone", 
each 
let
Pattern = "\\+[0-9] \\([0-9]{3}\\) [0-9]{3}-[0-9]{2}-[0-9]{2}",
Matches = Text.RegexMatch([Description], Pattern),
Result = if Matches then Text.Range([Description], Matches{0}[Offset], Matches{0}[Length]) else null
in
Result
)

2. Пользовательские функции

Создание собственных функций для обработки текста позволяет инкапсулировать сложную логику и многократно использовать её в различных запросах:

// Функция для стандартизации ФИО
(fullName as text) as text =>
let
// Разделяем по пробелам
Parts = Text.Split(Text.Trim(fullName), " "),
// Фильтруем пустые элементы
FilteredParts = List.Select(Parts, each _ <> ""),
// Первая буква каждого слова — заглавная, остальные строчные
FormattedParts = List.Transform(FilteredParts, each 
Text.Upper(Text.Start(_, 1)) & 
Text.Lower(Text.End(_, Text.Length(_) – 1))
),
// Собираем обратно
Result = Text.Combine(FormattedParts, " ")
in
Result

3. Условные преобразования

Часто текстовые данные требуют различной обработки в зависимости от их содержания или формата. Power Query предлагает гибкую систему условных выражений:

= Table.AddColumn(
Source, 
"FormattedValue", 
each 
if Text.Contains([RawValue], "@") then "Email: " & [RawValue]
else if Text.StartsWith([RawValue], "+") then "Phone: " & [RawValue]
else if Text.RegexMatch([RawValue], "^[0-9]{6}$") then "Postal Code: " & [RawValue]
else "Text: " & [RawValue]
)

4. Работа с Unicode и многоязычным текстом

Power Query корректно обрабатывает Unicode-символы, что делает его мощным инструментом для работы с многоязычными данными:

= Table.TransformColumns(
Source,
{{"Description", each 
// Транслитерация кириллицы
Text.Replace(
Text.Replace(
Text.Replace(_, "а", "a"),
"б", "b"
),
// ... остальные замены
"я", "ya"
)
}}
)

Владение этими продвинутыми методами существенно расширяет возможности аналитика, позволяя автоматизировать даже самые сложные сценарии обработки текста.

Елена Соколова, BI-аналитик

Мой самый сложный проект был связан с обработкой клиентских запросов из CRM-системы. Ежедневно поступало более 400 текстовых обращений, которые требовалось классифицировать по 15 категориям и извлечь ключевую информацию для автоматического маршрутизирования. Традиционный подход через формулы Excel был неприменим из-за объема и сложности данных.

Решение нашлось в Power Query. Я создала многоуровневый алгоритм, который сначала нормализовал текст (удаление лишних пробелов, приведение к нижнему регистру), затем искал ключевые маркеры через Text.Contains() для начальной классификации. Для более сложных случаев применяла регулярные выражения, извлекавшие номера заказов, идентификаторы продуктов и контактную информацию.

Самым сложным оказалось создание пользовательской функции для определения тональности обращения на основе словаря эмоционально окрашенных слов. В финальной версии система достигла точности классификации 92%, что позволило сократить время обработки запросов на 83%.

Извлечение и парсинг текста в Power Query

Извлечение структурированных данных из неструктурированного текста — одна из самых востребованных задач в аналитике. Power Query предлагает мощный набор инструментов для парсинга текста различной сложности. 🔍

1. Извлечение данных по позиции

Простейший метод извлечения — использование функций, работающих с позициями символов:

• Text.Start(текст, N) — извлекает N символов с начала строки
• Text.End(текст, N) — извлекает N символов с конца строки
• Text.Middle(текст, позиция, длина) — извлекает подстроку заданной длины, начиная с указанной позиции
• Text.Range(текст, от, длина) — аналог Text.Middle, но со смещением от начала строки

// Извлечение первых 5 символов из SKU продукта
= Table.AddColumn(Source, "ProductPrefix", each Text.Start([ProductSKU], 5))

2. Извлечение по разделителям

Когда данные разделены известными символами, эффективно использовать функцию Text.Split():

// Разделение полного имени на имя и фамилию
= Table.AddColumn(
Source, 
"FirstName", 
each 
let
Parts = Text.Split([FullName], " "),
First = if List.Count(Parts) > 0 then Parts{0} else null
in
First
)

Для более сложных случаев можно использовать функцию Text.SplitAny(), которая разбивает текст по любому из указанных символов.

3. Извлечение с помощью регулярных выражений

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

// Извлечение всех цифровых последовательностей из текста
= Table.AddColumn(
Source, 
"ExtractedNumbers", 
each 
let
Pattern = "[0-9]+",
Text = [Description],
Matches = Text.RegexMatches(Text, Pattern),
ExtractedValues = List.Transform(
Matches, 
each Text.Range(Text, _{0}[Offset], _{0}[Length])
)
in
ExtractedValues
)

4. Извлечение структурированных данных из форматированного текста

Power Query может извлекать данные из структурированных текстовых форматов, таких как JSON, XML или HTML:

// Парсинг JSON-строки
= Table.AddColumn(
Source, 
"ParsedJSON", 
each 
let
JsonText = [JSONData],
JsonObject = Json.Document(JsonText),
UserName = JsonObject[user][name],
UserId = JsonObject[user][id]
in
UserName & " (ID: " & Text.From(UserId) & ")"
)

5. Многоступенчатое извлечение

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

// Извлечение даты из текста вида "Заказ #12345 от 01.02.2025"
= Table.AddColumn(
Source, 
"OrderDate", 
each 
let
// Находим позицию подстроки "от "
FromPos = Text.PositionOf([OrderText], "от ") + 3,
// Извлекаем всё, что после "от "
DateText = Text.Middle([OrderText], FromPos),
// Удаляем возможный текст после даты
CleanDateText = 
if Text.PositionOf(DateText, " ") > 0 then 
Text.Start(DateText, Text.PositionOf(DateText, " ")) 
else 
DateText,
// Преобразуем в дату
Result = Date.FromText(CleanDateText)
in
Result
)

Эффективное извлечение данных из текста — это искусство, требующее понимания как структуры исходных данных, так и возможностей инструментария. Power Query предоставляет все необходимые функции, чтобы превратить неструктурированный текст в ценный аналитический актив.

Практические сценарии работы с текстом при анализе данных

Теория обретает ценность только при применении к реальным задачам. Рассмотрим несколько практических сценариев, демонстрирующих, как текстовые функции Power Query решают типичные проблемы аналитиков. 💼

Сценарий 1: Нормализация адресов

Адреса часто поступают в разных форматах с сокращениями, ошибками и непоследовательной структурой. Power Query может стандартизировать их:

= Table.TransformColumns(
Source,
{{"Address", each 
let
// Нормализация регистра и замена сокращений
Step1 = Text.Proper(Text.Trim(_)),
Step2 = Text.Replace(Step1, " Ул.", " улица"),
Step3 = Text.Replace(Step2, " Пр-т", " проспект"),
Step4 = Text.Replace(Step3, " Д.", " дом"),
// Стандартизация разделителей
Step5 = Text.Replace(Text.Replace(Step4, ",,", ","), " ,", ","),
// Финальная очистка
Result = Text.Trim(Step5)
in
Result
}}
)

Сценарий 2: Анализ SEO-данных

При анализе SEO часто требуется извлекать ключевые слова, домены и параметры URL:

= Table.AddColumn(
Source, 
"Domain", 
each 
let
Url = [ReferralURL],
// Удаляем протокол
NoProtocol = if Text.Contains(Url, "://") 
then Text.AfterDelimiter(Url, "://") 
else Url,
// Извлекаем домен
Domain = if Text.Contains(NoProtocol, "/") 
then Text.BeforeDelimiter(NoProtocol, "/") 
else NoProtocol
in
Domain
)

Сценарий 3: Обработка финансовых данных

Финансовые данные часто поступают в текстовом формате с различными разделителями и валютными знаками:

= Table.TransformColumns(
Source,
{{"Amount", each 
let
// Удаляем валютные символы и пробелы
Step1 = Text.Clean(Text.Trim(_)),
Step2 = Text.Replace(Text.Replace(Text.Replace(Step1, "$", ""), "€", ""), " ", ""),
// Заменяем разделители
Step3 = Text.Replace(Step2, ".", ","),
// Конвертируем в число
Result = Number.FromText(Step3)
in
Result
, type number}}
)

Сценарий 4: Оптимизация категоризации продуктов

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

= Table.AddColumns(
Source,
{
{"MainCategory", each 
if Text.Contains([ProductCategory], " – ") then 
Text.BeforeDelimiter([ProductCategory], " – ") 
else 
[ProductCategory]
},
{"SubCategory", each 
if Text.Contains([ProductCategory], " – ") then 
Text.AfterDelimiter([ProductCategory], " – ") 
else 
null
}
}
)

Сценарий 5: Обработка логов и технических данных

При анализе логов требуется извлекать временные метки, идентификаторы ошибок и другие технические параметры:

= Table.AddColumn(
Source, 
"ErrorCode", 
each 
let
LogEntry = [LogText],
HasError = Text.Contains(LogEntry, "ERROR:"),
ErrorCode = 
if HasError then
let
AfterError = Text.AfterDelimiter(LogEntry, "ERROR:"),
CodePattern = "\\[E[0-9]{4}\\]",
Matches = Text.RegexMatch(AfterError, CodePattern)
in
if Matches then 
Text.Range(
AfterError, 
Matches{0}[Offset] + 1, 
Matches{0}[Length] – 2
) 
else 
"UNKNOWN"
else
null
in
ErrorCode
)

Эти сценарии демонстрируют гибкость и мощь текстовых функций Power Query. С их помощью можно решать практически любые задачи обработки и трансформации текстовых данных, превращая неструктурированную информацию в ценные аналитические активы.

Не уверены, подходит ли вам карьера аналитика данных? Тест на профориентацию от Skypro поможет определить, соответствуют ли ваши навыки и предпочтения требованиям профессии. Пройдите тест и узнайте, насколько вам подойдет работа с Power Query и текстовыми данными. Получите персональные рекомендации по развитию карьеры и необходимым навыкам для успеха в аналитике.

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