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

Страницы: 1
Оптимизация запросов в Power Query
 
#
29.06.2022 12:26:05
Добрый день!
Прошу подсказать возможно ли ускорить выполнение запросов в Power Query.
Незнаю с чего начать, но попробую описать суть проблемы. Я новичок в PQ. Заранее скажу, я уже перелопатил кучу статей, часть рекомендаций выполнена. Стало конечно быстрее, но не намного.
Исходные данные следующие:
1. Ноутбук с О365/х64. 16 Gb ОЗУ, Core i5
2. Данные получаю из исходных файлов (формат txt, гружу из папки). Изначально был формат mdb, почитал, написано текстовые быстрее, а т.к. исходные данные можно и в txt получить перешел на него. Файлов от 1 до 6, вес каждого 50-100 Мб. Суммарное кол-во строк, получаемое в итоге от выгрузки от 500 000, в зависимости от кол-ва файлов в папке). Все бы ничего, если бы файлов было 1 или два, но когда их допустим 6, ожидать выполнение можно, пожалуй, вечность. 500 к  строк, 12 столбцов. Пример ниже (создан через стандартный сэмпл).
Запрос подключения
3. Структура следующая:
а.  Всего в файле xlsm 36 запросов, в каждом из которых выполняются определенные вычисления с исходными данными. Все эти запросы надо выгружать на лист Excel (там тоже произвел настройки согласно рекомендациям - убрал галочки с авто изменения ширины и отключил с каждого запроса Enable Background Refreshed).
б. Организовано все следующим образом - вот код что выше, это я значит обрабатываю исходные данные. Эти данные никуда не выгружаются. Следующим запросом получаю исходные данные для динамической фильтрации (использую для этого код на VBA, который в зависимости от нужд вставляется в запрос). Остальные запросы ссылаются на этот, отфильтрованный запрос.  Пример основного запроса ниже. (пробовал оборачивать его в буфер, результат нулевой, даже хуже вроде бы, т.к. контейнеры эти загружаются просто в хлам сразу все).

Основной запрос

в. Ниже приложу код некоторых запросов, прошу соориентировать в какую сторону копать для оптимизации запроса. Уверен, что вопрос лишь в нехватке знаний и практики. Вероятно был выбран неправильный подход к сбору данных.
Пример запроса
Пример запроса
Пример запроса
Несколько примечаний:
1. Т.к. в исходных данных 12 столбцов, а каждый конкретный запрос работает только с определенными столбцами, влияет ли это на скорость вычислений (ну допустим при группировке по Col9, Описателю и Col6 из 2 примера под спойлером? Если да, то возможно ли как то обращаться к отдельным столбцам таблицы из основного запроса или лучше использовать в таком случае list.generate/accumulate? Ускорит ли это каким-то образом выполнение запроса?
2. Попробовал импортировать в PBI Desktop, там как то быстрее по ощущениям работает (CPU меньше грузит и выполняется все в теч. 5-10 мин). Но надо в Excel, т.к. доп.софт никто ставить не будет.

Как вообще, подскажите схематично, какие best practices по сбору больших данных, как лучше такой объем данных собирать (желательно без костылей) с наименьшим кол-вом чтения источника,  опыта пока не хватает.
Спасибо.
Изменено: creept - 29.06.2022 12:58:11
 
Перейти    
Вычисление продолжительности по условию, Power Query. Работа с Date/Time
 
#
15.06.2022 10:27:22
Добрый день, что-то я всю голову сломал над этим. Подскажите пжл. как вычислить продолжительность по условию.
То есть, на основе примера,  надо высчитать продолжительность между State ГРУШИ (АКТ) и State ГРУШИ (НЕАКТ). Если после НЕАКТ снова появляется в списке ГРУШИ (АКТ), то отсчет продолжительности начинается заново. State необязательно последователен - может быть ГРУШИ (АКТ), потом опять ГРУШИ (АКТ) и потом ГРУШИ (НЕАКТ). В таком случае отсчет должен идти от первого ГРУШИ (АКТ) до ГРУШИ (НЕАКТ).
 
Перейти    
Подсчет продолжительности и поиск пиковых значений, Power Query. Работа с Date/Time
 
#
07.06.2022 16:29:25
Здравствуйте, задача из этой темы усложнилась. Прошу помочь в решении.
Прикладываю модифицированный пример, решение 1 части любезно предоставил surkenny
В результирующей таблице должна отображаться информация согласно следующего алгоритма:
1. Отчетный период разделен на 10-минутные интервалы, начинающиеся в полночь (следует учесть что данные в исходной таблице при этом могут начинаться с любого времени, не обязательно с 00:00). Полагаю от этого видоизмениться текущий запрос. Возможно такое вообще? Если допустим начать отсчет периода не с начальной даты/времени, указанной в таблице, а с 00:00
2. Информация вводится (время в столбце duration суммируется), когда за 10-минутный период поступает 10 или более значений в столбце "Count"
3. Информация не вводится если по достижении каждого любого нового 10-минутного периода значений пришло менее 5. Время в столбце Duration сбрасывается.
4. Пиковое значение 10-минутки показывает информацию о периоде с максимальным кол-вом значений в текущем массиве данным (например за время в 32 минуты он ищет пиковую 10-минутку).
Спасибо
 
Перейти    
Подсчет количества вхождений за определенный интервал времени, Power Query
 
#
07.06.2022 13:43:02
Добрый день, нужно подсчитать кол-во (сумму столбца) вхождений (столбец "Count") за интервал времени в 10 минут. Подскажите пжл. как это реализовать.
Спасибо. Пример во вложении
 
Перейти    
Реорганизация данных в таблице, Перенос значений исходной таблицы в другую таблицу
 
#
27.05.2022 13:16:09
Здравствуйте, прошу помочь в редизайне таблицы.
Есть таблица *.xlsx (получается от стороннего софта), очень неудобоваримая и огромная (см. пример Sheet1).
Хотелось бы эти данные отображать как в примере на Sheet 2 (с помощью VBA наверное, или как проще?).
---------------------
Планирую сделать шаблон в *xlsm, который будет запрашивать файл для редизайна и вставлять все на новый лист с помощью VBA, т.к. это не разовая операция, а на постоянной основе придется редизайнить эти таблицы. Но пока не очень понятно как выдергивать корректно нужные данные из текущей таблицы.
Спасибо.
 
Перейти    
Поиск значения в столбце другого листа
 
#
01.07.2021 13:28:26
Здравствуйте, подскажите пжл. - нужно из данные из листа 1 сверить с данными на листе 2 (они в одной колонке, но строки могут быть разные). Если данные на листе 2 присутствуют, то в соседеней колонке на листе 1 ставим +, если нет - пусто. Что-то я пробовал через индекс и поискпоз, но не получилось. Выдает ошибку "spill". Пример загрузил.

Спасибо.
Изменено: vikttur - 01.07.2021 13:34:13
 
Перейти    
Сопоставление данных строк из разных файлов и копирование диапазона
 
#
12.03.2021 15:46:30
Добрый день, есть обновляемый файл с данными (Book1) и другой файл со статичными данными (Book2). Если значение в столбце А book1 совпадает с столбцом A в book2, то копируется вся строка из book2 в book1. Необходимо, чтобы при выполнении макроса из файла Book2 копировался диапазон ячеек (а не вся строка как сейчас, т.к. в Book1 появились еще столбцы) A:G.
Что-то вот тут не так, а на что поменять не догоню.
Код
For rw = 1 To 999
    If Dict.exists(Cells(rw, "A").Value) Then
      cellValue = Cells(rw, "A").Value
      
    Rows(rw & ":" & rw).Select
Спасибо.
 
Перейти    
Группировка строк по значению в ячейке
 
#
19.02.2021 00:39:47
Здравствуйте. много разных тем про группировку, но похожего не нашел/не получилось применить на практике. Подскажите пжл., как сгруппировать и свернуть строки в заданном диапазоне при наличии определенного значения в ячейке, а при отсутствии развернуть. Группировать не обязательно, как вариант можно скрыть.
Метод решения - VBA

Спасибо.
 
Перейти    
Подсчет значений за интервал времени
 
#
18.02.2021 14:14:57
Всем привет, подскажите формулу (желательно не массива) - нужно посчитать кол-во ячеек, содержащих определенный текс за интервал времени (например, год).
Пример приложил. Вроде бы должно быть не сложно, но что-то жестко затупил. Спасибо.
 
Перейти    
Страницы: 1
Наверх