Power query excel 2020 объединение таблиц

Объединение данных из нескольких источников (Power Query)

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

В этом учебнике вы будете использовать редактор запросов Power Query для импорта данных из локального файла Excel, содержащего сведения о продукте, и из веб-канала OData, содержащего сведения о заказах на продукты. Вы выполните преобразование и агрегирование, а также объедините данные из обоих источников для получения отчета Total Sales per Product and Year (Общий объем продаж по товарам и годам).

Для выполнения этого учебника требуется книга » товары и заказы «. В диалоговом окне Сохранение документа присвойте файлу имя Products and Orders.xlsx.

В этом учебнике

Задача 1. Импорт товаров в книгу Excel

В этой задаче вы импортируете продукты из файла Products и Orders. xlsx в книгу Excel.

Шаг 1. Подключение к книге Excel

Создайте книгу Excel.

На вкладке ленты POWER QUERY щелкните Из файла > Из Excel.

В диалоговом окне Excel найдите или введите путь к файлу Products and Orders.xlsx, чтобы импортировать его или создать на него ссылку.

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

Примечание: В конце этой статьи есть небольшое видео о том, как вывести редактор запросов.

Шаг 2. Преобразование первой строки в заголовки столбцов таблицы

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

Щелкните значок таблицы ( ) в левом верхнем углу окна предварительного просмотра данных.

Щелкните Использовать первую строку в качестве заголовков.

Шаг 3. Удаление ненужных столбцов

В этом шаге вы удалите все столбцы, кроме Product >ProductName, Category >QuantityPerUnit.

В области предварительного просмотра запроса выберите столбцы Product >ProductName, Category >QuantityPerUnit. Для этого щелкните их, удерживая нажатой клавишу CTRL или SHIFT.

На ленте редактора запросов щелкните Удалить столбцы > Удалить другие столбцы или щелкните правой кнопкой мыши заголовок столбца и выберите команду Удалить другие столбцы.

Созданные шаги Power Query

По мере выполнения действий с запросом в Power Query создаются шаги запроса, которые указываются на панели Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ. Каждому шагу запроса соответствует формула Power Query, что также называют языком «M». Дополнительные сведения о языке формул Power Query см. в статье Формулы Power Query.

Подключение к книге Excel

Преобразование первой строки в заголовки столбцов таблицы

Удаление ненужных столбцов

Шаг 4. Импорт запроса товаров

В этом шаге вы импортируете запрос Products в книгу Excel.

На ленте редактора запросов нажмите кнопку Применить и закрыть. Результаты появятся на новом листе Excel.

Задача 2. Импорт данных о заказах из веб-канала OData

В этой задаче нужно импортировать данные в книгу Excel из образца данных Northwind, доступного в веб-канале OData по адресу http://services.odata.org/Northwind/Northwind.svc.

Шаг 1. Подключение к веб-каналу OData

На вкладке ленты POWER QUERY щелкните Из других источников > Из канала OData.

В диалоговом окне Канал OData введите URL-адрес канала OData Northwind.

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

В области Навигатор дважды щелкните таблицу Orders или щелкните Orders и выберите Изменить.

Примечание: При наведении указателя мыши на таблицу вы увидите всплывающее окно с предварительным просмотром таблицы.

Шаг 2. Развертывание таблицы Order_Details

В этом шаге вы развертываете таблицу Order_Details, которая относится к таблице Orders, чтобы объединить столбцы Product >UnitPrice и Quantity из таблицы Order_Details с таблицей Orders. Операция Расширить объединяет столбцы из связанной таблицы с конечной таблицей. При выполнении запроса строки из связанной таблицы ( Order_Details) объединяются со строками конечной таблицы ( Orders).

В Power Query столбец, содержащий ссылку на связанную таблицу, включает ссылку на запись или таблицу. Ссылка для ввода позволяет перейти к одной связанной записи и представляетодну для одной связи с таблицей темы .Ссылка на таблицу переходит к связанной таблице и представляет связь «один-ко-многим» с таблицей темы. Ссылка представляет свойства навигации в источнике данных в рамках реляционной модели. Для канала OData свойства навигации представляют объект с ассоциацией на основе внешнего ключа. В базе данных, например SQL Server, свойства навигации представляют связи по внешнему ключу в базе данных.

Развертывание таблицы Order_Details

После развертывания таблицы Order_Details в таблицу Orders добавляются три новых столбца и дополнительные строки (по одному столбцу для каждой строки во вложенной или связанной таблице).

В области предварительного просмотра запроса прокрутите данные до столбца Order_Details.

В столбце Order_Details щелкните значок развертывания ( ).

В раскрывающемся списке Расширить:

Чтобы очистить все столбцы, щелкните (Выделить все столбцы).

Щелкните Product >UnitPrice и Quantity.

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

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

Шаг 3. Удаление ненужных столбцов

В этом шаге вы удалите все столбцы, кроме OrderDate, Product >UnitPrice и Quantity. В предыдущей задаче вы использовали команду Удалить другие столбцы. Для выполнения этой задачи вы удалите выделенные столбцы.

Удаление выделенных столбцов

В области предварительного просмотра запроса выберите все столбцы:

Щелкните первый столбец ( OrderID).

Щелкните последний столбец ( Shipper), удерживая клавишу SHIFT.

Щелкните столбцы OrderDate, Order_Details.Product >Order_Details.UnitPrice и Order_Details.Quantity, удерживая клавишу CTRL.

Щелкните правой кнопкой мыши заголовок выделенного столбца и выберите команду Удалить столбцы.

Шаг 4. Вычисление общей суммы для каждой строки Order_Details

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

Вычисление общей суммы для каждой строки Order_Details

В области предварительного просмотра запроса щелкните значок таблицы ( ) в левом верхнем углу окна предварительного просмотра.

Щелкните Вставить столбец > Пользовательский.

В диалоговом окне Вставить пользовательский столбец в поле Пользовательская формула столбца введите [Order_Details.UnitPrice] * [Order_Details.Quantity].

В текстовом поле Имя нового столбца введите Line Total.

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

Шаг 5. Преобразование столбца OrderDate в столбец года

В этом шаге вы преобразуете столбец OrderDate для отображения года заказа.

В области предварительного просмотра щелкните правой кнопкой мыши столбец OrderDate и выберите команды Преобразование > Год.

Переименуйте столбец OrderDate в Year:

Дважды щелкните столбец OrderDate и введите Year или

щелкните столбец OrderDate правой кнопкой мыши, выберите команду Переименовать и введите Year.

Шаг 6. Группировка строк по значениям ProductID и Year

В области предварительного просмотра запроса выберите Year и Order_Details.ProductID.

Щелкните один из заголовков правой кнопкой мыши и выберите команду Группировать по.

В диалоговом окне Группировать по:

В текстовом поле Имя нового столбца введите Total Sales.

В раскрывающемся списке Операция выберите Сумма.

В раскрывающемся списке Столбец выберите Line Total.

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

Шаг 7. Переименование запроса

Перед импортом данных продаж в Excel присвойте запросу имя Total Sales:

На панели Параметры запроса в текстовом поле Имя введите Total Sales.

Итоговый запрос

После выполнения всех шагов у вас будет запрос Total Sales для канала OData Northwind.

Созданные шаги Power Query

По мере выполнения действий с запросом в Power Query создаются шаги запроса, которые указываются на панели Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ. Каждому шагу запроса соответствует формула Power Query, что также называют языком «M». Дополнительные сведения о языке формул Power Query см. в статье Формулы Power Query.

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

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

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

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

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

Рис. 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, каждая на своем листе. Чтобы объединить таблицы создайте новый запрос. Пройдите по меню Получить данные –> Объединить запросы –> Добавить:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Импорт и консолидация таблиц Excel через Power Query

Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

Таблицы Excel

Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

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

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

Именованный диапазон Excel

Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

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

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.

Динамический именованный диапазон Excel

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

Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:

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

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

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

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

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

Затем в строке формул вводим знакомую команду

Power Query показывает все таблицы в текущей книге.

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

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

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

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

Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

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

Подчеркивание удаляется из названия.

Поиск и замена здесь работает так же, как и в обычном Excel.

Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

Сделаем с помощью сводной таблицы маленький отчет по месяцам.

Прошло время, и в файл добавили новую таблицу с продажами за апрель.

Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.

А вот, как это выглядит при использовании Power Query.

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

Вот за это мы так любим Power Query.

Собрать и просуммировать данные из разных файлов при помощи PowerQuery

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

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?

Ниже можно скачать файлы, которые применялись в статье. В архиве два файла бюджета(в папке Бюджет) и готовая модель с запросом(файл «Сводный»).
В файле с запросом так же применен прием получения пути к файлам динамически из папки, которая расположена в папке с файлом запроса. Подробнее про это можно прочитать в статье: Относительный путь к данным PowerQuery
Скачать готовую модель:

Модель агрегации файлов.zip (53,5 KiB, 653 скачиваний)

Видеоинструкция:

Для ведения бюджета применяется таблица такого вида:

Сама таблица преобразована заранее в так называемую «умную» таблицу: выделяем таблицу -вкладка Вставка (Insert) и выбрать Таблица (Table) :

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

Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) —Получить данные —Из файла —Из папки:

В появившемся окне указываем путь к папке, в которую были помещены файлы бюджетов, присланные филиалами

Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца: Content и Name :

Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши —Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:
=Excel.Workbook([Content])

Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» — получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:

Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:

Теперь столбцы Content , Name и Name.1 можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений NULL :

А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование —Таблица —Использовать первую строку в качестве заголовков:

Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 — имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:

Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец Статьи вкладка Преобразование —Таблица —Группировать по:

В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:

Группировка – оставляем поле Статьи . Ниже создаем 13 столбцов группировки – по одному на каждый месяц и один для Итого. Для каждого столбца указываем имя(лучше такое же как и имя исходного столбца – название месяца, т.к. именно они будут использоваться в итоговой таблице), Операция – Сумма .
Останется перейти на вкладку Главная —Закрыть и загрузить. Готовая таблица будет выгружена на новый лист текущей книги.
Теперь, если в папку будут помещены другие файлы или имеющиеся будут заменены другими и результирующую таблицу бюджета потребуется обновить – все, что необходимо будет сделать, это на созданной PowerQuery таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:

Все файлы в папке будут просмотрены, преобразованы и просуммированы.

Статья помогла? Поделись ссылкой с друзьями!