Глава 3. Простое объединение таблиц в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Перед профессионалами Excel часто встают задачи объединения данных из нескольких однотипных таблиц.[1] Power Query может делать это автоматически.

В папке примеров есть три CSV-файла: Jan 2008.csv, Feb 2008.csv и Mar 2008.csv. Начните с импорта первого файла:

  • Создайте новую книгу Excel
  • Создайте запрос Данные –> Из текстового/CSV-файла
  • Выберите файл Jancsv

Ris. 3.1. Importirovannyj CSV fajl Jan 2008.csv soderzhit odnu oshibku

Рис. 3.1. Импортированный CSV-файл Jan 2008.csv содержит одну ошибку

Скачать заметку в формате Word или pdf, примеры в формате архива

Power Query импортирует файл и автоматически выполнит следующие действия:

  • Продвинет первую строку в заголовки.
  • Задаст типы данных.

Поскольку исходный файл Jan 2008.csv содержит данные в стандарте США, следует удалить шаг Измененный тип, и повторно назначить типы данных, используя для столбцов TranDate и Sum of Amount локальные установки США (а не РФ, действующие по умолчанию; подробнее см. предыдущую главу). Переименуйте столбцы TranDate –> Date и Sum of Amount –> Amount. Нажмите кнопку Закрыть и загрузить. Данные будут импортированы на лист Excel в виде, как на рис. 1.

Данные всё еще содержат одну ошибку – общие итоги. Вернитесь в редактор Power Query. Выделите столбец Date, кликните Удалить строки –> Удалить ошибки. Нажмите Закрыть и загрузить. Строка с итогами будет удалена.

Повторите операции для импорта Feb 2008.csv и Mar 2008.csv. Когда вы закончите, у вас будет три таблицы в книге Excel, каждая на своем листе. Чтобы объединить таблицы создайте новый запрос. Пройдите по меню Получить данные –> Объединить запросы –> Добавить:

Ris. 3.2. Menyu obedineniya zaprosov

Рис. 3.2. Меню объединения запросов

Откроется диалоговое окно Добавление (рис. 3.3). Доступ к окну Добавление можно получить и из редактора Power Query. Для этого в редакторе перейдите на вкладку Главная и пройдите по меню Добавить в запросы –> Добавить запросы в новый. (рис. 3.4).

Ris. 3.3. Okno Dobavlenie

Рис. 3.3. Окно Добавление

Ris. 3.4. Dostup k oknu Dobavlenie iz redaktora Power Query

Рис. 3.4. Доступ к окну Добавление из редактора Power Query

Диалоговое окно Добавление объединяет запросы Power Query, а не таблицы Excel. Упорядочьте запросы в правом окне, чтобы данные располагались последовательно. Нажмите Ok. Power Query создаст новый запрос Append1, который включает один шаг:

Ris. 3.5. Novyj obedinennyj zapros Append1

Рис. 3.5. Новый объединенный запрос Append1

У вас может возникнуть соблазн прокрутить запрос вниз, чтобы увидеть, все ли ваши записи вошли в него. К сожалению, это займет много времени, так как бегунок работает не так как вы привыкли в Excel. При перемещении вниз новые строки будут подгружаться довольно медленно. Причина в том, что Power Query может использоваться для обработки больших наборов данных. Представьте, что вы подключаетесь к набору данных, из 5 миллионов строк, но хотите вытащить записи только для отдела №150. Power Query осуществляет как бы «предварительный просмотр», который должен дать достаточно информации для определения ключевой структуры данных. Вы выполните преобразования в данных предварительного просмотра и создаёте шаблон. Во время загрузки всех строк Power Query обрабатывает этот шаблон, извлекая только необходимые записи. Это намного эффективнее, чем загрузка всех данных в книгу и последующая обработка каждой строки и столбца.

Но если вы не видить все данные, как вы проверите, что объединенный запрос корректен? Переименуйте запрос Append1 –> Transactions. Кликните Закрыть и загрузить.

Ris. 3.6. Novyj zapros summiruet vse stroki treh zaprosov

Рис. 3.6. Новый запрос суммирует все строки трех запросов

Вы также можете создать сводную таблицу, и убедиться, что Excel корректно объединил запросы:

Ris. 3.7. Svodnaya tablitsa na osnove dannyh iz zaprosa Transactions

Рис. 3.7. Сводная таблица на основе данных из запроса Transactions

Объединение запросов с разными заголовками

Ниже показана ситуация, когда пользователь забыл переименовать столбец TranDate в запросе Mar 2008. При объединении запросов Jan 2008 и Mar 2008 получится:

Ris. 3.8. Stolbets TranDate polnyj nulevyh znachenij v yanvare

Рис. 3.8. Столбец TranDate, полный нулевых значений в январе, и столбец Date, полный нулевых значений в марте

Чтобы исправить это, откройте запрос Mar 2008, переименуйте столбец TranDate –> Date. Сохраните запрос Mar 2008. Откройте запрос Transactions. Как только вы откроете запрос, вы увидите, что он уже исправлен – столбец TranDate отсутствует. Чтобы поправить таблицу Transactions на листе Excel, просто обновите ее.

[1] На самом деле, Power Query поддерживает два типа объединений:

Obedinenie dobavlenie

В английском варианте, это Merge Queries и Append Queries. Первая опция позволяет объединять таблицы, исключая строки-дубли и проводя иные интеллектуальные операции с данными. Вторая опция просто добавляет каждый последующий набор в конец существующего. Пиктограммы довольно неплохо иллюстрируют это. Настоящая заметка посвящена второй опции.


Прокомментировать