Руководство по Power Query
Очистка данных и получение данных из нескольких источников — самая большая проблема для пользователей Excel. Чтобы облегчить все это, Microsoft представила «Power Query» для Excel. Итак, если вы новичок в Power Query, мы проведем вас через это руководство.
Power Query — это дополнительный инструмент, доступный для Excel начиная с версии Excel 2016 и более поздних версий, но вы можете использовать его для версий Excel 2013 и 2010 также в качестве надстройки. Когда мы получаем данные из внешних источников для Excel, данные будут в уродливом формате и почти не смогут работать с этими данными, мы знаем, что вы испытали это!
Но с помощью Power Query в Excel мы можем получить доступ, преобразовать и очистить все уродливые данные в удобочитаемый формат. Мало того, что Power Query можно использовать для написания запросов, мы можем повторно использовать эти запросы для получения обновленных данных, просто нажав кнопку «Обновить».
Со всеми вышеперечисленными функциями Power Query по-прежнему удобен для пользователя, и на самом деле вы можете изучить это намного проще, чем вы изначально изучили MS Excel. В отличие от VBA, для этого не обязательно иметь опыт работы с языком программирования.
Путь данных // Знакомство с Power Query // Power Query это ?
Заметка: В версии Excel 2016 запрос мощности доступен как «Получить и преобразовать» на вкладке «Данные», но в версиях Excel 2010 и 2013 он является надстройкой. Вы можете использовать эту ссылку, чтобы скачать Надстройка Power Query. Убедитесь, что вы загрузили версию (32-разрядную или 64-разрядную), соответствующую разрядной версии MS Excel. После установки вы должны увидеть это как отдельную вкладку.
Как работать с Power Query?
Работа с Excel Power Query — это просто удовольствие, потому что в нем есть удобные для пользователя параметры, а также в нем так много функций, что мы попытаемся привести здесь некоторые примеры.
Пример — импорт данных из текстового файла
Получение данных из текстового файла является обычным делом, и каждый столбец разделяется значением разделителя. Например, посмотрите на приведенную ниже таблицу данных.
Мы будем использовать мощный запрос, чтобы импортировать эти данные и преобразовать их в формат, с которым Excel любит работать. Шаг 1 — Перейдите на вкладку «Данные» и в разделе «Получить данные» нажмите «Из файла», затем нажмите «Из файла».
«Из текста / CSV ». 
Шаг 2 — Теперь он попросит вас выбрать файл, который вы хотите импортировать, поэтому выберите файл и нажмите «ОК».
Урок 1. Знакомство с Power Query в Excel 2016
Шаг 3 — Это отобразит предварительный просмотр данных перед их загрузкой в модель запроса, и это будет выглядеть так. 
Как вы можете видеть с правой стороны, у нас есть окно под названием «Запросы и соединения», поэтому это предполагает, что данные импортируются с помощью запроса мощности. Шаг # 5 — После загрузки данных в Excel подключенный текстовый файл не должен быть поврежден в Excel, поэтому перейдите в текстовый файл и добавьте две дополнительные строки данных.
Шаг # 6 — Теперь перейдите в Excel и выберите таблицу, и в ней появятся еще две вкладки как «Дизайн запросов и таблиц». 
Шаг # 7 — В разделе «Запрос» нажмите кнопку «Обновить», и данные будут обновлены с обновленными двумя новыми строками. 
- Здесь есть еще одна проблема, т.е. первая строка не фиксируется как заголовок столбца.
Шаг # 8 — Чтобы применить эти изменения, нажмите «Изменить запрос» на вкладке «Запрос».
- Это откроет вкладку редактора мощных запросов.
Вот где нам нужен запрос мощности.
Шаг # 9 — Чтобы сделать первую строку заголовком на вкладке HOME, щелкните «Использовать первую строку как заголовок».
- Таким образом, первая строка станет заголовком столбца, и мы можем увидеть это ниже.
Шаг № 10 — Нажмите на «Закрыть и загрузить»На вкладке ГЛАВНАЯ, и данные вернутся в превосходное состояние с измененными изменениями.
- Теперь в Excel у нас есть такие данные.
Не меняя фактического положения данных, запрос мощности изменил данные.
Введение в окно Power Query
Когда вы смотрите на окно запроса мощности, вы, должно быть, сбиты с толку, позвольте познакомить вас с окном запроса мощности.
- Лента — Это похоже на наши ленты MS Excel, под каждой лентой у нас есть несколько функций для работы.
- Список запросов — Это все таблицы, импортированные в эту книгу для улучшения результатов.
- Панель формул — Это похоже на нашу панель формул в Excel, но здесь это M-код.
- Предварительный просмотр данных — Это предварительный просмотр данных выбранной таблицы запроса.
- Свойства — Это свойства выбранной таблицы.
- Прикладные шаги — Это самый важный, здесь отображаются все примененные шаги в запросе мощности. Мы можем отменить действия, удалив запросы.
Это вводное руководство по превосходной модели Power Query, и у нас есть много других дел, связанных с Power Query, и мы увидим их в следующих статьях.
То, что нужно запомнить
- Power Query — это надстройка для версий Excel 2010 и 2013, поэтому ее необходимо устанавливать вручную.
- В версии Excel 2016 Power Query находится на вкладке «ДАННЫЕ» с названием «Получить и преобразовать данные».
Источник: voxt.ru
Что такое Power Query?
Power Query — это механизм преобразования данных и подготовки данных. Power Query поставляется с графическим интерфейсом для получения данных из источников и редактора Power Query для применения преобразований. Так как подсистема доступна во многих продуктах и службах, назначение, в котором будут храниться данные, зависит от того, где использовался Power Query. С помощью Power Query можно выполнять обработку данных для извлечения, преобразования и загрузки (ETL).
Схема с символизируемыми источниками данных справа, передавая power query для преобразования, а затем переход в различные места назначения, такие как Azure Data Lake Storage, Dataverse, Microsoft Excel или Power BI.
Как Power Query помогает с получением данных
Бизнес-пользователи тратят до 80 процентов времени на подготовку данных, что задерживает работу по анализу и принятию решений. Некоторые проблемы влияют на эту ситуацию, и Power Query помогает решить многие из них.
| Поиск и подключение к данным слишком сложно | Power Query позволяет подключаться к широкому спектру источников данных, включая данные всех размеров и фигур. |
| Возможности подключения к данным слишком фрагментированы | Согласованность взаимодействия и четность возможностей запросов во всех источниках данных. |
| Данные часто необходимо изменить перед потреблением | Высокоинтерактивный и интуитивно понятный интерфейс для быстрого и итеративного создания запросов по любому источнику данных любого размера. |
| Любое формирование является одноразовым и не воспроизводимым | При использовании Power Query для доступа к данным и преобразования необходимо определить повторяемый процесс (запрос), который можно легко обновить в будущем для получения актуальных данных. Если необходимо изменить процесс или запрос для учета базовых данных или изменений схемы, можно использовать тот же интерактивный и интуитивно понятный интерфейс, который использовался при первоначальном определении запроса. |
| Объем (размеры данных), скорость (скорость изменения) и разнообразие (широкий спектр источников данных и фигур данных) | Power Query позволяет работать с подмножеством всего набора данных для определения необходимых преобразований данных, что позволяет легко фильтровать и преобразовывать данные в управляемый размер. Запросы Power Query можно обновлять вручную или с помощью возможностей запланированного обновления в определенных продуктах (таких как Power BI) или даже программным способом (с помощью объектной модели Excel). Так как Power Query обеспечивает подключение к сотням источников данных и более 350 различных типов преобразований данных для каждого из этих источников, вы можете работать с данными из любого источника и в любой форме. |
Возможности Power Query
Взаимодействие с пользователем Power Query предоставляется через пользовательский интерфейс редактора Power Query. Цель этого интерфейса заключается в том, чтобы помочь вам применить необходимые преобразования, просто взаимодействуя с понятным набором лент, меню, кнопок и других интерактивных компонентов.
Редактор Power Query — это основной интерфейс подготовки данных, где можно подключаться к широкому спектру источников данных и применять сотни различных преобразований данных, предварительно просматривая данные и выбирая преобразования из пользовательского интерфейса. Эти возможности преобразования данных являются общими для всех источников данных, независимо от ограничений базового источника данных.
При создании нового шага преобразования, взаимодействуя с компонентами интерфейса Power Query, Power Query автоматически создает код M, необходимый для преобразования, чтобы не писать код.
В настоящее время доступны два интерфейса Power Query:
- Power Query Online — в интеграции, таких как потоки данных Power BI, потоки данных Microsoft Power Platform, потоки данных Фабрики данных Azure и многое другое, предоставляющее возможности через веб-страницу.
- Power Query for Desktop — в интеграции, таких как Power Query для Excel и Power BI Desktop.
Хотя существуют два интерфейса Power Query, они оба обеспечивают практически одинаковый пользовательский интерфейс в каждом сценарии.
Преобразования
Подсистема преобразования в Power Query включает множество предварительно созданных функций преобразования, которые можно использовать с помощью графического интерфейса редактора Power Query. Эти преобразования могут быть такими же простыми, как удаление столбца или фильтрации строк или как обычное использование первой строки в качестве заголовка таблицы. Существуют также дополнительные параметры преобразования, такие как слияние, добавление, группировка по, сводка и отмена сводных данных.
Все эти преобразования становятся возможными, выбрав параметр преобразования в меню, а затем применив параметры, необходимые для этого преобразования. На следующем рисунке показаны некоторые преобразования, доступные в редакторе Power Query.
Потоки данных
Power Query можно использовать во многих продуктах, таких как Power BI и Excel. Однако использование Power Query в продукте ограничивает его использование только этим конкретным продуктом. Потоки данных — это не зависящая от продукта версия службы power Query, которая работает в облаке. С помощью потоков данных можно получать данные и преобразовывать данные таким же образом, но вместо отправки выходных данных в Power BI или Excel выходные данные можно хранить в других вариантах хранения, таких как Dataverse или Azure Data Lake Storage. Таким образом, можно использовать выходные данные потоков данных в других продуктах и службах.
Язык формул Power Query M
В любом сценарии преобразования данных существуют некоторые преобразования, которые невозможно сделать наилучшим образом с помощью графического редактора. Для некоторых из этих преобразований могут потребоваться специальные конфигурации и параметры, которые графический интерфейс в настоящее время не поддерживает. Подсистема Power Query использует язык скриптов в фоновом режиме для всех преобразований Power Query: языка формул Power Query M, также известного как M.
Язык M — это язык преобразования данных Power Query. Все, что происходит в запросе, в конечном итоге записывается в M. Если вы хотите выполнить расширенные преобразования с помощью обработчика Power Query, можно использовать расширенный редактор для доступа к скрипту запроса и его изменения по мере необходимости. Если вы обнаружите, что функции и преобразования пользовательского интерфейса не будут выполнять точные изменения, используйте расширенный редактор и язык M для точной настройки функций и преобразований.
Где можно использовать Power Query?
В следующей таблице перечислены продукты и службы Майкрософт, где можно найти Power Query.
| Excel для Windows | Да | Да | Нет | Нет |
| Excel для Mac | Да | Нет | Нет | нет |
| Power BI | Да | Да | Да | Да |
| Power Apps | Да | Нет | Да | Да |
| Power Automate | Да | Нет | Да | нет |
| Сервер отчетов Power BI | Да | Да | Нет | Нет |
| Фабрика данных Azure | Да | Нет | Да | Да |
| SQL Server Integration Services | Да | Нет | Нет | Нет |
| службы SQL Server Analysis Services | Да | Да | Нет | Нет |
| Dynamics 365 Customer Insights | Да | Нет | Да | Да |
| 1M engine | Базовый механизм выполнения запросов, который выполняет запросы, выраженные на языке формул Power Query («M»). |
| 2 Power Query Desktop | Интерфейс Power Query, найденный в классических приложениях. |
| 3 Power Query Online | Интерфейс Power Query, найденный в приложениях веб-браузера. |
| 4Dataflows | Power Query как услуга, которая работает в облаке и не зависит от продукта. Сохраненный результат можно использовать в других приложениях в качестве служб. |
Источник: learn.microsoft.com
Power Query. Знакомство с Power Query
В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.
Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).
Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.
Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:
Первый столбец — это дата прохождения теста. 2 следующих столбца — это информация о сотруднике и точке продаж. Далее множество столбцов с ответами на вопросы, а вопрос находится в заголовках столбцов. Ваша задача — получить такую таблицу:
Для каждого сотрудника здесь указано количество ответов, количество верных ответов, результат теста и перечень вопросов с неверными ответами.
В Excel эта задача решается долго и тяжело. Если тесты происходят, например, раз в неделю, то каждую неделю вам придется тратить уйму времени на обработку файлов. Более того, чтобы решить эту задачу стандартными возможностями Excel нужен достаточно высокий уровень владения.
Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».
Решение
Запуск Power Query
Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.
Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx?id=39379 и нажмите «Скачать». Потом отметьте галочкой файл для вашей разрядности операционной системы (64 или 32 бит). После скачивания файла запустите установку.
Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.
Добавить кнопки Power Query на панель быстрого доступа
Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:
- Изменить запрос
- Запросы и подключения
- Запустить редактор запросов
Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.
Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.
Предварительные настройки Power Query
Откройте редактор запросов любым удобным способом:
- При помощи созданной кнопки в панели быстрого доступа
- Вкладка Данные — Группа Получить и преобразовать данные —Получить данные — Запустить редактор запросов
В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:
Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».
Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».
Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.
Создаем первый запрос Power Query
Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:
- Запускаем редактор запросов
- Вкладка Главная — Создать источник — Другие источники — Интернет
- Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
- Нажимаем Подключение
- Слева указываем нужную таблицу и жмем ОК
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!
Power Query разное
| 1 | Power Query. Знакомство с Power Query | В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос |
| 2 | Power Query. Подключение XML | В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel. |
| 3 | Power Query. Уникальные значения двух столбцов | В этом уроке мы получим уникальные значения из двух столбцов таблицы. |
| 4 | Power Query. Импорт таблиц PDF | Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет. |
| 5 | Power Query. Собрать разбитую строку | В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала. |
| 6 | Power Query. Пивот со счетом | В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы. |
| 7 | Power Query. Минимальное значение в диапазоне | В этом уроке мы найдем минимальное значение в диапазоне строк. |
| 8 | Power Query. Нарастающий итог 2 | В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query. |
| 9 | Power Query. Нарастающий итог 3 | В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query. |
| 10 | Power Query. Прирост населения Китая | В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет. |
| 11 | Power Query. Повторяющиеся значения в строке | В этом уроке разберем как определить есть ли в строке повторения. |
| 12 | Power Query. Таблица навигации по функциям М | В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query. |
| 13 | Power Query. Удалить запросы и модель данных из книги | Разберем как быстро удалить все запросы и модель данных из текущей книги. |
| 14 | Power Query. Открыть еще 1 Excel и еще 3 трюка | В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query. |
| 15 | Power Query. Подключиться к ZIP архиву | Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве. |
| 16 | Power Query. Импорт Word | Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками. |
| 17 | Power Query. Фильтрация списком | В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров. |
| 18 | Power Query. Пользовательская функция Switch | В этом уроке мы создадим пользовательскую функцию Switch. |
| 19 | Power Query. Информация о формате, Чтение zip | В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query. |
| 20 | Power Query. Импорт данных из gz | В этом уроке мы разберем как импортировать файл в формате gz. |
| 21 | Power Query. Удалить лишние пробелы, Text.Split | В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы. |
| 22 | Power Query. Параметры в SQL-запросе | Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel. |
| 23 | Power Query. Параметры в SQL-запросе 2 | Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать. |
| 24 | Power Query. Добавить столбец в каждую таблицу табличного столбца | В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца. |
| 25 | Power Query. Интервальный просмотр 1 (ВПР 1) | Объединить 2 таблицы с интервальным просмотром = 1. |
| 26 | Power Query. Относительный путь к файлу и папке | Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере. |
| 27 | Power Query. Нарастающий итог в каждой категории | Применим функцию нарастающего итога не ко всей таблице, а к определенному окну. |
| 28 | Power Query. ВПР без Merge или Join | Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц. |
Источник: comrade-xl.ru
О том, что такое надстройка «Power Query».
В данной статье я расскажу Вам о возможностях надстройки «Power Query».
Насколько мне известно, эта чудесная надстройка была разработана сторонними программистами (не Microsoft) несколько лет назад для программы «Эксель» версий 2013 и выше. В дальнейшем надстройка была приобретена компанией Microsoft и уже в 2016 офисе инструменты «Power Query» стали неотъемлемой частью панели инструментов «Excel»
Для чего нужна и что может надстройка «Power Query»?
«Power Query» помогает пользователям формировать запросы по аналогии с базами данных MS «Access». Что в конечном итоге дает возможность формирования таблиц, в котороые информация загружается из разных источников и выстраивается в нужном порядке.
Например, можно создать таблицу, в которую будет подгружаться информация из файла «Excel» со списком городов и загружаться информация с температурой воздуха из сайта с прогнозом погоды. Удобство состоит в том, что при обновлении таблицы к перечню городов будет добавляться информация с актуальной на сегодня погодой в реальном времени.
Еще одно значительное преимущество «Power Query» в том, что таблицы, сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия похож на связанные таблицы «Excel».
Возможности «Power Query» «Excel».
Возможность загрузки данных в таблицу «Excel» из множества различных источников:
- Интернет;
- Файлы форматов: XML, CSV, таблицы Excel, текстовые документы;
- Из баз данных: SQL, Oracle, Access, IBM DB, Mysql, Sybase и т.д.;
- Из Azure;
- Из веб служб и интерфейсов: MS Exchange Online, облачных хранилищ;
Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям.
Выполнять поиск по каталогам.
Автоматически «собирать» и обновлять таблицу из нескольких расположенных в одной папке.
Где скачать надстройку «Power Query» для «Excel 2013».
Скачивается надстройка «Power Query» с официального сайта Microsoft по ссылке приведенной ниже:
Перед установкой следует обратить внимание на требования к системе ПК.
Автор Master Of Exc Опубликовано 22.03.2018 22.03.2022 Рубрики Готовые решения и файлы
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Источник: ruexcel.ru
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Четыре занимательные вещи, которые я могу сделать с помощью Power Query для Excel
Опубликовано 22.07.2013 Автор Ренат Лотфуллин
Microsoft Power Query для Excel – это новая надстройка, которая обеспечивает удобный поиск, трансформацию и обновление данных для информационных работников, дашборд профессионалов и других пользователей.
Power Query дает нам новую вкладку в ленточном интерфейсе Excel, где мы сможем импортировать, преобразовывать и объединять данные из различных источников. В дополнение к стандартным источникам данных, таких как Microsoft Access, SQL и текстовым файлам, Power Query позволяет импортировать из Active Directory, Azure, OData и Hadoop.
Power Query – это бесплатная надстройка для Excel 2010 и Excel 2013, которую можно скачать здесь.
Обратите внимание, что Power Query ранее был известен под кодовым именем “Data Explorer”. Аналогично, Power Map ранее была известна как проект “GeoFlow”.
В сегодняшней статье, я покажу вам несколько примеров, которые можно сделать с помощью надстройки Power Query.
1. Основы использования Power Query
После установки надстройки, вы увидите новую вкладку Power Query на ленте Excel.
В группе Get External Data в левой части вкладки можно выбрать источники данных. В этом первом примере я выберу From Web и введу интересующий меня сайт.
Через несколько секунд, Excel активирует окно запроса с, найденными на сайте, таблицами. Вы можете щелкнуть по любой из них, чтобы посмотреть, как выглядят данные. Метод импорта данных из интернета мы уже рассматривали в одной из предыдущих статей.
Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка.
Обратите внимание на строку формул сверху. Эта строка содержит синтаксис, необходимый для запуска шагов, которые вы выбрали. Выдвижная панель Steps позволяет просматривать или удалять любые действия, которые были применены к запросу. Вы также можете вручную менять формулу, но об этом немного позже.
После того, как запрос будет настроен, можно вернуть данные на рабочий лист Excel. Как вы уже могли догадаться, чтобы обновить данные, щелкните правой кнопкой мыши по таблице и выберите Обновить.
2. Работа с каналами данных
Инструмент Power Query имеет механизм, поиска данных в интернет. К примеру, вам захотелось посмотреть календарь соревнований зимних Олимпийских игр в Сочи. Для этого вы можете воспользоваться Power Query.
Щелкаем по кнопке Online Search, в появившемся диалоговом окне вводим поисковый запрос Sochi. Результатом работы поисковой машины Excel станет список статей, где встречается слово Sochi. Если мы щелкнем по любому из них, данные появятся на рабочем листе.
3. Получаем список файлов в папке
Появлялась ли когда-нибудь у вас нужда выгрузить список файлов в папке? У меня да. И для этого я писал отдельный макрос. Ну, это дела давно минувших дней, теперь Power Query позволяет сделать тоже самое в несколько щелчков.
Выбираем From File в группе Get External Data и определяем папку, из которой мы хотим извлечь список.
Excel активирует окно запроса, где будет виден список всех файлов в папке.
К тому же вы можете добавить колонки с дополнительными атрибутами файла, щелкнув по иконке, находящейся справа от заголовка столбца Attributes.
После подтверждения изменений, мы имеем обновляемую таблицу со списком всех файлов в указанной папке. Не уверен, что Power Query был предназначен именно для такой выгрузки, но я считаю эту функцию очень удобной.
4. Консолидация данных, или как создать одну таблицу из нескольких Excel файлов
Как я уже упоминал ранее, каждое действие в Power Query создает некую формулу, которая описывает новый шаг в процессе извлечения данных.
Чтобы открыть окно с формулами запроса, щелкните по иконке в виде листочка в окне запроса:
Изначально синтаксис выглядит безнадежно непроницаемым. Но немного поигравшись с Power Query, вы поймете, как он работает. Ключ в том, что необходимо сделать какие-либо действия в запросе, а затем посмотреть, как изменится код. Через некоторое время вы сможете создавать собственные запросы для выполнения сложных действий.
Вы также можете посетить страницу формул Power Query для большего понимания.
Для примера предположим, что мне необходимо получить данные с двух файлов Excel и поместить их в одну таблицу. Структура файлов одинакова (в каждом одинаковые колонки).
Банальный копипэйст меня не устраивает, так как эту операцию необходимо выполнять еженедельно. Требуется что-то более автоматизированное. Нам поможет Power Query, с помощью которого мы сможем написать запрос для получения данных.
Для начала необходимо включить расширенные возможности редактирования запросов, во вкладке Power Query в группе Machine Settings щелкаем по кнопке Options -> Enable Advanced Query Editing.
Теперь мы готовы писать запрос. В группе Get External Data выбираем From Other Sourses -> Blank Query.
Данная опция позволяет мне напрямую ввести необходимый код. В данном случае я скомбинировал таблицы обоих файлов, при этом первую строку использовал как заголовки.
После подтверждения всех изменений, Excel выдаст консолидированную таблицу с данными обоих файлов. И самое замечательное во всем этом – это возможность обновления в любое время.
Важная мысль: если вы отправите файл содержащий запрос Power Query кому-то у кого он не был установлен, этот пользователь увидит данные таблицы, но не сможет их обновить.
Вам также могут быть интересны следующие статьи
- Пошаговая инструкция по созданию надстройки Excel для ленты
- Надстройка Inquire в excel 2013 — сравнение, анализ и связь файлов Excel
- Приложения для Excel — Apps for Excel: установка, использование, удаление
- Поиск решений в Excel — пример использования сервиса поиск решений в Excel
- Создание простейшего дашборда с помощью надстройки PowerView в Excel
- Визуализация географических данных с помощью надстройки GeoFlow
7 комментариев
Добрый день Ренат. Спасибо за информацию. Просьба, не хотели бы вы посвятить одну из своих тем — использование надстройки NodeXL. Спасибо.
Спасибо за идею, думаю в ближайшем будущем можно будет ожидать статью на тему надстройки NodeXL в Excel
Здравствуйте, не сталкивались ли с такой проблемой. При выгрузке списка из SharePoint Online через данную надстройку никаких таблиц не выгружается. С чем может быть связана это проблема?
К сожалению, с SharePoint не работал, не могу ответить
вы описали пример как подкачать данные из двух файлов.
А как настроить, чтобы данные закачивались из всех файлов определенной папке?
Алексей, для описания этого действия потребуется отдельная статья) Вот хорошее описание того, что вам необходимо datapigtechnologies.com
Источник: exceltip.ru