Страницы: 1
RSS
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
Помогите, пожалуйста, советом.

PQ собирает файлы Excel из папки. Затем, из каждого файла выбирает листы, в которых есть заданная метка. Эти листы формируют столбец с элементами Data. Далее разворачиваем элемент Data. При разворачивании PQ автоматически формирует список столбцов:
Скрытый текст

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

У меня в файле есть параметрическая таблица, в которой можно задать (с запасом) количество столбцов для вытягивания. А затем построить список столбцов заданного количества, который и использовать в формуле Table.ExpandTableColumn. Но это только идея - как формулой построить список столбцов я не знаю.

А, может, все это можно сделать и без параметрической таблицы?

Заранее благодарен.
 
попробуйте так
Код
    Custom1 = List.Union(List.Transform(#"Удаленные столбцы", [Объекты.Data], (x) => Table.ColumnNames(x))),
    #"Развернутый элемент Объекты.Data" = Table.ExpandTableColumn(#"Удаленные столбцы", "Объекты.Data",Custom1),
 
Спасибо за ответ!

Ругается на строку: Custom1 = List.Union(List.Transform(#"Удаленные столбцы", [Объекты.Data], (x) => Table.ColumnNames(x)))
Expression.Error: Неизвестный идентификатор. Использовалось ли условное обозначение [field] для _[field] вне выражения "each"?
 
Nik139, сделайте какой нибудь пример с запросом. так трудно сориентироваться
 
Сделал пример. В нем:
- папка с 2 бюджетами
- файл "развертывание", который собирает из папки данные.

В файле "развертывание" два запроса:- Сборка_01 - простое развертывание, фиксирующее столбцы;
- List_Union - развертывание с использованием формулы - вылезает ошибка.
 
В общем, пока хорошее решение еще не найдено, сделал промежуточный вариант: число столбцов выбирается с запасом. При необходимости, можно это число задавать в параметрической таблице.

Код
    Lnum=List.Numbers(1,50),    //Вторая цифра - число извлекаемых столбцов с запасом
    Cols=List.Transform(Lnum, each "Column"&Text.From(_)),
    #"Развернутый элемент Объекты.Data" = Table.ExpandTableColumn(#"Удаленные столбцы", "Объекты.Data", Cols),

Буду очень благодарен, если кто-нибудь предложит более корректный вариант.
 
Цитата
artyrH написал:
Nik139 , сделайте какой нибудь пример с запросом. так трудно сориентироваться
 
Обновил пример, добавив туда описанные выше решения:

- Сборка_01 - это исходный вариант, где PQ автоматом создает столбцы
- List_Union - это предложение из #2
- Сборка_02 - это решение из #6
- С запасом - это решение из #7
 
запятая была лишней
 
Большое спасибо!
В файле с примером это решение работает хорошо и быстро.

Однако в "большой сборке" (много файлов и много столбцов) не дает результата - бесконечно долго обновляет запрос...

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

К сожалению, такие варианты возможны. Обеспечить во всех файлах сокращение рабочей области, наверное, не получится. Также не получится все нужные таблицы преобразовать в формат "умных". Отдельные товарищи обязательно преобразуют их в обычный диапазон. Либо сделают не из шаблона... Запрос не должен из-за этого слетать.

Если все-таки попытаться все решить формулой, то надо, чтобы PQ определило для каждого разворачиваемого элемента Data фактический конец области с данными. Например, если есть 5 пустых столбцов подряд, то на этом список заканчивается. Можно ли это предусмотреть формулами?
 
Цитата
Nik139 написал:
если есть 5 пустых столбцов подряд, то на этом список заканчивается
к сожалению, я этого не смогу сделать, не умею
думаю, будут еще, как минимум, советы
скорее всего, в понедельник)
Изменено: artyrH - 13.04.2019 19:22:05
 
Nik139, названия файлов в итоговом массиве нужны?
Вот горшок пустой, он предмет простой...
 
PooHkrd,
названия файлов в итоговом массиве не нужны.
 
Тогда попробуйте так:
Код
#"Развернутый элемент Объекты.Data" = Table.Combine(#"Удаленные столбцы"[Объекты.Data])

Предыдущий способ тормозной для большого количества файлов, т.к в нем происходит многократное считывание этих файлов. Предложенный вариант такой фигней не занимается, а просто объединяет все таблицы из файлов без разбору. Если все таки имена файлов потребуются то в этой теме есть модифицированный вариант такого способа.
З.Ы. возможно [Объекты.Data] нужно написать вот так [#"Объекты.Data"] если код будет ошибку выдавать.
Изменено: PooHkrd - 13.04.2019 23:44:17
Вот горшок пустой, он предмет простой...
 
Спасибо, PooHkrd!
 
На файле с примером все сработало на ура!

А на "большой сборке" было следующее:
  • предварительный просмотр довольно быстро развернулся: создано 999+ столбцов (за исключением первых 50, все они – пустые).
  • возникло существенное торможение последующих операций с массивом данных  (фильтрация и unpivoting). PQ несколько раз вылетел.
  • хотя в окне предварительного просмотра и удалось дойти до финала (увидеть выполнение всех действий), полноценное обновление запроса снова затянулось (на 6-й минуте перестал ждать и отключил).
Беда в том, что в одном из файлов граница рабочей области стоит "где-то за горизонтом". В результате, формируется множество пустых столбцов, не нужных нам. Они тормозят процесс. Тогда получаются следующие варианты:
  • либо есть какая-то функция в PQ, которая позволяет игнорировать пустые столбцы - это было бы лучше всего - есть варианты?
  • либо надо сначала пройтись макросом по файлам в папке с исходниками и принудительно ограничить в них рабочую область (на большом объеме файлов, внутри которых много листов, из которых нам нужен только один, это может затянуться)
  • либо использовать топорные решения с ручным заданием количества столбцов, как я приводил выше, но мне так не нравится
  • либо импортировать не листы, а умные таблицы (но тогда надо проверять, что во всех файлах данные действительно лежат в формате умных таблиц)
Изменено: Nik139 - 14.04.2019 11:59:01
 
Прошелся по всем файлам-источникам и принудительно сократил в них рабочую область.

Это помогло, но почему-то не очень сильно. Запрос обновился, но это заняло целых 4 минуты (Развертывание с помощью Table.Combine, а затем серия манипуляций). Тогда как при "топорном" решении (выделение только 50 столбцов) - обновление запроса занимает всего 10 сек.

Честно говоря, не представляю, за счет чего теперь происходит увеличение времени запроса.
 
Справедливости ради, время обновления запроса - довольно условная величина. У меня комп - не новый и со средненькой конфигурацией. PQ существенно загружает его процессор и память. Если какие-то процессы в это время еще подключаются, то время обновления запроса тоже вырастет. Тем не менее, факт увеличения времени запроса с "умеренного" до "значительного" - это факт.
 
может обрабатывать функцией с транспонированием и удалением пустых строк
только как быть если и вправду будут не только умные таблицы или просто листы с данными, а и то и другое(и умные таблицы и просто листы с данными).
Изменено: artyrH - 14.04.2019 14:33:04
 
Цитата
Nik139 написал:
существенное торможение последующих операций с массивом данных  (фильтрация и unpivoting)
попробуйте перед этими операциями транспонировать таблицу, затем удалить пустые строки, затем снова транспонировать.
 
Хорошая идея! Буду пробовать
Цитата
artyrH написал: как быть если и вправду будут не только умные таблицы или просто листы с данными, а и то и другое(и умные таблицы и просто листы с данными
Это можно решить фильтрацией, выбирая в атрибуте "Kind" (появляется при развертывании файла Excel) тип "sheet".
 
Добавил после развертывания следующие действия: транспонирование, удаление пустых строк, обратное транспонирование.

Предпросмотр последующих действий по фильтрации и отмене свертывания просто "полетел".  

Но транспонирование тоже прилично ест ресурсы. В итоге, запрос с Table.Combine без транспонирования оказался в 2 раза быстрее, чем то же самое, но с транспонированием.

Вывод: транспонирование для удаления пустых строк и обратное транспонирование - не помогает сократить время.

Меня все-таки мучает вопрос: откуда появляется такое большое количество пустых столбцов, если я урезал рабочую область во всех "собираемых файлах" 50 столбцами? Не собирает же PQ с листов данные за пределами рабочей области... Может, кто знает?
Изменено: Nik139 - 15.04.2019 18:35:40
 
Цитата
Nik139 написал:
Не собирает же PQ с листов данные за пределами рабочей области... Может, кто знает?
собирает
F1 творит чудеса
Страницы: 1
Наверх