Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Nik139
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
15.04.2019 18:35:08
Добавил после развертывания следующие действия: транспонирование, удаление пустых строк, обратное транспонирование.

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

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

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

Меня все-таки мучает вопрос: откуда появляется такое большое количество пустых столбцов, если я урезал рабочую область во всех "собираемых файлах" 50 столбцами? Не собирает же PQ с листов данные за пределами рабочей области... Может, кто знает?
Изменено: Nik139 - 15.04.2019 18:35:40
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
14.04.2019 14:43:25
Хорошая идея! Буду пробовать
Цитата
artyrH написал: как быть если и вправду будут не только умные таблицы или просто листы с данными, а и то и другое(и умные таблицы и просто листы с данными
Это можно решить фильтрацией, выбирая в атрибуте "Kind" (появляется при развертывании файла Excel) тип "sheet".
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
14.04.2019 13:05:07
Справедливости ради, время обновления запроса - довольно условная величина. У меня комп - не новый и со средненькой конфигурацией. PQ существенно загружает его процессор и память. Если какие-то процессы в это время еще подключаются, то время обновления запроса тоже вырастет. Тем не менее, факт увеличения времени запроса с "умеренного" до "значительного" - это факт.
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
14.04.2019 12:31:47
Прошелся по всем файлам-источникам и принудительно сократил в них рабочую область.

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

Честно говоря, не представляю, за счет чего теперь происходит увеличение времени запроса.
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
14.04.2019 11:57:37
Спасибо, PooHkrd!
 
На файле с примером все сработало на ура!

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

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

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

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

Если все-таки попытаться все решить формулой, то надо, чтобы PQ определило для каждого разворачиваемого элемента Data фактический конец области с данными. Например, если есть 5 пустых столбцов подряд, то на этом список заканчивается. Можно ли это предусмотреть формулами?
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
13.04.2019 17:17:29
Обновил пример, добавив туда описанные выше решения:

- Сборка_01 - это исходный вариант, где PQ автоматом создает столбцы
- List_Union - это предложение из #2
- Сборка_02 - это решение из #6
- С запасом - это решение из #7
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
13.04.2019 16:49:38
В общем, пока хорошее решение еще не найдено, сделал промежуточный вариант: число столбцов выбирается с запасом. При необходимости, можно это число задавать в параметрической таблице.

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

Буду очень благодарен, если кто-нибудь предложит более корректный вариант.
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
13.04.2019 15:36:08
Сделал пример. В нем:
- папка с 2 бюджетами
- файл "развертывание", который собирает из папки данные.

В файле "развертывание" два запроса:- Сборка_01 - простое развертывание, фиксирующее столбцы;
- List_Union - развертывание с использованием формулы - вылезает ошибка.
 
Перейти    
PQ: Развернуть элемент Data, в котором меняется количество столбцов
 
#
13.04.2019 11:32:53
Спасибо за ответ!

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

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

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

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

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

Заранее благодарен.
 
Перейти    
Проблема при разделении столбца в Power Query, Если в ячейке разделителей больше, чем было при создании запроса, дополнительные столбцы не создаются.
 
#
29.03.2019 13:52:47
Большое спасибо за ответы!
У меня был и пример, и текст с описанием проблемы. Но почему-то они не отображены на форуме.

Из варианта от artyrH
Text.PositionOfAny дает номера позиций, которые разделитель занимает в строке, начиная с нуля
List.Count дает нам количество этих позиций, т.е. количество разделителей
К этому прибавляем 1 и получаем количество столбцов, на которые надо разбить исходный столбец
Такие значения по каждой строке помещаем в дополнительный столбец (виртуальный)
По данному столбцу рассчитываем максимальное значение с помощью List.Max
И уже это максимальное значение вставляем в Splitter.SplitTextByDelimiter в качестве числа столбцов.

В решении от Андрея Лящука создается пользовательская функция, в которой формируется список имен разделенных столбцов.

Еще раз большое спасибо!
 
Перейти    
Страницы: 1
Наверх