Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Cобрать определенные значения из одинаковых по структуре файлов в одну таблицу, В файлах – данные по вертикали, в хотелке – по горизонтали
 
#1
22.01.2018 16:18:06

Добрый вечер.
Есть много файлов в одной папке.
В каждом файле по одному листу (названия листов разные).
Во всех файлах одинаковая структура данных.
То есть, заполнен диапазон A1:B10.

Можно ли в PQ собрать определенные значения из файлов в одну таблицу?

Рисунок удален: превышение допустимого СУММАРНОГО размера вложения [МОДЕРАТОР]
Изменено: Бахтиёр - 22.01.2018 21:01:47
 
 
 
#2
22.01.2018 16:21:51
На всякий случай прилагаю файлы.
 
 
 
#3
22.01.2018 17:08:21
Доброе время суток
Цитата
Бахтиёр написал:
Можно ли в PQ собрать определенные значения из файлов в одну таблицу?
Можно, отфильтровав таблицу, например по
Код
Table.SelectRows(soruceTable, each List.Contains({"Автомобиль", "Цвет", "Код"}, [ИмяСтолбцаСоЗначениями]))
 
 
 
#4
22.01.2018 17:52:34
Бахтиёр, ваши файлы из примера в папку D:\temp
Андрей VG, я может конечно не прав, но мне кажется, что у автора вызвало затруднение то, что в файлах листы имеют разные имена, т.к.  в приемах хорошо описана Сборка таблиц из разных файлов Excel с помощью Power Query, но с листов с одинаковым именем.
Изменено: StepanWolkoff - 22.01.2018 17:54:41
 
 
 
#5
22.01.2018 18:37:29
Возможно, спасибо
 
 
 
#6
23.01.2018 09:08:10

Андрей, таким кодом:

Код
let
    Источник = Folder.Files("D:\Temp"),
    #"Вызвать настраиваемую функцию1" = Table.AddColumn(Источник, "Преобразовать файл из Temp", each #"Преобразовать файл из Temp"([Content])),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
    #"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл из Temp"}),
    #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл из Temp", Table.ColumnNames(#"Преобразовать файл из Temp"(#"Пример файла"))),
    #"Измененный тип" = Table.TransformColumnTypes(#"Столбец расширенной таблицы1",{{"Source.Name", type text}, {"Name", type text}, {"Data", type any}, {"Item", type text}, {"Kind", type text}, {"Hidden", type logical}}),
    #"Развернутый элемент Data" = Table.ExpandTableColumn(#"Измененный тип", "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент Data",{"Data.Column1", "Data.Column2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Data.Column1] = "Автомобиль" or [Data.Column1] = "Код" or [Data.Column1] = "Цвет"))
in
    #"Строки с примененным фильтром"
я отфильтровал нужные строки (см. скрин), но не получается прийти к хотелке.

StepanWolkoff, можете подсказать, какие кнопки/команды вы нажимали?

Изменено: Бахтиёр - 23.01.2018 09:20:10
 
 
 
#7
23.01.2018 09:33:45
Здравствуйте. Может таблицу  после фильтра транспонировать?
 
 
 
#8
23.01.2018 09:40:11
Бахтиёр, практически все тоже самое что и вы, только в другом порядке) Сначала создал функцию getData, как это делает Николай в приемах, потом применил эту функцию к файлам в папке.
 
 
 
#9
23.01.2018 09:52:06
Там транспонирование не поможет, там группировать надо, а потом уже транспонировать.
Примерно так.
Вот горшок пустой, он предмет простой...
 
 
 
#10
23.01.2018 09:56:41
На основе одного любого файла создаем функцию обработки файла, я брал за пример файл Волга.xlsm.
При создании подключения к файлу мастер в PowerQuery создает сразу несколько шагов, в нашем случае шаги: навигация и изменить тип - будут лишними, поэтому удаляем их, оставляя только шаг источник. Далее комментарии в коде
Код
let
    Источник = Excel.Workbook(File.Contents("D:\temp\Волга.xlsm"), null, true),
    --Разворачиваем поле Data, нажав в заголовке стрелочки
    #"Развернутый элемент Data" = Table.ExpandTableColumn(Источник, "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"}),
    --Выделяем получившиеся столбцы, нажимаем ПКМ и выбираем пункт "Удалить другие столбцы"
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент Data",{"Data.Column1", "Data.Column2"}),
    --По первому столбцу накладываем фильтр, оставляя только строки: Автомобиль, Код, Цвет
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Data.Column1] = "Автомобиль" or [Data.Column1] = "Код" or [Data.Column1] = "Цвет")),
    --Выделяем первый столбец и на вкладке "Преобразование" нажимаем "Столбец сведения". Разворачиваем"Расширенные параметры и выбираем "Не агрегировать"
    #"Сведенный столбец" = Table.Pivot(#"Строки с примененным фильтром", List.Distinct(#"Строки с примененным фильтром"[Data.Column1]), "Data.Column1", "Data.Column2")
in
    #"Сведенный столбец"

Полученный код преобразуем в функцию
Скрытый текст
Изменено: StepanWolkoff - 23.01.2018 09:58:59
 
 
 
#11
23.01.2018 09:58:36
Кто как :)
Если без мышиного хардкода, то такой вариант
Код
let
    names = {"Автомобиль", "Цвет", "Код"},
    /* В этой книге в ячейке с именем folderPath храниться путь к папке с файлами.
       В этой папке хранятся только обрабатываемые файлы и никакие другие */
    sourceFolder = Excel.CurrentWorkbook(){[Name="folderPath"]}[Content]{0}[Column1],
    files = Table.SelectRows(Folder.Files(sourceFolder), each (Text.Lower([Extension]) = ".xlsm") and not(Text.StartsWith([Name], "~$"))),
    books = Table.TransformColumns(files[[Name],[Content]], {"Content", each Excel.Workbook(_, false){0}[Data]}),
    expanded = Table.ExpandTableColumn(books, "Content", {"Column1", "Column2"}),
    needed = Table.SelectRows(expanded, each List.Contains(names, [Column1])),
    pivoted = Table.Pivot(needed, names, "Column1", "Column2"),
    return = Table.RemoveColumns(pivoted, {"Name"})
in
    return
 
 
 
#12
23.01.2018 10:01:27
ОФФ Андрей VG, ну вы так сразу в омут с головой отправляете людей))
 
 
 
#13
23.01.2018 10:03:11
Так и надо. Поддерживаю. Выплывет - молодец! Не выплывет - ну, бывает :)
Вот горшок пустой, он предмет простой...
 
 
 
#14
23.01.2018 11:35:14
Спасибо всем. Изучаю варианты.
 
 
 
#15
24.01.2018 09:24:33
Цитата
Андрей VG: такой вариант
Андрей, спасибо, работает.
Цитата
StepanWolkoff: создаем функцию обработки файла
StepanWolkoff, спасибо, этим путём тоже получилось, (читал ваши подсказки и статью Николая в Приёмах - и сделал сам)

Цитата
PooHkrd: там группировать надо, а потом уже транспонировать.
PooHkrd, в вашем сообщении №9 эта строка кода:    
Код
#"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Столбец1"}, {{"Количество", each Table.Transpose(Table.SelectColumns(_,{"Столбец2"})), type table}}),
результат нажатия какой кнопки? вы писали, что нужно сначала группировать а потом транспонировать, а тут группируется уже транспонированная таблица или как?
Изменено: Бахтиёр - 24.01.2018 09:25:52
 
 
 
#16
24.01.2018 09:34:46
Это сначала нажимается кнопа "Группировка" и в качестве агрегатора выбирается вариант "Все строки". в качестве значений ячейки у вас получаются те самые таблицы, на которые разделяет исходный массив функция Table.Group. Потом к итератору _ я применяю две функции, сначала
Table.SelectColumns(_,{"Столбец2"}), которая оставляет в таблице только нужный столбец, после чего полученную таблицу транспонируем, и разворачиваем получившиеся столбцы.
Сразу предупреждаю, что данный прием работает только если заполнены все ячейки с данными, если будут пустые, то может нехорошо получиться, т.е. в таком случае лучше перед такой группировкой пустые ячейки заполнить хоть какими-то значениями, а потом уже их крутить.
Изменено: PooHkrd - 24.01.2018 09:35:36
Вот горшок пустой, он предмет простой...
 
 
 
#17
24.01.2018 09:57:24
PooHkrd, спасибо, получилось. Мой код полностью совпал с вашим.
 
 
 
Страницы: 1
Наверх