Страницы: 1
RSS
Объединение таблиц Excel посредством PowerQuery, Параметризация запросов, объединение таблиц их файлов Excel из одной папки
 
Добрый день! Подскажите, пожалуйста, каким образом можно с помощью 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"
Скрытый текст


Структура запроса 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
    #"Строки с применным фильтром"
Скрытый текст

Собственно, нужно каким то образом заменить имя "ОМ" на параметр. Я так думаю, параметр будет определяться в 1-м запросе - там ведь у нас имя файла есть..но как это осуществить..? Язык М не знаю, кто может для тупого объяснить?

PS Николай, было бы здорово, если бы вы начали добавлять на сайт ролики с применением PQ :)
Изменено: NikitaV - 08.05.2016 21:49:49
 
Цитата
NikitaV написал: ... объединить таблицы из файлов Excel находящихся в одной папке... Посмотреть решение тоже негде, везде как то опускают эти тонкости , пишут - можно и все.
увидеть таблицы тоже негде  ;) вы как-то опустили эти тонкости... а ведь таблицы бывают разные, поэтому и объединяются по-разному - всё, как всегда, зависит от структуры данных... которую никто не видит кроме вас  8)...
p.s. уже ведь не 1-й пост на форуме - в Правилах о файле(ах)-примере
P.P.S загляните под спойлер - много примеров на любой вкус... например, вариант (из #5)
Изменено: JeyCi - 08.05.2016 22:34:58
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Спасибо за ответ, как то проглядел эту тему. А на Ваше замечание касательно примера таблиц - какая разница какие они? 3 у них столбца, или 10 - это не важно. В запросе ведь убиваются лишние, а сами таблицы одинаковы по своей структуре изначально - это я просто опускаю за очевидностью факта.

Способов уверен не 1 и не 2  - но это если знаешь язык М, ну и шаришь в его логике. Я, увы, пока только пытаюсь постичь и то, и то, поэтому мне хотя бы какой то вариантик) Скачал вариант 5, предложенный Вами) Спасибо, пойду разбираться)
 
NikitaV, кнопка цитирования не для ответа.
 
Доброе время суток
Цитата
А на Ваше замечание касательно примера таблиц ...
вот потому и не стал отвечать ;)  -  позвольте отвечающим решать - пригодится ли пример.
 
ЮрийМ, позвольте уточнить, в каких случаях Вы рекомендуете использовать цитирование, а в каких нет. Я применяю цитирование, если хочу дать ответ на конкретное высказывание конкретного человека. Людей здесь много разных, поэтому, очевидно, нужно указывать кому адресован ответ, поэтому я использую цитирование. Возможно есть другие способы, буду рад если научите.

Андрей VG, я видел Вас в какой то теме связанной с PQ :) Еще раз повторюсь, банальное сливание 3 одинаковых табличек требовалось, поэтому как то даже не подумал что могут какие то вопросы возникнуть :) В общем, был не прав, исправлюсь))

JeyCi, Ваша ссылка просто супер кладезь полезной инфы. Еще раз благодарю.
 
Цитата
NikitaV написал:
в каких случаях Вы рекомендуете использовать цитирование, а в каких нет
Когда хотите на чём-то сделать акцент - цитирование - самое то.

Цитата
NikitaV написал:
хочу дать ответ на конкретное высказывание конкретного человека
А в тот момент кроме вас двоих в теме никого не было, И сообщение было всего одно.

Цитата
NikitaV написал:
Людей здесь много разных, поэтому, очевидно, нужно указывать кому адресован ответ
Я же в своём #4 смог обратиться именно к Вам. И без какого-либо цитирования.
 
Цитата
NikitaV написал: Андрей VG, я видел Вас в какой то теме связанной с PQ
Цитата
NikitaV написал: Ваша ссылка просто супер кладезь полезной инфы.
:D
 
макросы_тренинг.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 - 09.05.2016 15:30:03
 
С Днём Победы!
Цитата
NikitaV написал: Text.Lower
M-language - регистрозависимый. Поэтому все расширения приводятся в нижний регистр для отбора.
Цитата
NikitaV написал:  и нужно магическое [Content] в конце строки
Всего лишь получение списка элементов столбца [Content] таблицы source
Цитата
NikitaV написал:
шаг baseTables - вот тут магия какая то. Ничего не понимаю.
Всего лишь итерация по индексам списка folderData выполняется преобразование элементов Content (получение таблицы описания содержимого очередного файла) и объединение через Table.Combine в единую таблицу данных файлов. Дальше вы, как понял, понимаете, что происходит.
Чтобы это было лучше понимать - прочитайте Microsoft  Power Query for Excel Formula Language Specification и Microsoft Power Query  Preview  For Excel Formula  Library Specification.

Цитата
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 - 09.05.2016 16:08:41
 
Цитата
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  и кучей прибамбасов - для меня начинаются танцы с бубном) ну ладно, эт лирика. Спасибо огромное, благодаря Вашему Коду я продвинулся в изучении так, как сам бы не продвинулся и за месяц )))
Изменено: NikitaV - 09.05.2016 22:23:34
 
Цитата
NikitaV написал:
До шага toTables все ясно.
Так и там всё тоже самое ;)
1. Извне PQ принимает фактически всегда таблицы (за исключением бинарных случаев).
2. Наружу (в таблицу Excel или Power Pivot) отдаёт только таблицы.
3. Ячейка таблицы в PQ может содержать любой допустимый тип, включая таблицу, и даже функцию.
Цитата
NikitaV написал: Эт новая функция или давно уже существует?
Да как бы уже в Excel 4 была  :)
Страницы: 1
Наверх