Power Query Excel: извлечь имя, email из столбца Body

В Power Query (Power Query в Excel) самый надёжный способ — сначала разбить текст столбца Body на строки, затем добавить пользовательские столбцы, которые ищут строки с метками (Имя:, Фамилия:, Email:, Центр затрат) и извлекают значение после двоеточия с очисткой и резервными стратегиями (поиск email по символу @ и разбиение полного имени). Ниже — пошаговые методы: быстрый через Column From Examples и устойчивый через готовый M‑код плюс советы по обработке HTML, ошибок и автосвежению.

Содержание

• Введение: проблема и подход
• Подготовка: включение Power Query в Excel
• Метод 1 — Power Query: Column From Examples (быстро и гибко)
• Метод 2 — Power Query M‑код: разбиение по строкам и поиск меток
• Обработка Email и разделение имени/фамилии
• Обработка ошибок и нетипичных форматов
• Экспорт и автоматизация обновлений
• Частые ошибки и советы — FAQ
• Источники
• Заключение

Введение: проблема и подход

Вы импортируете письма из Exchange, в столбце Body лежит много текста — несколько строк, метки и иногда HTML. Разные письма форматированы по‑разному: двоеточия могут сопровождаться пробелами, метки могут быть на русском и/или английском, поля могут отсутствовать. Почему простое Split по “:” и по пробелу не всегда срабатывает? Потому что формат непостоянный: переносы строк, лишние пробелы, HTML‑теги или случаи, когда имя и фамилия идут в одной строке.

Подход: 1) привести Body к списку строк; 2) для каждой строки искать метки (case‑insensitive); 3) извлечь значение после “:”; 4) для Email — иметь запасной план (искать токен с “@”); 5) очистить и привести типы. Популярность запросов по Power Query подтверждается данными Yandex Wordstat — power query. Это полезно для SEO и понимания типичных задач пользователей.

Подготовка: включение Power Query в Excel

1. Убедитесь, что у вас доступен Power Query: в Excel 2016+ это Data → Get Data → Launch Power Query Editor; в старых версиях — надстройка Power Query. (См. данные по использованию: power query excel.)
2. В Query Editor найдите шаг, где есть столбец Body. Приведите его к тексту: Home → Transform → Data Type → Text.
3. Если Body содержит HTML (теги), сначала приведите его к plain text (Column From Examples хорошо помогает с извлечением видимого текста) — либо используйте очистку тегов до парсинга.
4. Рекомендуется работать на копии запроса или добавить шаг фильтрации первых N строк для тестирования (быстрее и безопаснее).

Метод 1 — Power Query: Column From Examples (быстро и гибко)

Когда структура писем непостоянна, Column From Examples часто быстрее: вы показываете Power Query пару примеров желаемого результата, и движок выводит формулу автоматически.

Шаги:

1. В Power Query Editor выберите ваш запрос и колонку Body.
2. Home → Add Column → Column From Examples → From Selection (или From All Columns).
3. В новой колонке вручную введите ожидаемое значение для первых 2–4 строк: например, для колонки “Имя” в первых двух строках введите “Иван”, “Мария”. Power Query предложит трансформацию.
4. Примите предложенную формулу, переименуйте колонку (Имя), повторите для Фамилии, Email и Центра затрат.
5. Проверьте для большего числа строк — иногда нужно добавить примеры для разных форматов.
   Плюсы: быстро, не нужно писать M. Минусы: хрупко при сильно варьирующихся шаблонах.

Метод 2 — Power Query M‑код: разбиение по строкам и поиск меток

Это устойчивый метод, который хорошо масштабируется и контролируется. Идея: разбить Body на список строк, очистить, найти строку с нужной меткой (например, “Имя:”), взять текст после “:” и вернуть в новый столбец. Ниже — готовый M‑скрипт, который вы можете вставить в Advanced Editor (замените шаг Source на ваш текущий исходный шаг, где есть столбец Body):

let
 // Замените Source на ваш шаг импорта (например: Excel.CurrentWorkbook(){[Name="Table1"]}[Content])
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"EnsureText" = Table.TransformColumnTypes(Source, {{"Body", type text}}),

 // Разбиваем Body на строки (нормализуем CRLF -> LF)
 #"ToLines" = Table.AddColumn(#"EnsureText", "Lines", each Text.Split(Text.Replace([Body], "#(cr)#(lf)", "#(lf)"), "#(lf)"), type list),

 // Удаляем пустые строки и обрезаем пробелы
 #"TrimmedLines" = Table.TransformColumns(#"ToLines", {"Lines", each List.Select(List.Transform(_, Text.Trim), (x)=> x <> ""), type list}),

 // Функция: поиск значения по списку ключей (case-insensitive). Ищет строку, начинающуюся с "ключ:".
 FindValue = (lines as list, keys as list) as nullable text =>
 let
 items = List.Transform(lines, each Text.Trim(_)),
 matches = List.Transform(items, (line) => List.AnyTrue(List.Transform(keys, (k) => Text.StartsWith(Text.Lower(line), k & ":")))),
 idx = List.PositionOf(matches, true),
 matchLine = if idx = -1 then null else items{idx},
 value = if matchLine = null then null else Text.Trim(Text.AfterDelimiter(matchLine, ":"))
 in
 value,

 // Функция: нахождение email — ищет любой токен с '@'
 FindEmail = (lines as list) as nullable text =>
 let
 tokens = List.Combine(List.Transform(lines, each Text.SplitAny(_, {" ", ";", ",", ":", "<", ">", "(", ")", "|", "/", "\""}))),
 candidateList = List.Select(List.Transform(tokens, Text.Trim), each Text.Contains(_, "@")),
 candidate = if List.Count(candidateList) > 0 then candidateList{0} else null
 in
 candidate,

 // Добавляем колонки по ключам (с набором ключевых слов для разных языков/вариантов)
 #"AddFirstName" = Table.AddColumn(#"TrimmedLines", "Имя", each FindValue([Lines], {"имя","name"}), type text),
 #"AddLastName" = Table.AddColumn(#"AddFirstName", "Фамилия", each FindValue([Lines], {"фамилия","фам","surname","lastname","last name"}), type text),
 #"AddEmail" = Table.AddColumn(#"AddLastName", "Email", each let v = FindValue([Lines], {"email","e-mail","электронная почта"}) in if v <> null then v else FindEmail([Lines]), type text),
 #"AddCostCenter" = Table.AddColumn(#"AddEmail", "Центр затрат", each FindValue([Lines], {"центр затрат","кц","cost center","costcenter"}), type text),

 // Случай: имя и фамилия могут быть в одной строке ("Имя: Иван Петров")
 #"FindCombinedName" = Table.AddColumn(#"AddCostCenter", "CombinedNameLine", each FindValue([Lines], {"имя фамилия","имя_фамилия","name surname","full name"}), type text),

 // Фолбек: если Имя пусто — взять из CombinedNameLine; если Фамилия пусто — взять последнее слово
 #"FillNameFallback" = Table.AddColumn(#"FindCombinedName", "Имя_final", each if [Имя] = null then (if [CombinedNameLine] <> null then Text.BeforeDelimiter([CombinedNameLine], " ") else null) else [Имя], type text),
 #"FillLastFallback" = Table.AddColumn(#"FillNameFallback", "Фамилия_final", each if [Фамилия] = null then (if [CombinedNameLine] <> null and Text.Contains([CombinedNameLine]," ") then List.Last(Text.Split([CombinedNameLine]," ")) else null) else [Фамилия], type text),

 // Удаляем временные колонки и переименовываем финальные
 #"RemovedTemp" = Table.RemoveColumns(#"FillLastFallback", {"Lines", "CombinedNameLine", "Имя", "Фамилия"}),
 #"RenamedFinal" = Table.RenameColumns(#"RemovedTemp", {{"Имя_final","Имя"}, {"Фамилия_final","Фамилия"}})

in
 #"RenamedFinal"

Пояснения:

• Text.Replace нормализует переносы строк; Text.Split даёт список строк.
• FindValue ищет строки, которые начинаются с ключа + “:” (регистронезависимо).
• FindEmail — запасной план, если email не размечен; ищет первый токен с “@”.
• После выполнения проверьте результаты, удалите или переименуйте временные колонки и приведите типы.

Дополнительно: для числового Центра затрат примените try Number.FromText(…) или Table.TransformColumnTypes, если гарантирован формат числа.

(Если хотите, добавлю вариант, который сначала очищает HTML из Body — скажите, есть ли у вас HTML в теле.)

Обработка Email и разделение имени/фамилии

Email:

• Если есть метка “Email:” — берём значение после “:” и затем очищаем от символов <, >, ), . Пример очистки: Text.Trim(Text.Replace(Text.Replace(candidate, “>”, “”), “<”, “”)).
• Если метки нет — поиск токена с символом “@”: мы уже реализовали это в FindEmail. После нахождения токена дополнительно удалите завершающие точку/запятую.

Имя / Фамилия:

• Часто приходят как отдельные метки — берём напрямую.
• Если имя и фамилия в одной строке (“Иван Петров”), то split по пробелу: первое слово — Имя, последнее слово — Фамилия. Это корректно в большинстве случаев, но не идеально для двойных фамилий и отчеств. Для таких случаев нужно дополнительно правила (например, правило: если >2 слов — считать средние слова как отчество и объединять их с именем или фамилией по требованию).

Пример для разбивки полного имени:

• let parts = Text.Split([FullName], " ")
• Имя = if List.Count(parts) >= 1 then parts{0} else null
• Фамилия = if List.Count(parts) >= 2 then List.Last(parts) else null

Обработка ошибок и нетипичных форматов

• Всегда используйте try … otherwise для конверсий: each try Number.FromText([Центр затрат]) otherwise null.
• Удаляйте или заменяйте непечатаемые символы и неразрывные пробелы (NBSP). Если видите странные пробелы — Replace Values по символу (можно вставить NBSP в поле поиска).
• Если Body — HTML, сначала преобразуйте в текст (Column From Examples или Html.Table в M для продвинутых сценариев).
• Тестируйте на подвыборке писем: сначала обработайте 10–50 строк, скорректируйте ключи и правила, затем применяйте на весь набор.
• Для больших таблиц минимизируйте количество промежуточных шагов и по возможности фильтруйте ненужные строки раньше (производительность).

Экспорт и автоматизация обновлений

• После проверки Close & Load — загрузите результат в лист или Data Model.
• Для автоматического обновления: Data → Queries & Connections → правая кнопка по запросу → Properties → включить Refresh on open и/или задать Refresh every X minutes.
• Если источник — Exchange, подумайте о лимитах и объёмах: лучше сначала фильтровать письма на стороне источника (например, по дате) и затем загружать.

Частые ошибки и советы — FAQ

• Split по “:” даёт много колонок — почему? Потому что “:” встречается в теле более одного раза; более надёжно искать строки, начинающиеся с метки, или разбивать по строкам.
• Column From Examples не находит правило? Добавьте ещё пару примеров, или используйте M‑код для сложных случаев.
• Email содержит обёртки “user@domain” — удаляйте угловые скобки через Text.Replace или используйте token‑поиск по “@”.
• Что если метки на английском и русском? В список ключей в FindValue добавьте варианты (см. M‑скрипт выше).
• Нужен ли regex? Иногда удобен, но функции regex в разных версиях Power Query могут отличаться; предложенный метод без regex более переносим.

Источники

1. Yandex Wordstat — power query
2. Yandex Wordstat — power query excel
3. Yandex Wordstat — извлечение данных excel
4. Yandex Wordstat — разделение столбца power query
5. Yandex Wordstat — парсинг текста excel

Заключение

Коротко: используйте Power Query в Excel, разбейте Body на строки и либо быстро извлекайте поля через Column From Examples, либо применяйте устойчивый M‑скрипт (показан выше) с функциями поиска по меткам и резервным поиском email по “@”. Этот подход даёт воспроизводимый и масштабируемый результат: Имя, Фамилия, Email и Центр затрат окажутся в отдельных колонках и будут корректно обновляться при новой загрузке данных. Если хотите, я адаптирую M‑код под ваш реальный пример Body (вставьте 3–5 реальных строк), и подгоню ключи/правила под ваши особенности формата.