Power Query: найти дубликаты Email, если в одной строке несколько адресов.

От /

Есть отчет из 1С с несколькими десятками тысяч строк и столбцами: Клиент — Регион — E-mail — Телефон и т.д. Помимо свободных комментариев в кириллице, кавычек <>, [ ], запятых, лишних пробелов в ячейке E-mail, там есть вторые, третьи и даже пятые E-mail клиента, которые нужно сохранить, а затем проверить на дубли и выделить уникальные.

Начало задачи — очистка текстовой ячейки с e-mail’ами и свободными комментариями:

  • Убрать всё лишнее (кириллицу, знаки, пробелы),
  • Оставить только e-mail’ы,
  • Если в ячейке несколько e-mail’ов, то:
    • первый оставить в текущей ячейке,
    • остальные перенести вправо (в соседние ячейку).

Решение — здесь.

Продолжение. Требуется найти дубликаты Email, если в одной строке несколько ячеек с Email-адресами

Предположим, в текстовом поле, которое мы разделили на предыдущем шаге, было не более 5 E-mail для одной строки, или для одного клиента. Единственное, что я добавил в полученную на предыдущем шаге таблицу — это служебное поле ID, в котором присвоил порядковый номер строки исходной таблицы. Просто =Строка(). Чтобы после обработки можно было свериться с исходником и убедиться в корректной обработке данных.

Таблица из начального вида:

Организация, Партнер.Код, ИНН, Город, E-mail с мусором, Телефон, МОП

 превратилась в такую:

Организация, ID, Партнер.Код, ИНН, Город, E-mail1, E-mail2, E-mail3, E-mail4, E-mail5, Телефон, МОП

В новой таблице — только очищенные E-mail. Если E-mail2 — E-mail5 у клиента нет — там, соответственно, пусто. Бывает, что и E-mail1 нет, но есть какие-то другие контакты.

Мы точно знаем, что в таблице было и осталось много дублей. 

Задача — выделить первые, уникальные вхождения E-mail, и отдельно отметить дубли.  

Данные переводим в умную таблицу (Ctrl+T) с именем Таблица1. Имя можно проверить и изменить в меню Конструктор таблиц, в этом случае имя также нужно изменить и в коде.

Далее: меню Данные → Получить данные → Из других источников → Пустой запрос 

Откроется редактор Power Query. Меню Главная → Расширенный редактор → вставить М-код ↓

				
					let
    // 1. Получение таблицы из Excel (название таблицы: Таблица1)
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

    // 2. Перечень столбцов, которые НЕ нужно разворачивать
    НеРазворачиваемыеСтолбцы = {"ID", "Организация", "Партнер.Код", "ИНН", "Город", "Телефон", "МОП"},

    // 3. Разворачиваем только столбцы E-mail1..5
    РазвёрнутыеEmail = Table.UnpivotOtherColumns(Источник, НеРазворачиваемыеСтолбцы, "Позиция", "Email"),

    // 4. Добавляем индекс для отслеживания порядка
    СИндексом = Table.AddIndexColumn(РазвёрнутыеEmail, "Индекс", 1, 1, Int64.Type),

    // 5. Группируем по Email и добавляем индекс вхождения (ПорядокПовторения)
    Группировка = Table.Group(СИндексом, {"Email"}, {
        {"Группированные", each Table.AddIndexColumn(_, "ПорядокПовторения", 1, 1), type table}
    }),

    // 6. Разворачиваем обратно (со всеми колонками)
    Объединено = Table.ExpandTableColumn(Группировка, "Группированные", {"ID", "Организация", "Партнер.Код", "ИНН", "Город", "Позиция", "Телефон", "МОП", "Индекс", "ПорядокПовторения"}),

    // 7. Добавляем пометку "Дубликат"
    СДубликатами = Table.AddColumn(Объединено, "Статус", each if [ПорядокПовторения] > 1 then "Дубликат" else null),

    // 8. Удаляем индекс, если он не нужен
    Результат = Table.SelectColumns(СДубликатами, {"ID", "Организация", "Партнер.Код", "ИНН", "Город", "Email", "Позиция", "Телефон", "МОП", "Статус"})
in
    Результат

В результате мы:

  • Сохранили все контактные данные (Организация, ИНН, Город и т. д.);
  • Развернули только столбцы E-mail1–E-mail5 в один столбец;
  • Отметили второе и последующие вхождения E-mail как «Дубликат«;
  • Оставили результат в виде «одна строка = один E-mail», с соответствующим клиентом и его деталями.
При желании можно остановиться на предпоследнем шаге СДубликатами, чтобы увидеть индекс строки-дубля, соответствующий положению в исходной таблице Excel.
 

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

  • У некоторых строк все 5 E-mail были пустыми.
  • Power Query не разворачивает их, потому что нет значений для создания новой строки.

Чтобы исправить, можно Заменить пустые ячейки на null (или текст вроде «Пусто») до разворота

В шаге перед Unpivot добавь такой шаг:

				
					ЗаменитьПустыеEmail = Table.ReplaceValue(
    Источник,
    "",
    null,
    Replacer.ReplaceValue,
    {"E-mail1", "E-mail2", "E-mail3", "E-mail4", "E-mail5"}
)

Но мы не ищем легких путей

Нам необходимо сохранить в оригинале все строки, в которых вообще не было ни одного E-mail.

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

При этом, если в одной строке 2 или более E-mail, он делает ВТОРОЙ ИЛИ БОЛЕЕ дубль строки под тем же оригинальным задвоенным номером строки, при этом в поле E-mail отображается второй и т.д. E-mail. 

				
					let
    // 1. Источник из Excel (убедитесь, что таблица называется "Таблица1")
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

    // 2. Заменить пустые строки ("") в e-mail полях на null
    ЗаменитьПустыеEmail = Table.ReplaceValue(
        Источник,
        "",
        null,
        Replacer.ReplaceValue,
        {"E-mail1", "E-mail2", "E-mail3", "E-mail4", "E-mail5"}
    ),

    // 3. Разворачиваем e-mail столбцы в строки (Unpivot через OtherColumns)
РазвернутыеEmail = Table.UnpivotOtherColumns(
    ЗаменитьПустыеEmail,
    {"ID", "Организация", "Партнер.Код", "ИНН", "Город", "Телефон", "МОП"},
    "Позиция",
    "Email"
),

    // 4. Выбираем строки, где ВСЕ e-mail столбцы пустые
    ТолькоПустые = Table.SelectRows(ЗаменитьПустыеEmail, each
        List.NonNullCount(Record.ToList(Record.SelectFields(_, {"E-mail1", "E-mail2", "E-mail3", "E-mail4", "E-mail5"}))) = 0
    ),

    // 5. Добавляем в эти строки пустые поля "Позиция" и "Email"
    ТолькоПустыеCПозиция = Table.AddColumn(ТолькоПустые, "Позиция", each null),
    ТолькоПустыеCEmail = Table.AddColumn(ТолькоПустыеCПозиция, "Email", each null),

    // 6. Объединяем обычные строки и строки с пустыми e-mail'ами
    ОбъединеноСоПустыми = Table.Combine({РазвернутыеEmail, ТолькоПустыеCEmail}),

    // 7. Добавляем индекс (порядок e-mail’ов)
    СИндексом = Table.AddIndexColumn(ОбъединеноСоПустыми, "Индекс", 1, 1, Int64.Type),

    // 8. Группировка по Email, чтобы посчитать вхождения и добавить "ПорядокПовторения"
    Группировка = Table.Group(СИндексом, {"Email"}, {
        {"Группированные", each Table.AddIndexColumn(_, "ПорядокПовторения", 1, 1), type table}
    }),

    // 9. Разворачиваем обратно все нужные поля, кроме повторяющегося "Email"
    Объединено = Table.ExpandTableColumn(
        Группировка,
        "Группированные",
        {"ID", "Организация", "Партнер.Код", "ИНН", "Город", "Позиция", "Телефон", "МОП", "Индекс", "ПорядокПовторения"}
    ),

    // 10. Добавляем колонку "Статус" — метка "Дубликат" для повторений
    СДубликатами = Table.AddColumn(Объединено, "Статус", each if [ПорядокПовторения] > 1 then "Дубликат" else null),

    // 11. Окончательная таблица с нужными полями
    Результат = Table.SelectColumns(СДубликатами, {
        "ID", "Организация", "Партнер.Код", "ИНН", "Город", "Email", "Позиция", "Телефон", "МОП", "Статус", "Индекс"
    })
in
    Результат
Telegram
VK
Email
Post Views: 52
Прокрутить вверх