Как объединить в power query excel столбцы

Слияние запросов (Power Query)

При слиянии обычно объединяются два запроса, которые находятся в Excel или из внешнего источника данных. Кроме того, функция слияния имеет интуитивно понятный пользовательский интерфейс, помогающий легко объединить две связанные таблицы. Пример объединения общего объема продаж из запроса сведений о заказе в таблицу продуктов см. в руководстве Об объединении нескольких источников данных.

Внимание: Уровни конфиденциальности не позволяют пользователю непреднамеренно объединять данные из нескольких источников данных, которые могут быть частными или организационными. В зависимости от запроса пользователь может непреднамеренно отправить данные из частного источника данных в другой источник данных, который может быть вредоносным. Power Query анализирует каждый источник данных и классифицирует его по определенному уровню конфиденциальности: общедоступному, организационному и частному. Дополнительные сведения об уровнях конфиденциальности см. в разделе Установка уровней конфиденциальности.

Сведения о слиянии запросов

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

Существует два типа операций слияния:

• Встроенное слияние Вы объединяете данные в существующий запрос, пока не достигнете окончательного результата. Результатом является новый шаг в конце текущего запроса.
• Промежуточное слияние Для каждой операции слияния создается новый запрос.

Чтобы просмотреть визуальное представление связей в диалоговом окне Зависимости запросов , выберите Просмотр > зависимостей запросов. В нижней части диалогового окна выберите команду Макет , чтобы управлять ориентацией схемы.

Выполнение слияния

Вам потребуется по крайней мере два запроса, которые можно объединить и которые имеют по крайней мере один или несколько столбцов для сопоставления в операции соединения. Они могут поступать из разных типов внешних источников данных. В следующем примере используются Products и Total Sales.

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
2. Выберите Главная >Запросы слияния. По умолчанию выполняется встроенное слияние. Чтобы выполнить промежуточное слияние, щелкните стрелку рядом с командой и выберите Команду Объединить запросы как Новые.

Убедитесь, что выбрано одинаковое количество столбцов для сопоставления в предварительном просмотре основной и связанной или вторичной таблиц. Сравнение столбцов основано на порядке выбора в каждой таблице. Соответствующие столбцы должны иметь один и тот же тип данных, например Text или Number. Можно также выбрать несколько столбцов для слияния.

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

Операция соединения по умолчанию — это внутреннее соединение, но в раскрывающемся списке Тип соединения можно выбрать следующие типы операций соединения: Внутреннее соединение Возвращает только совпадающие строки из основной и связанной таблиц.

Левое внешнее соединение Сохраняет все строки из первичной таблицы и возвращает все совпадающие строки из связанной таблицы.

Правое внешнее соединение Сохраняет все строки из связанной таблицы и возвращает все совпадающие строки из основной таблицы.

Полный внешний Возвращает все строки из основной и связанной таблиц.

Левое анти-соединение Возвращает только строки из основной таблицы, в которых нет совпадающих строк из связанной таблицы.

Правое анти-соединение Возвращает только строки из связанной таблицы, в которых нет совпадающих строк из основной таблицы.

Result (Результат)

Разверните столбец Таблица

После операции слияния можно развернуть структурированный столбец Таблица, чтобы добавить столбцы из связанной таблицы в основную таблицу. После развертывания столбца в главной таблице вы можете применить фильтры и другие операции преобразования. Дополнительные сведения см. в статье Работа со структурированным столбцом списка, записи или таблицы.

1. В предварительном просмотре данных щелкните значок Развернутьрядом с заголовком столбца NewColumn .
2. В раскрывающемся списке Развернуть выберите или очистите столбцы, чтобы отобразить нужные результаты. Чтобы агрегировать значения столбцов, выберите Агрегат.

Вы можете переименовать новые столбцы. Дополнительные сведения см. в разделе Переименование столбца.

Группирование строк данных (Power Query)

В Power Query одни и те же значения можно сгруппировать в одном или нескольких столбцах в одну сгруппированную строку. Столбец можно группировать с помощью агрегатной функции или группы по строке.

Следующие процедуры основаны на этом примере данных запроса:

Группирование столбца с помощью агрегатной функции

Вы можете группировать данные с помощью агрегатной функции, например Sum и Average. Например, необходимо свести итоговые суммы проданных единиц на уровне страны и канала продаж, сгруппированные по столбцам Страна и Канал продаж .

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, изменение и загрузка запроса в Excel.
2. Выберите Главная >Группировать по.
3. В диалоговом окне Группировать по выберите Дополнительно , чтобы выбрать несколько столбцов для группировки.
4. Чтобы добавить другой столбец, выберите Добавить группирование.

Имя нового столбца введите «Всего единиц» для нового заголовка столбца.

Операции Выберите Сумма. Доступные агрегаты: Sum, Average, Median, Min, Max, Count Rows и Count Distinct Rows.

Result (Результат)

Группировка по строке

Операция строки не требует столбца, так как данные группируются по строке в диалоговом окне Группировать по. При создании нового столбца можно выбрать два варианта:

Счетчик строк , отображающий количество строк в каждой сгруппированной строке.

Все строки Вставляется внутреннее значение таблицы. Он содержит все строки для столбцов, по которым вы группируете. При необходимости можно развернуть столбцы позже. Дополнительные сведения см. в статье Работа со структурированным столбцом списка, записи или таблицы.

Последовательность действий

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

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
2. Выберите Главная >Группировать по.
3. В диалоговом окне Группировать по выберите Дополнительно , чтобы выбрать несколько столбцов для группировки.
4. Добавьте столбец для агрегирования, выбрав Добавить агрегат в нижней части диалогового окна.

Агрегирование агрегирования столбца Units с помощью операции Sum. Назовите этот столбец Всего единиц.

Result (Результат)

Объединение столбцов (Power Query)

В Power Query можно объединить несколько столбцов в запросе. Вы можете объединить столбцы, чтобы заменить их одним объединенным столбцом, или создать новый объединенный столбец вместе со столбцами, которые будут объединены. Объединить можно только столбцы типа данных «Текст». В примерах используются следующие данные:

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

Слияние столбцов с заменой существующих столбцов

При объединении столбцов выбранные столбцы объединяются в один столбец под названием Объединенные. Исходные два столбца больше не доступны.

В этом примере мы объединяем OrderID и CustomerID.

1. Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос>изменить. Дополнительные сведения см. в этойExcel.
2. Убедитесь, что столбцы, которые вы хотите объединить, являются текстовыми. При необходимости выберем столбец и выберите преобразовать>тип данных >текст.
3. Выберите несколько столбцов, которые нужно объединить. Чтобы выбрать несколько столбцов поперемно или поперемно, нажмите shift+щелчок или CTRL+щелчок каждого последующего столбца.

Порядок выбора задает порядок объединенных значений.

Выберите преобразовать>объединить столбцы.

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

Нажмите кнопку ОК.

Объединенный столбец можно переименовать, чтобы он был более осмысленным. Дополнительные сведения см. в статье Переименование столбца.

Слияние столбцов с созданием нового столбца

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

В этом примере мы соедиам OrderID и CustomerID, разделив их пробелом.

1. Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос>изменить. Дополнительные сведения см. в этойExcel.
2. Убедитесь, что столбцы, которые вы хотите объединить, имеют текстовый тип данных. Выберите преобразовать>Тип >текст.
3. Выберите Добавить столбец>настраиваемый столбец. Появится диалоговое окно Пользовательский столбец.
4. В списке Доступные столбцы выберите первый столбец и выберите Вставить. Вы также можете дважды щелкнуть первый столбец. Столбец добавляется в поле Настраиваемая формула столбца сразу после знака равно (=).

Выберите ОК.

Результат

Настраиваемый столбец можно переименовать, чтобы он был более осмысленным. Дополнительные сведения см. в статье Переименование столбца.

Как объединить в power query excel столбцы

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071