Всем привет!
Сегодня поговорим о том, как загрузить в редактор Power Query несколько Excel файлов, у которых есть отличия в названии столбца.
Конечно, все можно исправить руками в Excel. Только как быть, если файлы большого объема и их десятки или сотни. А нам нужна одна плоская таблица для последующей работы и анализа.
Решение есть и сегодня мы с ним познакомимся. Придется немного поработать с кодом Power Query в расширенном редакторе. Но на выходе получается элегантно и красиво.
А главное позволяет автоматизировать одну из самых распространенных задач при обработке данных в Power Query перед началом работы.
Что у нас есть. Разбираемся с примерами файлов.
Итак, начнем с того, что познакомимся с нашими файлами и поймем, какой сюрприз нас ждет.
У нас три отчета по продажам за определенные периоды времени по сотруднику.
Отличаются названием столбца «С» - «Факт продаж на дату». Отличие, собственно, в самой дате.
Как видим, у нас отчеты за два. В столбце «А» - «Менеджер» указан сотрудник.
В столбце «В» - «Факт продаж» идет накопительный итог факта продаж за период.
В столбце «С» - указано значение факт продаж за дату из столбца «D».
Поэтому объединить нам эти таблицы просто не получится.
Как решать такую задачу?
Конечно, первое, что приходит в голову, это написать скрипт, который бы удалял часть названия столбца «С» после «Факт продаж» при загрузке в Power Query. Таким образом, мы бы получили одинаковые столбцы. Но, что если нам нужно сохранить название, потому что там хранится важный атрибут? Или у нас несколько столбцов с разными названиями? Или столбцы у нас идут в разном порядке в разных файлах. Все может быть. Ведь нам их присылает человек.
Начинаем загружать данные в Power Query.
Поэтому открываем Power Query.
- На ленте Excel выбираем «Данные», далее в левом верхнем углу выбираем «Получить данные».
Нам нужно получить данные «Из файла» и «Из папки».
Теперь Power Query просит указать нам папку, где лежат наши файлы.
- Выбираем папку и нажимаем «Открыть».
Вы должны увидеть окно со списком Ваших файлов.
- Нажимаем на выпадающий список «Объединить» и выбираем «Объединить и преобразовать данные»
Выбираем какой файл взять за основу – тут не принципиальный момент. Поэтому выбирайте любой понравившейся. Видим его содержимое в окне предпросмотра и нажимаем «Ок».
Итак, мы получили ожидаемый результат. Файлы Power Query объединил по образу и подобию выбранного файла для примера (в моем случае это был первый файл, отчет за 22.06.2021). И логично, что «Факт продаж», как совпадающий столбец по названию он смог объединить. А вот столбец «С» он смог взять значения только из первого (файл пример) файла, а далее показывает пусто. Так как в других файлах названия у столбца «С» другие.
Немного подшаманим код Power Query! Спасибо Google!
Вот описанное выше нам и предстоит исправить. На просторах интернета мне попалась простая функция, которая делает очень полезную вещь. Меняет название у заданного количества столбцов с конца (то есть последних) на другие. Новые также задаются руками.
Исходный код у этой функции, который я нашел, следующий:
let
Source = Source,
ColumnNamesOld = List.LastN(Table.ColumnNames(Source), 3),
ColumnNamesNew = {"Amount 1", "Amount 2", "Amount 3"},
ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),
#"Renamed Columns" = Table.RenameColumns(Source, ZippedList)
in
#"Renamed Columns"
Теперь нам нужно сделать две вещи.
Первое – встроить код этой функции в наш код;
Второе – сделать так, чтобы она применялась ко всем нашим файлам.
Начинаем.
Поскольку логика запроса «Загрузить и объединить» состоит в том, что Power Query последовательно по шагам работает с каждым файлом на основе файла примера, то сначала нам нужно сделать так, чтобы в файле примере у нас автоматически изменялось название столбца «С».
Первое, что мы должны сделать, это встать на шаг запроса под названием «Преобразовать пример файла» и открыть расширенный редактор в меню ленты «Главное».
Сейчас код выглядит так:
let
Источник = Excel.Workbook(Параметр1, null, true),
Лист1_Sheet = Источник{[Item="Лист1",Kind="Sheet"]}[Data],
#"Повышенные заголовки" = Table.PromoteHeaders(Лист1_Sheet, [PromoteAllScalars=true])
in
#"Повышенные заголовки"
Нам нужно добавить код после строки #”Повышенные заголовки”. Не забудьте поставить запятую в конце этой строки, и изменить шаг, по итогу которого применяется результат запроса.
Ниже я приведу вставленный код функции с комментариями.
После запуска этого кода, получаем следующий результат:
Итог:
Код:
let
Источник = Excel.Workbook(Параметр1, null, true),
Лист1_Sheet = Источник{[Item="Лист1",Kind="Sheet"]}[Data],
A# "Повышенные заголовки" = Table.PromoteHeaders(Лист1_Sheet, [PromoteAllScalars=true]),
// вставляем после запятой код функции
// ColumnNamesOld: важно в скобках функции Table.ColumnNames указать привязку к предыдущему шагу # "Повышенные заголовки"
// после запятой заменить количество изменяемых столбцов. В нашем случае на значение 2. Так как меняем столбец "С", а "D" все-равно
// придеться захватить, так как меняем столбцы с конца
ИзменяемоеКоличествоСтолбцов = List.LastN(Table.ColumnNames(# "Повышенные заголовки"), 2),
//ColumnNamesNew: в фигурных скобках в кавычках пишем нужное нам название нового столбца, и "Дата" оставляем как есть
НовоеНазваниеИзменяемыхСтолбцов= {"Факт продаж на дату", "Дата"},
//ZippedList: функция принимает список списков lists и возвращает список списков, объединяя элементы на одной позиции
ЗаменненоеЗанчение = List.Zip( { ИзменяемоеКоличествоСтолбцов, НовоеНазваниеИзменяемыхСтолбцов } ),
//#"Renamed Columns" указываем в скобках первый шаг после которого мы добавили код и ссылку на предыдущий шаг
ПереименованныеСтолбцы= Table.RenameColumns(#"Повышенные заголовки", ЗаменненоеЗанчение)
// в конце не забываем указать, какой шаг у нас был последний
in
ПереименованныеСтолбцы
Но теперь мы видим, что в итоговом варианте объединенной таблицы у нас есть ошибка:
Все правильно, ведь код ссылается на старое название столбцов!
Давайте посмотрим через расширенный редактор:
let
Источник = Folder.Files("C:\Users\poddu\Desktop\Статья PQ 28.06.2021"),
#"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
#"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл", each #"Преобразовать файл"([Content])),
#"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл"}),
#"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл", Table.ColumnNames(#"Преобразовать файл"(#"Пример файла"))),
#"Измененный тип" = Table.TransformColumnTypes(#"Столбец расширенной таблицы1",{{"Source.Name", type text}, {"Менеджер", type text}, {"Факт продаж", Int64.Type}, {"Факт продаж на 22.06.2021", Int64.Type}, {"Дата", type date}})
in
#"Измененный тип"
В последнем шаге #"Измененный тип" видно старое название: «Факт продаж на 22.06.2021». А наше новое «Факт продаж на дату». Поэтому нам осталось только заменить в коде старое название на новое! И все готово! Наслаждайтесь результатом!
Теперь наши данные готовы к дальнейшей обработке и анализу!
Файлы Excel с примерами таблиц можно скачать тут:
Файл Excel с кодом PQ можно скачать тут
P.S: не забудьте изменить источник, то есть папку, из которой вы будете загружать свои данные!
