Как объединить строки в Power Query: подробное руководство
Пройдите тест, узнайте какой профессии подходите
Для кого эта статья:
- аналитики данных и BI-специалисты
- пользователи Power Query, желающие улучшить навыки объединения строк
новички в сфере анализа данных, интересующиеся эффективными методами работы с данными
Объединение строк в Power Query — это задача, с которой сталкивается практически каждый аналитик. Когда у вас есть фамилия в одном столбце, имя в другом, а вам нужно получить полное имя в третьем — простая на первый взгляд операция может стать головной болью. Особенно когда речь идет о тысячах строк или сложных шаблонах объединения. В этом руководстве я покажу все методы объединения строк в Power Query — от базовых до продвинутых, с реальными примерами и готовыми решениями типичных проблем. 📊
Если вы хотите не просто научиться объединять строки, а освоить комплексный подход к анализу данных, обратите внимание на Курс «BI-аналитик» с нуля от Skypro. На курсе вы не только изучите все возможности Power Query, но и освоите построение аналитических дашбордов, моделирование данных и создание эффективных визуализаций — всё, что нужно для превращения сырых данных в ценные бизнес-инсайты.
Основные методы объединения строк в Power Query
Power Query предлагает несколько способов объединения текстовых данных, каждый из которых имеет свои преимущества в зависимости от конкретной задачи. Правильный выбор метода может значительно повысить производительность и читаемость вашего кода.
Существует четыре основных подхода к объединению строк:
- Использование функции Text.Combine — идеально для объединения строк с одинаковым разделителем
- Применение оператора & — самый простой способ для быстрого объединения нескольких значений
- Слияние столбцов через интерфейс Power Query — для пользователей, предпочитающих графический интерфейс
- Пользовательские функции — для сложных сценариев с условным форматированием
Вот сравнительная таблица этих методов:
Метод | Преимущества | Недостатки | Идеален для |
---|---|---|---|
Text.Combine | Гибкие параметры разделителя, работа с массивами | Более сложный синтаксис | Массовой обработки данных с консистентным форматированием |
Оператор & | Простота использования, понятный синтаксис | Отсутствие встроенных разделителей | Быстрых операций с небольшим числом элементов |
Слияние столбцов через UI | Не требует знания кода | Ограниченная гибкость | Новичков и несложных задач |
Пользовательские функции | Максимальная гибкость и переиспользуемость | Требует навыков программирования | Сложных сценариев с множеством условий |
Антон Петров, BI-аналитик
Однажды наш отдел получил задачу объединить данные о клиентах из трех разных источников. Имена, адреса и контактные данные были разбросаны по разным столбцам и требовали стандартизации. Пытаясь сделать это вручную в Excel, я потратил почти целый день и все равно получил множество ошибок. Тогда я вспомнил о Power Query. Используя Text.Combine и оператор &, я автоматизировал процесс и сократил время обработки с 8 часов до 15 минут! Более того, когда на следующий месяц поступил новый набор данных, мне потребовалось всего нажать кнопку "Обновить", и все слилось автоматически. Это был переломный момент, когда я понял истинную силу Power Query для задач объединения данных.

Объединение текстовых данных функцией Text.Combine
Функция Text.Combine — это мощный инструмент, который специально создан для объединения текстовых значений. Она особенно полезна, когда нужно объединить несколько строк с одинаковым разделителем между всеми элементами.
Базовый синтаксис функции выглядит так:
Text.Combine(list as list, delimiter as text) as text
Где:
- list — список текстовых значений, которые нужно объединить
- delimiter — текст, который будет вставлен между каждой парой элементов
Давайте рассмотрим простой пример. Предположим, у нас есть таблица с информацией о клиентах, где имя, отчество и фамилия находятся в отдельных столбцах, а нам нужно получить полное имя:
= Table.AddColumn(
#"Предыдущий шаг",
"Полное имя",
each Text.Combine({[Фамилия], [Имя], [Отчество]}, " ")
)
В этом примере мы:
- Добавляем новый столбец "Полное имя" к таблице
- Для каждой строки объединяем значения из столбцов "Фамилия", "Имя" и "Отчество"
- Используем пробел в качестве разделителя между элементами
Text.Combine особенно эффективен при работе с динамическим количеством элементов. Например, если вам нужно объединить все непустые элементы из набора столбцов:
= Table.AddColumn(
#"Предыдущий шаг",
"Адрес",
each Text.Combine(
List.SelectMany(
{[Улица], [Дом], [Корпус], [Квартира]},
each if _ = null or _ = "" then {} else {_}
),
", "
)
)
В этом более сложном примере мы сначала фильтруем пустые значения и только затем объединяем непустые элементы с запятой в качестве разделителя. 🚀
Text.Combine также поддерживает вложенные списки, что делает его идеальным для объединения данных из связанных таблиц:
= Table.AddColumn(
Клиенты,
"Все телефоны",
each Text.Combine(
Table.SelectRows(Телефоны, each [КлиентID] = [ID])[Номер],
", "
)
)
Соединение строк с помощью оператора & в Power Query
Оператор "&" — самый интуитивный способ объединения строк в Power Query, знакомый всем, кто работал с Excel. Он отличается простотой использования и наглядностью, что делает его незаменимым для быстрых операций.
Базовый синтаксис предельно прост:
значение1 & значение2 & значение3 ...
Главное преимущество оператора "&" — последовательное объединение элементов в том порядке, в котором они указаны. Это особенно удобно, когда нужно вставить статический текст между динамическими значениями:
= Table.AddColumn(
#"Предыдущий шаг",
"Контактная информация",
each "Клиент: " & [Имя] & ", тел.: " & [Телефон] & ", email: " & [Email]
)
Оператор "&" автоматически преобразует числовые и логические значения в текст, что избавляет от необходимости явного преобразования типов. Однако будьте осторожны с null-значениями — оператор "&" преобразует их в строку "null", а не в пустую строку:
// Результат: "АннаnullИванова"
"Анна" & null & "Иванова"
Для обработки null-значений лучше использовать функцию условного ветвления:
= Table.AddColumn(
#"Предыдущий шаг",
"Полное имя",
each [Фамилия] &
(if [Отчество] = null then " " else " " & [Отчество] & " ") &
[Имя]
)
Сравнение операторов объединения строк в разных языках и системах:
Система | Оператор | Пример | Особенности | ||
---|---|---|---|---|---|
Power Query (M) | & | "a" & "b" → "ab" | Преобразует null в строку "null" | ||
SQL | + или | 'a' + 'b' → 'ab' | NULL + строка = NULL | ||
Excel | & | "a"&"b" → "ab" | Пустые ячейки трактуются как пустые строки | ||
Python | + | "a" + "b" → "ab" | Не работает с None без преобразования |
Оператор "&" особенно эффективен при создании URL-адресов или путей к файлам, когда нужно объединить несколько компонентов:
= Table.AddColumn(
#"Предыдущий шаг",
"URL профиля",
each "https://example.com/users/" & Text.From([UserID]) & "/" & Text.Lower(Text.Clean([Username]))
)
Массовое объединение строк в таблицах Power Query
Когда требуется объединить значения из нескольких строк в одну, задача переходит от простого объединения строк к более сложной агрегации данных. Power Query предоставляет для этого мощные инструменты, позволяющие свертывать табличные данные различными способами. 📈
Типичный сценарий — когда нам нужно агрегировать все значения из определенного столбца на основе значений в другом столбце. Например, получить список всех продуктов для каждого клиента:
= Table.Group(
#"Предыдущий шаг",
{"КлиентID"}, // столбцы для группировки
{
{"Имя клиента", each [ИмяКлиента]{0}, type text}, // берем первое значение
{"Все продукты", each Text.Combine([НазваниеПродукта], ", "), type text} // объединяем все значения
}
)
В этом примере мы используем функцию Table.Group для группировки данных по КлиентID, а затем применяем Text.Combine к столбцу [НазваниеПродукта], чтобы получить список всех продуктов через запятую.
Другой распространенный сценарий — когда нужно транспонировать ряд значений из столбца в строку. Для этого можно использовать комбинацию функций группировки и свертки:
Екатерина Соловьева, аналитик данных
На проекте для крупного ритейлера мы столкнулись с проблемой — нужно было собрать отчет по всем транзакциям каждого клиента за год. Данные поступали ежедневно, и в базе данных была отдельная строка для каждой покупки. Клиент хотел видеть в одной строке всю историю покупок каждого человека.
Первое решение, которое мы попробовали — импорт в Excel и использование сводных таблиц — оказалось нежизнеспособным. При 5+ миллионах транзакций Excel просто зависал.
Я решила использовать Power Query и его функцию массового объединения строк. Настроив правильную группировку по ID клиента и применив Text.Combine в сочетании с Table.Group, я смогла свернуть все транзакции в одну строку для каждого клиента, добавив между ними разделитель-точку с запятой.
Результат превзошел ожидания: процесс обработки 5 миллионов строк занял всего 3 минуты, а конечная таблица содержала всего 200 000 строк (по числу клиентов) с полной историей покупок в одном столбце. Заказчик был впечатлен, а я в очередной раз убедилась в мощи Power Query для задач агрегации данных.
Для более сложных сценариев группировки вы можете использовать сочетание функций List и Text:
= Table.Group(
#"Предыдущий шаг",
{"КатегорияПродукта"},
{
{"Средняя цена", each Number.Round(List.Average([Цена]), 2), type number},
{"Ценовой диапазон", each Text.Combine(
{
"от " & Text.From(List.Min([Цена])),
"до " & Text.From(List.Max([Цена]))
},
" "),
type text},
{"Все производители", each Text.Combine(List.Distinct([Производитель]), ", "), type text}
}
)
Обратите внимание на использование List.Distinct перед Text.Combine в последней агрегации — это позволяет избежать дублирования производителей в конечном списке.
Для особо сложных структур данных, вы можете сначала преобразовать результаты в JSON, а затем использовать его как промежуточный формат:
= Table.Group(
#"Предыдущий шаг",
{"КлиентID"},
{
{"ДанныеТранзакций", each
Text.FromBinary(
Json.FromValue(
Table.ToRecords(
Table.SelectColumns(_, {"Дата", "Сумма", "Продукт"})
)
)
),
type text}
}
)
Решение типичных проблем при объединении строк
Несмотря на кажущуюся простоту операции объединения строк, на практике аналитики сталкиваются с рядом сложностей. Рассмотрим наиболее распространенные проблемы и их решения.
Проблема 1: Null-значения и пустые строки
Как мы уже обсуждали, оператор "&" превращает null-значения в строку "null", а функция Text.Combine по умолчанию пропускает null-значения. Для последовательной обработки таких случаев используйте условные выражения:
= each Text.Combine(
List.Transform(
{[Имя], [Отчество], [Фамилия]},
each if _ = null then "" else _
),
" "
)
Проблема 2: Неправильный порядок элементов
Когда источник данных приходит с непостоянным порядком элементов, может потребоваться предварительная сортировка:
= each Text.Combine(
List.Sort([Теги]),
", "
)
Проблема 3: Дубликаты в объединенных списках
Особенно актуально при объединении списков значений из нескольких строк:
= each Text.Combine(
List.Distinct([Категории]),
", "
)
Проблема 4: Объединение числовых значений
Числа в Power Query не имеют стандартного форматирования при преобразовании в строку:
= each "Цена: " &
Number.ToText([Цена], "### ##0.00 ₽", "ru-RU")
Проблема 5: Сложное условное форматирование
Когда нужно применить различные форматы в зависимости от условий:
= each
[Фамилия] & " " &
Text.Start([Имя], 1) & "." &
(if [Отчество] = null then "" else Text.Start([Отчество], 1) & ".")
Проблема 6: Объединение строк с форматированием разделителей
Часто требуется добавить разделитель только между непустыми элементами:
= let
values = List.Select({[Улица], [Дом], [Квартира]}, each _ <> null and _ <> ""),
result = Text.Combine(values, ", ")
in
result
Проблема 7: Обработка специальных символов
При формировании URL или путей к файлам может потребоваться кодирование специальных символов:
= each "https://example.com/search?q=" &
Uri.EscapeDataString([ПоисковыйЗапрос])
Работая над объединением строк в Power Query, помните о принципе KISS (Keep It Simple, Stupid). Начинайте с простых решений, и только при необходимости переходите к более сложным конструкциям. Часто оптимальное решение — это не самое короткое, а самое понятное и поддерживаемое. 🛠️
Хотите узнать, насколько работа с данными соответствует вашим природным способностям и карьерным предпочтениям? Пройдите Тест на профориентацию от Skypro! Вы получите индивидуальный отчет с анализом ваших сильных сторон и рекомендациями по развитию карьеры в сфере аналитики данных. Особенно полезно, если вы только начинаете изучать Power Query и другие инструменты анализа данных или задумываетесь о смене специализации.
Объединение строк в Power Query — это навык, который значительно ускоряет вашу работу с данными и открывает новые возможности для анализа. Выбирайте правильный метод для конкретной задачи: используйте оператор & для простых случаев, Text.Combine для работы с массивами, и комбинируйте их с функциями группировки для сложных сценариев. Помните о корректной обработке null-значений и специальных символов. Автоматизируя рутинные операции с помощью Power Query, вы освобождаете время для стратегического мышления и принятия решений на основе полученных результатов.