Полное руководство: Извлечение записей PowerQuery Excel

PowerQuery предлагает несколько мощных методов для извлечения конкретных записей из столбца в отдельные столбцы, включая использование функции Text.BetweenDelimiters, разделение по разделителям и создание пользовательских столбцов с формулами извлечения. Наиболее эффективный подход зависит от структуры ваших данных и шаблонов разделителей, при этом функция Text.BetweenDelimiters особенно полезна, когда у вас есть последовательные начальные и конечные разделители, отмечающие конкретную информацию, которую вы хотите извлечь.

Содержание

• Понимание структуры ваших данных
• Метод 1: Использование функции Text.BetweenDelimiters
• Метод 2: Разделение столбца по разделителю
• Метод 3: Пользовательский столбец с функциями позиционирования
• Метод 4: Команды извлечения в Power Query
• Продвинутые методы для сложных сценариев
• Устранение распространенных проблем

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

Перед извлечением данных важно проанализировать структуру вашего столбца “Body”. Исследования показывают, что успешное извлечение зависит от выявления последовательных шаблонов и разделителей в ваших данных.

“Наиболее похожая функция на кнопку “Текст в столбцы” в Microsoft Excel - это разделение столбцов по разделителю. Она позволяет определить конкретный символ и разделить части одного столбца на новые столбцы на его основе.” Источник: PopAutomation

Ищите последовательные шаблоны в ваших данных Exchange. Например:

• Имена могут предшествовать “First Name:” или “FN:”
• Адреса электронной почты обычно содержат символы “@”
• Центры затрат могут быть указаны с помощью определенных кодов или шаблонов

Метод 1: Использование функции Text.BetweenDelimiters

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

Базовый синтаксис:

Text.BetweenDelimiters(text as any, optional startDelimiter as any, optional endDelimiter as any, optional startIndex as any, optional endIndex as any) as any

Пошаговая реализация:

1. Откройте редактор Power Query в Excel
2. Выберите вашу таблицу со столбцом “Body”
3. Перейдите в Добавить столбец → Пользовательский столбец
4. Используйте формулы, подобные приведенным ниже, для каждого поля:

// Извлечение имени
= Text.BetweenDelimiters([Body], "First Name:", "Last Name:")

// Извлечение фамилии  
= Text.BetweenDelimiters([Body], "Last Name:", "Email:")

// Извлечение email
= Text.BetweenDelimiters([Body], "Email:", "Cost Center:")

// Извлечение центра затрат
= Text.BetweenDelimiters([Body], "Cost Center:", "End of Record:")

Обработка крайних случаев:

= if Text.Contains([Body], "First Name:") then 
    Text.BetweenDelimiters([Body], "First Name:", "Last Name:") 
  else null

Как объясняет RADACAD, эта функция идеально подходит для извлечения конкретных частей текстовых значений с помощью разделителей.

Метод 2: Разделение столбца по разделителю

Этот метод полезен, когда ваши данные структурированы с последовательными разделителями.

Пошаговая реализация:

1. Выберите столбец “Body” в редакторе Power Query
2. Перейдите в Преобразование → Разделить столбец → По разделителю
3. Выберите ваш разделитель (например, “:”, “;” или пробел)
4. Выберите Разделить на строки или Разделить на столбцы в зависимости от ваших потребностей

Для извлечения нескольких полей может потребоваться несколько операций разделения:

1. Сначала разделите по основному разделителю (например, “Record:” или “END”)
2. Затем разделите полученные столбцы по более мелким разделителям

Согласно Microsoft Support, вы можете разделить столбец с типом данных текст на два или более столбцов с использованием количества символов или разделителей внутри текстового значения.

Метод 3: Пользовательский столбец с функциями позиционирования

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

Пошаговая реализация:

1. Перейдите в Добавить столбец → Пользовательский столбец
2. Используйте функции, такие как Text.PositionOf и Text.Range:

// Извлечение текста между конкретными позициями
= Text.Range(
    [Body], 
    Text.PositionOf([Body], "First Name:") + 12, // Начать после "First Name:"
    Text.PositionOf([Body], "Last Name:") - Text.PositionOf([Body], "First Name:") - 12
)

// Альтернативный подход с использованием нескольких функций
= if Text.Contains([Body], "First Name:") then
    let
        startPos = Text.PositionOf([Body], "First Name:") + 12,
        endPos = Text.PositionOf([Body], "Last Name:", Occurrence.First),
        result = Text.Range([Body], startPos, endPos - startPos)
    in result
  else null

Обсуждение на Stack Overflow предоставляет подробные примеры использования этих функций для сложных сценариев извлечения.

Метод 4: Команды извлечения в Power Query

Power Query предоставляет встроенные команды извлечения для распространенных сценариев.

Пошаговая реализация:

1. Выберите столбец “Body”
2. Перейдите в Добавить столбец → Извлечь
3. Выберите из этих опций:
   • Текст перед разделителем - Извлекает текст перед конкретным символом
   • Текст после разделителя - Извлекает текст после конкретного символа
   • Текст между разделителями - Извлекает текст между двумя разделителями

Для вашего сценария:

1. Создайте столбец “First Name” с помощью Text Before Delimiter с разделителем “Last Name:”
2. Создайте столбец “Last Name” с помощью Text Between Delimiters с разделителями “Last Name:” и “Email:”
3. Создайте столбец “Email” с помощью Text Between Delimiters с разделителями “Email:” и “Cost Center:”
4. Создайте столбец “Cost Center” с помощью Text After Delimiter с разделителем “Cost Center:”

Как предлагает Excel Campus, вместо создания дублирующихся столбцов, вы можете перейти в “Добавить столбец” → “Извлечь” и выполнить операции с текстом перед, после и между разделителями.

Продвинутые методы для сложных сценариев

Обработка нескольких вхождений

Если ваши данные содержат несколько записей в одной ячейке, вам может потребоваться сначала разделить их на строки:

let
    Source = YourTable,
    SplitRows = Table.ExpandListColumn(
        Table.TransformColumns(Source, {{"Body", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv)}}, "Body"),
        "Body"
    ),
    ExtractFields = Table.AddColumn(SplitRows, "First Name", each Text.BetweenDelimiters([Body], "First Name:", "Last Name:")),
    // Добавьте больше столбцов при необходимости
in ExtractFields

Создание повторно используемых функций

Для повторяющихся задач извлечения вы можете создавать пользовательские функции:

= (text as text, startDelim as text, endDelim as text) as text =>
    if Text.Contains(text, startDelim) and Text.Contains(text, endDelim) then
        Text.BetweenDelimiters(text, startDelim, endDelim)
    else null

Ресурс My Online Training Hub предоставляет информацию о создании повторно используемых функций для извлечения текста.

Обработка несогласованных данных

Для данных с несогласованными шаблонами используйте условную логику:

= if Text.Contains([Body], "First Name:") then
    Text.BetweenDelimiters([Body], "First Name:", "Last Name:")
  else if Text.Contains([Body], "FN:") then
    Text.BetweenDelimiters([Body], "FN:", "LN:")
  else null

Устранение распространенных проблем

Проблема 1: Текст не найден

Проблема: Извлечение возвращает null или пустые значения
Решение: Добавьте обработку ошибок:

= if Text.Contains([Body], "First Name:") then
    Text.BetweenDelimiters([Body], "First Name:", "Last Name:")
  else "Не найдено"

Проблема 2: Несколько записей в одной ячейке

Проблема: Несколько записей объединены в одной ячейке
Решение: Сначала разделите на строки, затем извлеките:

= Table.ExpandListColumn(
    Table.TransformColumns(YourTable, {{"Body", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv)}}, "Body"),
    "Body"
)

Проблема 3: Несогласованные разделители

Проблема: Разделители различаются между записями
Решение: Используйте несколько методов извлечения или создайте комплексную функцию:

= if Text.Contains([Body], "First Name:") then
    Text.BetweenDelimiters([Body], "First Name:", "Last Name:")
  else if Text.Contains([Body], "Name:") then
    Text.BetweenDelimiters([Body], "Name:", "Email:")
  else null

Обсуждения на Reddit показывают практические примеры обработки разных типов разделителей и крайних случаев.

Заключение

Извлечение конкретных записей из столбца в отдельные столбцы с помощью PowerQuery можно выполнить несколькими эффективными методами:

1. Text.BetweenDelimiters - наиболее прямой метод, когда у вас есть последовательные начальные и конечные разделители
2. Разделение столбца по разделителю хорошо работает для данных с четкими разделителями
3. Пользовательские столбцы с функциями позиционирования обеспечивают максимальную гибкость для сложных сценариев
4. Встроенные команды извлечения предоставляют удобный интерфейс для распространенных задач извлечения

Для вашего сценария с данными Exchange я рекомендую начать с метода Text.BetweenDelimiters, так как он специально разработан для извлечения текста между разделителями. Если ваши данные имеют несогласованные шаблоны, объедините его с условной логикой или создайте повторно используемые функции.

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

Источники

1. Microsoft Support - Разделение столбца текста (Power Query)
2. Microsoft Learn - Text.BetweenDelimiters
3. RADACAD - Извлечение частей текстового значения в Power BI с помощью разделителя
4. Excel Campus - Как разделить ячейки и текст в Excel с помощью Power Query
5. Stack Overflow - Power BI Query - Извлечение текста между разделителями в новый столбец
6. My Online Training Hub - Удаление текста между разделителями - Power Query
7. PopAutomation - Методы разделения столбцов в Power Query