Как обновить запросы Power Query(и не только) в указанном порядке?
С выходом Power Query в массы все чаще стал появляться вопрос: как обновить запросы один за другим в заданном порядке?
В книге может быть десять и более запросов. И только 4 из них надо обновлять постоянно, да еще и в заданном порядке. Например, у нас есть с десяток запросов по продажам, отгрузкам и прочей аналитике, но только 4 из них нам надо обновить. При этом обновить надо в строгой последовательности:
- Вручную
- Кнопка Обновить все(Refresh All)
- Написать собственный порядок обновления через Visual Basic for Applications
Можно обновить запросы стандартными методами, поочередно вручную обновляя каждый запрос. Но это не всегда удобно, особенно если запросов более 5. Значит этот вариант не разбираем в деталях в принципе - все его плюсы и минусы очевидны.
Кнопка Данные
На самом деле, если мы имеем дело с Power Query, то правильнее всего выстраивать все взаимосвязи уже на стадии создания этих запросов в любом случае. Т.е. создавать их в правильной последовательности и "затягивать" данные в основной запрос в самую последнюю очередь, ориентируясь на результаты вспомогательных запросов. Это оптимальный вариант, т.к. в этом случае достаточно будет просто обновить нужный запрос, а Power Query сама определит какие вспомогательные запросы необходимо обновить для корректного результата. При этом встроенный механизм Power Query достаточно хорошо справляется с кешированием данных и даже если обновление происходит через кнопку Обновить все
Но здесь у нас опять же возникает проблема - если нам не надо обновлять все запросы в книге - как Power Query узнает, какие из них надо обновлять, а какие нет? Ответ - никак. Поэтому хоть и следует всегда стараться выстраивать цепочки взаимодействий запросов в "правильном" порядке - нашу проблему с обновлением только части запросов это никак не решит. В этом случае можно создать мастер запрос, в который просто через объединение запросов собирать все нужные. И обновление этого запроса должно инициировать обновление всех указанных в нем запросов со всеми их цепочками. Как создать подобный мастер запрос:
- идем на вкладку
Главная -Объединить -Добавить запросы :
- в появившемся окне выбираем "Три таблицы и больше"
- добавляем все нужные запросы
- нажимаем Ок
Все, запрос готов. Теперь достаточно обновить только его, чтобы все связанные запросы обновились. Правда, есть одна проблема: если данных много, то такой запрос их увеличит вдвое - ведь мы в один запрос собираем все остальные...
В решении через мастер-запрос есть одна проблема - запросы могут быть не только Power Query, но и более старых версий. Здесь вариантов вообще не остается - только Visual Basic for Applications. Да и в случае, если запросы уже созданы ранее кем-то еще или навыков создать мастер-запрос нет.
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Код обновляет поочередно указанные запросы в заданном порядке '--------------------------------------------------------------------------------------- Sub RefreshConnections() Dim oc, xQuery, aConnections, xConName Dim IsBG_Refresh As Boolean 'создаем перечисление всех запросов, которые надо обновить 'располагаем их в том порядке, в котором необходимо обновлять aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет") 'перебираем и обновляем все запросы поочередно For Each xConName In aConnections Set oc = ActiveWorkbook.Connections(xConName) Set xQuery = Nothing Select Case oc.Type Case Excel.XlConnectionType.xlConnectionTypeODBC Set xQuery = oc.ODBCConnection Case Excel.XlConnectionType.xlConnectionTypeOLEDB Set xQuery = oc.OLEDBConnection Case Else 'запрос выгружен в таблицу на листе If oc.Ranges.Count > 0 Then Set xQuery = oc.Ranges(1).QueryTable End If End Select 'непосредственно обновление запроса с ожиданием окончания обновления If Not xQuery Is Nothing Then 'запоминаем значение обновления в фоне для запроса IsBG_Refresh = xQuery.BackgroundQuery 'выставляем принудительно ждать завершения запроса xQuery.BackgroundQuery = False 'обновляем запрос xQuery.Refresh 'возвращаем обновление в фоне в первоначальное состояние xQuery.BackgroundQuery = IsBG_Refresh End If Next End Sub |
Чтобы использовать приведенный код для своих запросов достаточно в строке:
aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет") |
внутри
Код обновляет не только запросы Power Query, но и запросы более старых версий, выгруженных просто на лист.
Что делать, если
Да, вполне логичная ситуация, когда запросов много и переписывать имя каждого запроса не такая уж простая задача. Очень легко ошибиться в имени и тогда обновления не будет. Поэтому ниже я прилагаю код, который выводит на новый лист имена всех запросов:
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Код выводит на лист все запросы в текущей книге '--------------------------------------------------------------------------------------- Sub GetAllConnections() Dim ws As Worksheet Dim lr As Long Dim oc, aList() 'создаем массив для запоминания имен всех запросов ReDim aList(1 To ActiveWorkbook.Connections.Count, 1 To 1) For Each oc In ActiveWorkbook.Connections lr = lr + 1 'увеличиваем счетчик aList(lr, 1) = oc.Name Next 'создаем новый лист, в который записываем имена всех запросов Set ws = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1)) 'оформляем заголовок(для эстетики) ws.Cells(1, 1).Value = "Имя запроса" ws.Cells(1, 1).Font.Bold = True 'записываем имена всех запросов ws.Cells(2, 1).Resize(lr, 1).Value = aList End Sub |
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Спасибо большое за статью, очень помогла
Подскажите, а есть возможность часть запросов обновлять вместе, а часть по отдельности в одном макросе?
Ирина, если правильно понял вопрос - то возможно поможет следующее: открываете панель запросов(Данные -Запросы и подключения) -правая кнопка мыши на запросе -Свойства. Там есть галочка "Обновить это подключение по команде Обновить все". Для тех запросов, которые надо обновлять вместе - галочку оставляете. Для остальных - снимаете.
В итоге те запросы, которые надо обновить вместе можно обновлять кнопкой "Обновить все", а другие - в заданном порядке.
Только помните - если есть связанные запросы, то даже если галочка не установлена, а от этого запроса зависит результат другого запроса - он будет обновлен.