Как объединить строки в Power Query: подробное руководство

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

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

• новички в сфере анализа данных, интересующиеся эффективными методами работы с данными

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

Если вы хотите не просто научиться объединять строки, а освоить комплексный подход к анализу данных, обратите внимание на Курс «BI-аналитик» с нуля от Skypro. На курсе вы не только изучите все возможности Power Query, но и освоите построение аналитических дашбордов, моделирование данных и создание эффективных визуализаций — всё, что нужно для превращения сырых данных в ценные бизнес-инсайты.

Основные методы объединения строк в Power Query

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

Существует четыре основных подхода к объединению строк:

• Использование функции Text.Combine — идеально для объединения строк с одинаковым разделителем
• Применение оператора & — самый простой способ для быстрого объединения нескольких значений
• Слияние столбцов через интерфейс Power Query — для пользователей, предпочитающих графический интерфейс
• Пользовательские функции — для сложных сценариев с условным форматированием

Вот сравнительная таблица этих методов:

Антон Петров, 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({[Фамилия], [Имя], [Отчество]}, " ")
)

В этом примере мы:

1. Добавляем новый столбец "Полное имя" к таблице
2. Для каждой строки объединяем значения из столбцов "Фамилия", "Имя" и "Отчество"
3. Используем пробел в качестве разделителя между элементами

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 " " & [Отчество] & " ") & 
[Имя]
)

Сравнение операторов объединения строк в разных языках и системах:

Оператор "&" особенно эффективен при создании 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, вы освобождаете время для стратегического мышления и принятия решений на основе полученных результатов.