Добрый день! Прошу подсказать возможно ли ускорить выполнение запросов в 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, который в зависимости от нужд вставляется в запрос). Остальные запросы ссылаются на этот, отфильтрованный запрос. Пример основного запроса ниже. (пробовал оборачивать его в буфер, результат нулевой, даже хуже вроде бы, т.к. контейнеры эти загружаются просто в хлам сразу все).
Основной запрос
Код
//Journal
let
src = connection,
filter_level = Table.SelectRows(src, each Text.Contains([Уровень], "8") or Text.Contains([Уровень], "9")
or Text.Contains([Уровень], "10") or Text.Contains([Уровень], "11")
or Text.Contains([Уровень], "12") or Text.Contains([Уровень], "13")
or Text.Contains([Уровень], "14") or Text.Contains([Уровень], "15"))
in
filter_level
в. Ниже приложу код некоторых запросов, прошу соориентировать в какую сторону копать для оптимизации запроса. Уверен, что вопрос лишь в нехватке знаний и практики. Вероятно был выбран неправильный подход к сбору данных.
Несколько примечаний: 1. Т.к. в исходных данных 12 столбцов, а каждый конкретный запрос работает только с определенными столбцами, влияет ли это на скорость вычислений (ну допустим при группировке по Col9, Описателю и Col6 из 2 примера под спойлером? Если да, то возможно ли как то обращаться к отдельным столбцам таблицы из основного запроса или лучше использовать в таком случае list.generate/accumulate? Ускорит ли это каким-то образом выполнение запроса? 2. Попробовал импортировать в PBI Desktop, там как то быстрее по ощущениям работает (CPU меньше грузит и выполняется все в теч. 5-10 мин). Но надо в Excel, т.к. доп.софт никто ставить не будет.
Как вообще, подскажите схематично, какие best practices по сбору больших данных, как лучше такой объем данных собирать (желательно без костылей) с наименьшим кол-вом чтения источника, опыта пока не хватает. Спасибо.
Добрый день, что-то я всю голову сломал над этим. Подскажите пжл. как вычислить продолжительность по условию. То есть, на основе примера, надо высчитать продолжительность между State ГРУШИ (АКТ) и State ГРУШИ (НЕАКТ). Если после НЕАКТ снова появляется в списке ГРУШИ (АКТ), то отсчет продолжительности начинается заново. State необязательно последователен - может быть ГРУШИ (АКТ), потом опять ГРУШИ (АКТ) и потом ГРУШИ (НЕАКТ). В таком случае отсчет должен идти от первого ГРУШИ (АКТ) до ГРУШИ (НЕАКТ).
Здравствуйте, задача из этой темы усложнилась. Прошу помочь в решении. Прикладываю модифицированный пример, решение 1 части любезно предоставил surkenny В результирующей таблице должна отображаться информация согласно следующего алгоритма: 1. Отчетный период разделен на 10-минутные интервалы, начинающиеся в полночь (следует учесть что данные в исходной таблице при этом могут начинаться с любого времени, не обязательно с 00:00). Полагаю от этого видоизмениться текущий запрос. Возможно такое вообще? Если допустим начать отсчет периода не с начальной даты/времени, указанной в таблице, а с 00:00 2. Информация вводится (время в столбце duration суммируется), когда за 10-минутный период поступает 10 или более значений в столбце "Count" 3. Информация не вводится если по достижении каждого любого нового 10-минутного периода значений пришло менее 5. Время в столбце Duration сбрасывается. 4. Пиковое значение 10-минутки показывает информацию о периоде с максимальным кол-вом значений в текущем массиве данным (например за время в 32 минуты он ищет пиковую 10-минутку). Спасибо
Добрый день, нужно подсчитать кол-во (сумму столбца) вхождений (столбец "Count") за интервал времени в 10 минут. Подскажите пжл. как это реализовать. Спасибо. Пример во вложении
Здравствуйте, прошу помочь в редизайне таблицы. Есть таблица *.xlsx (получается от стороннего софта), очень неудобоваримая и огромная (см. пример Sheet1). Хотелось бы эти данные отображать как в примере на Sheet 2 (с помощью VBA наверное, или как проще?). --------------------- Планирую сделать шаблон в *xlsm, который будет запрашивать файл для редизайна и вставлять все на новый лист с помощью VBA, т.к. это не разовая операция, а на постоянной основе придется редизайнить эти таблицы. Но пока не очень понятно как выдергивать корректно нужные данные из текущей таблицы. Спасибо.
Здравствуйте, подскажите пжл. - нужно из данные из листа 1 сверить с данными на листе 2 (они в одной колонке, но строки могут быть разные). Если данные на листе 2 присутствуют, то в соседеней колонке на листе 1 ставим +, если нет - пусто. Что-то я пробовал через индекс и поискпоз, но не получилось. Выдает ошибку "spill". Пример загрузил.
Добрый день, есть обновляемый файл с данными (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
Здравствуйте. много разных тем про группировку, но похожего не нашел/не получилось применить на практике. Подскажите пжл., как сгруппировать и свернуть строки в заданном диапазоне при наличии определенного значения в ячейке, а при отсутствии развернуть. Группировать не обязательно, как вариант можно скрыть. Метод решения - VBA
Всем привет, подскажите формулу (желательно не массива) - нужно посчитать кол-во ячеек, содержащих определенный текс за интервал времени (например, год). Пример приложил. Вроде бы должно быть не сложно, но что-то жестко затупил. Спасибо.