Auto-updating Power Query Connection via VBA
I have a Power Query set in myexcel.xlsx. I set its connections’s properties as this and this.
I wrote a VBA code like the following
When I open the myexcel.xslx manually, the Power Query connection updates. But through VBA code it doesn’t. I should add I tested this with an old fashioned Excel Connection andit works fine through VBA code. But the problem is with Power Query connections. Any thoughts?
5 Answers 5
It is actually rather easy, if you check out your existing connections, you can see how the power query connection name starts, they’re all the same in the sense that they start with «Query — » and then the name. In my project, I’ve written this code which works:
This will refresh all your power queries, but in the code you can see it says:
This just means if the name of the connection, the first EIGHT characters starting from the LEFT and moving towards the RIGHT (the first 8 characters) equals the string «Query — » then.
- and if you know the name of your query, adjust the 8 to a number that will indicate the amount of characters in your query name, and then make the statement equal to your query connection name, instead of the start of all power query connections («Query — «).
I’d advise NEVER updating all power queries at once IF you have a large amount of them. Your computer will probably crash, and your excel may not have auto saved.
Знакомство с Power Query на примере транспонирования Таблицы Excel
Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую и сейчас можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.
В Excel 2016 Power Query уже встроен в ядро программы. Команды управления запросами находятся во вкладке Данные, в группе Скачать и преобразовать (в английском варианте Get & Transform).
Далее будем использовать привычное название Power Query.
На самом деле в Excel и раньше можно было импортировать данные. Для этого в той же вкладке Данные была и есть целая группа команд Получение внешних данных.
Однако их возможности и удобство использования сильно ограничены.
После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.
Power Query работает на специальном языке программирования под названием M, с помощью которого записываются последовательные шаги обработки данных. Однако есть и пользовательский редактор с кнопками, поэтому быть программистом не обязательно. Здесь уместна аналогия с записью обычных макросов. Включили запись, произвели действия, закончили запись. В любой момент запустили выбранный макрос.
Вкратце алгоритм работы Power Query таков:
1. импорт данных из выбранных источников данных
2. обработка полученных данных
Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.
На этапе обработки производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query является то, что все шаги записываются. Это дает возможность затем нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.
На последнем этапе запроса обработанные данные выгружаются в указанное место либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другой раз.
В качестве наглядного примера рассмотрим следующую задачу. Имеются данные, которые нужно транспонировать, то есть строки сделать столбцами, а столбцы строками.
В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.
Первый и самый быстрый способ – воспользоваться Специальной вставкой, поставив галочку напротив опции транспонировать.
Отличный вариант, но одноразовый. В смысле, нет никакой связи между результатом и источником. Поэтому при любом изменении данных все нужно повторить снова. Это минус.
Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.
Теперь при изменении данных в источнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера диапазона механизм перестает работать, т.к. транспонированный диапазон зафиксирован и не будет расширяться вслед за источником.
Итого получается, что мы не можем сделать динамическое транспонирование данных в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.
Транспонирование таблицы средствами Power Query
Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.
Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные – Скачать и преобразовать – Из таблицы.
Открывается окно редактирования Power Query.
Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:
1. Инструменты редактирования – лента, на которой находятся команды Power Query.
2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.
3. Запросы – скрываемая панель для навигации между запросами текущей книги.
4. Панель результата – место, где отображается результат обработки данных на этапе выделенного шага.
5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.
Выделив любой из шагов, мы увидим состояние данных на соответствующем этапе.
Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».
Из предыдущего рисунка видно, что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к источнику, а второй автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.
Вернемся к условию задачи. Нужно транспонировать вот эту таблицу.
Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица – Использовать первую строку в качестве заголовков – Использовать заголовки как первую строку.
Таблица с данными получит такой вид.
Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.
Таблица мгновенно изменяется.
Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица — Использовать первую строку в качестве заголовков либо через кнопку в верхнем левом углу от таблицы.
Получим конечный результат обработки.
Задача решена. Все шаги преобразования данных записаны и видны справа.
Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.
Если ее нажать, то результат загрузится на новый лист эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.
Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.
Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.
Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все.
Добавим в исходную таблицу данные о продажах во втором квартале.
А теперь обновим запрос.
Это просто праздник какой-то! (с).
Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.
Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командами управления снизу.
Можно вновь войти в редактирование запроса, удалить его и т.д. Эти же и некоторые другие команды появятся в контекстном меню после кликания по названию запроса правой кнопкой мыши.
Перечислим наиболее часто используемые среди них.
Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.
Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).
Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)
Дублировать – сделать копию выбранного запроса.
Другие команды не менее важны, но их рассмотрим в другой раз.
Панель Запросы книги можно закрыть или снова отобразить с помощью команды Данные – Скачать и преобразовать – Показать запросы.
Итак, мы узнали, что такое Power Query. На примере транспонирования данных увидели, насколько он облегчает и ускоряет работу в Excel.
Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности
Всем привет.
Наступил новый 2016 год, а значит пора обновить инструменты для упрощения скучной механической работы. Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:
1. Данные приходится собирать воедино из нескольких источников.
2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.
Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:
1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.
2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).
3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.
В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.
Выбор MySQL (или любой другой популярной базы данных) вполне очевиден — бесплатно, относительно просто, возможность работать с довольно большими базами данных без технических хитростей. В качестве примера будем использовать Amazon Web Services: дешево (в большинстве случаев используемый инстанс будет бесплатен для вас в течение 12 месяцев).
Итак, начнем (если у вас уже есть базы данных с готовыми данными, то можно сразу переходить к разделу с Excel):
1. Регистрируемся на AWS (если еще нет учетки), запускаем самый простой инстанс t2.micro и заходим на него по SSH. Можно посмотреть краткую инструкцию в прошлом посте habrahabr.ru/post/265383. Обратите внимание, что нам потребуется первый в списке вариант инстанса на Amazon Linux AMI. Необходимо выставить правила, разрешающие обращение к инстансу по нужным портам: 
В целях безопасности лучше выставлять ограничения на IP-адрес. Если у вас динамический IP, то это проблемная опция. Также иногда ограничение доступа к MYSQL по IP вызывает ошибку в Excel. Если выставить любой IP, то все работает.
2. Исполняем подряд команды, описанные в документации docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Нам нужна глава «Tutorial: Installing a LAMP Web Server on Amazon Linux». Запомните пароль, который вводите при выполнении команды «sudo mysql_secure_installation». Для удобства установите phpMyAdmin как описано в конце этой главы. Если будете копипастить из документации строчку «sudo sed -i -e ‘s/127.0.0.1/your_ip_address/g’ /etc/ht tpd/conf.d/phpMyAdmin.conf», то обратите внимание, что иногда при копировании в «httpd» появляется лишний пробел.
После этих действий на вашем инстансе должна открываться такая страница: 
3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи —> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим: 
4. Теперь создадим базу данных data: 
5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)): 
6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».
Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период: 
Отлично, данные получены. Посмотрим как получать их в Excel.
Использование Power Query для выгрузки данных в Excel
Power Query представляет собой надстройку, которая расширяет возможности Excel по выгрузке данных. Скачать можно тут www.microsoft.com/en-us/download/details.aspx?id=39379. Для работы с MySQL может потребоваться MySQL Connector и Visual Studio (предлагаются при установке из дистрибутива).
1. После установки выбираем MySQL: 
2. В качестве базы указываем ID нашего инстанса (как было в скрипте) ec2-. compute.amazonaws.com. База данных data. Для ввода логина выбираем «База данных»: 
3. В открывшемся окне дважды кликаем на таблицу seo и получаем: 
В этом окне можно управлять запросами, изменяя столбцы и количество строчек. Когда база данных небольшая, то это работает. Однако если размер данных превышает даже 20MB, то Excel на большинстве компьютеров просто повиснет от такого запроса. К тому же неплохо бы менять даты запроса или другие параметры.
Динамические запросы в Power Query можно делать с помощью встроенного языка M msdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:
1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению: 
2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу: 
3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить». 
Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.
Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки —> Параметры запроса —> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок. 
4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню. 
Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:
let
Source = MySQL.Database(«ec2-. compute.amazonaws.com», «data», [Query &Text.From(Таблица1<0>[Значение])&»‘ and endDate<='»&Text.From(Таблица1<1>[Значение])&»‘ and query like ‘%2015%’;»])
in
Source
В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.
5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные —> Подключения и нажимаем «Обновить все»: 
В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».
Как обновить запрос в power query
Давайте представим себе ситуацию, когда у нас есть таблица с информацией о задолженности клиентов в национальной валюте. Нашей задачей будет перевести суму этой задолженности в иностранную валюту (доллар США) по курсу на текущий день, сохранив при этом данные за предыдущие дни.
Исходные данные к этому заданию предоставлены в виде файла, что содержит в себе динамическую «умную» таблицу со списком должников предприятия и сумм их долга, которую мы заранее назвали «Задолженность»
Так как нам нужно переводить задолженность в доллары по курсу на текущую дату, давайте, создадим подключение к сайту НБУ. В новом файле Microsoft Excel (назовем его «Результат») на вкладке Данные выбираем Получить данные — Из других источников — Из Интернета.
В открывшемся окне указываем ссылку на справочное значение курса гривны к доллару США по состоянию на 12:00 нужной вам даты на сайте НБУ (https://bank.gov.ua/files/Kurs_dovid.xlsx) и нажимаем кнопку «ОК».
Дальше, в окне навигатора, выбираем нужный нам год и нажимаем кнопку «Преобразовать данные»
После нажатия на кнопку «Преобразовать данные» открывается редактор Power Query, в котором нам нужно будет немного отформатировать полученные данные:
- Удаляем верхнюю строку (Главная — Сократить строки — Удалить строки — Удаление верхних строк – Количество строк «1» — ОК);
- Задаем строку заголовков (Главная – в блоке «Преобразование» выбираем «Использовать первою строку в качестве заголовком»);
- Заменяем значение «-» (такое значение указывает на то, что просматриваемая дата – выходной) на курс за предыдущий день. Для этого, для начала, нам нужно изменить тип данных во втором столбце на «Десятичное число». В строках, где вместо курса доллара указано «-» теперь будет отображаться ошибка — «error».
Создаем новый столбец с помощью команды Добавление столбца – Настраиваемый столбец – в открывшемся окне задаем название нового столбца (в нашем случае «Курс») и в поле формул напишем конструкцию try [выбираем столбец, которые содержит в себе курс валют] otherwise null. С помощью такой формулы все значение, которые раньше были «-» заменятся зарезервированным в языке М словом null, которое дальше мы заменим на курс за предыдущий день.
Дальше, предварительно выделив только что созданный столбец, на вкладке Преобразование – в блоке Любой столбец нажимаем на кнопку Заполнить – Вниз.
4. Удаляем ненужные столбцы и меняем название запроса (например, Курс_НБУ).
5. На вкладке Главная – выбираем «Закрыть и загрузить» — Закрыть и загрузить в… — Только создать подключение.
Теперь давайте, подключимся к нашему исходному файлу, который содержит в себе сведения о задолженности клиентов (Данные – Получить данные – Из файла – Из книги – и указываем путь к нашему файлу.
В открывшемся окне выбираем нашу «умную» таблицу – «Задолженность» и нажимаем на клавишу – «Преобразовать данные». Теперь в редакторе Power Query нам нужно будет добавить столбец, в котором будет отображаться текущая дата. Для этого в главном меню выбираем Добавление столбца – Настраиваемый столбец. В качестве названия столбца укажем «Дата», а в поле формул воспользуемся функцией DateTime.LocalNow, которая выводит значение текущей даты.
Результатом нашей функции будет новый столбец, в котором отображены текущая дата и время, но так как нам нужна только календарная составляющая, давайте, изменим тип данных. Для этого нажмем в левом углу названия столбца и выберем тип данных «Дата».
Теперь мы можем к нашему запросу «Задолженность» добавить курс доллара на текущую дату. Чтобы объединить два запросы воспользуемся пунктом главного меню Главная – Объединить – Объединить запросы. В открывшемся окне указываем какой запрос будет добавлен к текущему (в нашем случае, таким запросом будет «Курс_НБУ). Дальше обозначаем общие для двух запросов столбцы (мы выбираем один столбец «Дата»). Так как запрос «Курс_НБУ» ссылается на внешний источник, а именно – сайт, у нас появиться окно «Уровни безопасности» — отмечаем поле «Пропустить проверку…» — Сохранить – и жмем ОК.
После выполнения вышеуказанных действий нам нужно выбрать, какой именно столбец из запроса «Курс_НБУ» нужно развернуть. Для этого жмем на кнопку «Развернуть» в правом углу названия появившегося столбца – выбираем нужный нам столбец – забираем отметку возле пункта «Использовать исходное имя столбца как префикс» — ОК.
Теперь можем разделить «Задолженость клиента, грн» на «Курс» и получить новый столбец — «Задолженость клиента, usd» и выгрузить полученную таблицу на лист Microsoft Excel (Главная – «Закрыть и загрузить» — Закрыть и загрузить в… — Имеющийся лист – ОК).
Дальше нам нужно создать ещё один запрос, который будет выводить на лист информацию, что была актуальной до обновления запроса (в нашем случае, такой информацией будут данные за предыдущие дни). Выделяем любую ячейку полученной нами таблицы после выгрузки запроса на лист Microsoft Excel и на вкладке Данные нажимаем на кнопку Из таблицы/диапазона (в новых версиях Microsoft Excel — С листа). В полученном запросе ничего не редактируем, только меняем название на «Пред_день» и сохраняем эго (Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение).
Теперь давайте, объединим оба запросы. Открываем запрос «Задолженность» на вкладке Главная выбираем Объединить – Добавить запросы и в открывшемся окне выбираем запрос «Пред_день» — ОК
Теперь можем возвращаться на лист Microsoft Excel (Главная — Закрыть и загрузить) и на следующий день попробовать обновить нашу таблицу (Данные – Обновить всё) проверив, подтянется ли новый курс доллара и получилось ли у нас сохранить данные за предыдущий период.