Добрый день! Подскажите, пожалуйста, каким образом можно с помощью Power Query объединить таблицы из файлов Excel находящихся в одной папке. Я знаю - это должна быть комбинация из двух запросов - 1-й получение файлов из папки, второй - выгрузка из файла Excel. Целый день бьюсь, ничего не выходит. Посмотреть решение тоже негде, везде как то опускают эти тонкости , пишут - можно и все.
Стуктура запроса FromFolder для получения файлов из папки:
Код
let
Источник = Folder.Files("F:\Обучение\макросы_тренинг\Power Query"),
#"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Name"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Другие удаленные столбцы", "Nam", each FromExcel),
#"Развернутый элемент Nam" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Nam", {"Сотрудник", "Оклад", "Дожность"}, {"Nam.Сотрудник", "Nam.Оклад", "Nam.Дожность"})
in
#"Развернутый элемент Nam"
Скрытый текст
Берем экселевские файлы из папки Power Query , чикаем лишнюю инфу о файлах, добавляем пользовательский столбец. В формуле прописываем имя запроса FromExcel - мой запрос который берет данные с файла.
Структура запроса FromExcel для получения данных из Файла:
Код
let
Источник = Excel.Workbook(File.Contents("F:\Обучение\макросы_тренинг\Power Query\ОМ.xlsx"), null, true),
Лист1_Sheet = Источник{[Item="Лист1",Kind="Sheet"]}[Data],
#"Повышенные заголовки" = Table.PromoteHeaders(Лист1_Sheet),
#"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Сотрудник", type text}, {"Оклад", Int64.Type}, {"Дожность", type text}}),
#"Строки с применным фильтром" = Table.SelectRows(#"Измененный тип", each ([Дожность] = "нач"))
in
#"Строки с применным фильтром"
Скрытый текст
Берем файл с именем ОМ и убираем все лишнее. Кстати сразу бросается в глаза косяк кода в русской версии офиса 16 -"применным"
Собственно, нужно каким то образом заменить имя "ОМ" на параметр. Я так думаю, параметр будет определяться в 1-м запросе - там ведь у нас имя файла есть..но как это осуществить..? Язык М не знаю, кто может для тупого объяснить?
PS Николай, было бы здорово, если бы вы начали добавлять на сайт ролики с применением PQ
NikitaV написал: ... объединить таблицы из файлов Excel находящихся в одной папке... Посмотреть решение тоже негде, везде как то опускают эти тонкости , пишут - можно и все.
увидеть таблицы тоже негде вы как-то опустили эти тонкости... а ведь таблицы бывают разные, поэтому и объединяются по-разному - всё, как всегда, зависит от структуры данных... которую никто не видит кроме вас ... p.s. уже ведь не 1-й пост на форуме - в Правилах о файле(ах)-примере P.P.S загляните под спойлер - много примеров на любой вкус... например, вариант (из #5)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Спасибо за ответ, как то проглядел эту тему. А на Ваше замечание касательно примера таблиц - какая разница какие они? 3 у них столбца, или 10 - это не важно. В запросе ведь убиваются лишние, а сами таблицы одинаковы по своей структуре изначально - это я просто опускаю за очевидностью факта.
Способов уверен не 1 и не 2 - но это если знаешь язык М, ну и шаришь в его логике. Я, увы, пока только пытаюсь постичь и то, и то, поэтому мне хотя бы какой то вариантик) Скачал вариант 5, предложенный Вами) Спасибо, пойду разбираться)
ЮрийМ, позвольте уточнить, в каких случаях Вы рекомендуете использовать цитирование, а в каких нет. Я применяю цитирование, если хочу дать ответ на конкретное высказывание конкретного человека. Людей здесь много разных, поэтому, очевидно, нужно указывать кому адресован ответ, поэтому я использую цитирование. Возможно есть другие способы, буду рад если научите.
Андрей VG, я видел Вас в какой то теме связанной с PQ Еще раз повторюсь, банальное сливание 3 одинаковых табличек требовалось, поэтому как то даже не подумал что могут какие то вопросы возникнуть В общем, был не прав, исправлюсь))
JeyCi, Ваша ссылка просто супер кладезь полезной инфы. Еще раз благодарю.
макросы_тренинг.rar(35.98 КБ) Андрей VG, ваш код помог мне сделать что я хотел. Вы осуществили объединение в 1-м запросе. Но разобраться полностью в коде я не смог. Можете прояснить некоторые моменты? Исходники в архиве.
В файл с запросом на PQ - тот же самый - "помесячный расчет", поменял лишь адрес, и удалил что не нужно. Код:
Код
let
fileFolder = Excel.CurrentWorkbook(){[Name="Путь"]}[Content]{0}[Column1],
source = Folder.Files(fileFolder),
folderData = Table.SelectRows(source,each Text.Lower([Extension])=".xlsx")[Content],
baseTables = Table.Combine(List.Transform(List.Positions(folderData),each Excel.Workbook(folderData{_},true))),
neededTables = Table.SelectRows(baseTables, each [Kind]="Sheet")[Data],
combined = Table.Combine(neededTables)
in
combined
Прокомментирую код как я его понял: 1) при задании адреса используется ячейка текущей рабочей книги. Это определяется в 1-й строке кода.Вы обозвали диапазон с ячейкой A2 как "Путь" и PQ понял. Супер) Что означает дальнейший кусок кода: [Content]{0}[Column1] ? 2)Далее, источником становится папка по указанному нами адресу 3) folderData определяется Вами как фильтр по расширению xlsx (Text.Lower ??? похоже с синтаксисом придется долго разбираться) , а что осталось определяется как (как бы это сказать).. сущность. Как я понял, чтобы сделать сущность , и нужно магическое [Content] в конце строки 4) шаг baseTables - вот тут магия какая то. Ничего не понимаю. 5) Шаги neededTables и combined воспроизводятся редактором, но код длиннее получается.
И еще 1 момент: Как Вы считаете, есть ли разница во времени если сначала обработать файлы с помощью запросов (фильтр сделать, удалить ненужное и т.п) и потом объединять уже запросы, или же сначала объединить файлы, а затем обрабатывать. С помощью Вашего кода я сначала объединяю, потом уже обрабатываю. Может можно как то и 1-й способ реализовать?
NikitaV написал: объединять уже запросы, или же сначала объединить файлы
С точки зрения быстродействия - особой разницы не вижу, а вот по памяти - выигрыш будет, особенно это актуально, если у вас 32битная версия Excel. Успехов.
Вас также с праздником! И спасибо за ответ и за полезные ссылки Обязательно их проштудирую. Кстати, касательного Вашего решения и его применения к моему примеру - Если сначала объединить файлы, то мы теряем возможность идентифицировать информацию, ведь вставить столбец с названием файла источника уже не получится, это нужно делать до объединения файлов..Изначально я хотел это сделать с помощью двух запросов. 1-й - фильтрует файлы по начальнику и добавляет имя файла в пользовательский столбец. А второй уже объединяет запросы.. Если будет свободная минутка, можете показать как бы вы сделали это?
Теперь по коду..
Цитата
Андрей VG написал: Всего лишь получение списка элементов столбца [Content] таблицы source
Супер. Теперь я понял как и этот шаг повторить с помощью редактора запросов. Нужно лишь добавить после автокода [Content]
Цитата
Андрей VG написал: Всего лишь итерация по индексам списка folderData выполняется преобразование элементов Content (получение таблицы описания содержимого очередного файла) и объединение через Table.Combine в единую таблицу данных файлов. Дальше вы, как понял, понимаете, что происходит.
слабо понимаю объяснение, придется читать литературу. Дальше вроде понял. Сейчас еще лучше. Но вот что интересно, если в книгах больше 1 листа, то, поскольку на предпоследнем шаге проверяем у каких данных тип Sheet,
Код
neededTables = Table.SelectRows(baseTables, each [Kind]="Sheet")[Data]
будут добавляться таблицы и с других листов ..проверил - не ошибся, урря) Вот решение проблемы
Код
neededTables = Table.SelectRows(baseTables, each ([Kind]="Sheet" and [Name]="Лист1"))[Data]
Ладно, еще раз с праздником. Пойду попробую реализовать объединение запросов. Если получится скину сюда.
NikitaV написал: Если будет свободная минутка, можете показать как бы вы сделали это?
так примера как не было так и нет - что есть, что должно быть результатом. На чёч тогда показывать?
Цитата
NikitaV написал: Если сначала объединить файлы, то мы теряем возможность идентифицировать информацию, ведь вставить столбец с названием файла источника уже не получится
это почему? Всего лишь выполнить две последовательные трансформации столбцов - рстануться имена файлов, а затем ExpandTableColumn. В прочем, подходы могут быть разные. Не факт, что мой вариант будет эффективнее.
Андрей VG, я заново добавил файлы - исходники, и там же файл - результат - то как должно быть. Т.е мы сначала фильтруем инфу по должности - нач, а потом объединяем.
let
sPath = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{[Параметр="Где"]}[Значение],
sWho = Text.Lower(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{[Параметр="Кого"]}[Значение]),
source = Folder.Files(sPath),
xlFiles = Table.SelectRows(source,each Text.Lower([Extension])=".xlsx")[[Name],[Content]],
toTables = Table.TransformColumns(xlFiles, { {"Content",each Excel.Workbook(_,true){[Kind="Sheet"]}[Data], type table} }),
neededOnly = Table.TransformColumns(toTables, { {"Content", each Table.SelectRows(_, each Text.Lower([Должность])=sWho),type table} }),
return = Table.ExpandTableColumn(neededOnly, "Content", {"Должность", "Оклад", "Сотрудник"})
in
return
До шага toTables все ясно. Очень понравилось как Вы Диапазон задали, и пользуетесь им. Я ранее не использовал никогда функцию Ячейка, не знал что так можно определить путь к файлу. Эт новая функция или давно уже существует?
А вот шаги toTables и neededOnly с операцией Table.TransformColumns и кучей прибамбасов - для меня начинаются танцы с бубном) ну ладно, эт лирика. Спасибо огромное, благодаря Вашему Коду я продвинулся в изучении так, как сам бы не продвинулся и за месяц )))
Так и там всё тоже самое 1. Извне PQ принимает фактически всегда таблицы (за исключением бинарных случаев). 2. Наружу (в таблицу Excel или Power Pivot) отдаёт только таблицы. 3. Ячейка таблицы в PQ может содержать любой допустимый тип, включая таблицу, и даже функцию.
Цитата
NikitaV написал: Эт новая функция или давно уже существует?