Как настроить power query excel 2016

Power Query. Введение. Знакомство с Power Query

Описание

В этом уроке вы узнаете:

• Зачем нужен Power Query
• История Power Query
• Как установить Power Query в Excel
• Зачем нужен 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. Выполняем следующие действия:

1. Запускаем редактор запросов
2. Вкладка Главная — Создать источник — Другие источники — Интернет
3. Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
4. Нажимаем Подключение
5. Слева указываем нужную таблицу и жмем ОК

Перед вам появится следующее окно редактора запросов Power Query:

Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.

Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.

По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.

Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.

Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.

Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!

Источник

О Power Query в Excel

С помощью Power Query (в предыдущих версиях Excel & преобразование данных) можно импортировать или подключиться к внешним данным, а затем сформировать их, например удалить столбец, изменить тип данных или объединить таблицы в нужном виде. Затем вы можете загрузить запрос в Excel, чтобы создавать диаграммы и отчеты. Периодически обновляйте данные, чтобы обновить их.

Power Query можно использовать в четыре этапа.

Подключение Подключение к данным в облаке, в службе или локально

Преобразование Данные фигуры должны соответствовать вашим потребностям, а исходный источник остается без изменений

Объединить Интеграция данных из нескольких источников для получения уникального представления данных

Загрузить Заполните запрос и загрузите его на таблицу или в модель данных и периодически обновляйте его.

Рассмотрим каждый этап более подробно. В заключительном разделе содержится история работы Power Query в Excel. Общие сведения по всем темам справки по Power Query см. в справке Power Query для Excel.

С помощью Power Query можно импортировать данные в один источник данных, например в книгу Excel, или в несколько баз данных, веб-каналов или служб, расположенных в облаке. К источникам данных относятся данные из Интернета, файлов, баз данных, таблиц Azure и даже таблиц Excel в текущей книге. С помощью Power Query вы сможете собрать все эти источники данных, используя собственные уникальные преобразования и сочетания для анализа информации, которая в противном случае была бы не видна.

После импорта вы можете обновить данные, чтобы внести в них дополнения, изменения и удалить их из внешнего источника. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel».

Преобразование данных означает внесение в них изменений в зависимости от требований к их анализу. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций — это преобразование данных. Этот процесс применения преобразований (и объединения) к одному или несколько наборам данных также называется формированием данных.

Это можно обдумать так. Вазу начинается как единое обилие, которое превратилось в одну фигуру в практический и красивый. Данные одинаковы. Она должна формироваться в таблицу, подходящую для ваших потребностей и которая позволяет использовать привлекательные отчеты и панели мониторинга.

Power Query использует выделенное окно, называемое редактором Power Query, для упрощения и отображения преобразований данных. Чтобы открыть редактор Power Query, выберите редактор запросов запуска в группе «Получить данные» в группе «Пуск & Преобразование данных», но он также откроется при подключении к источнику данных, создании запроса или загрузке запроса.

Редактор Power Query отслеживает все ваши работы с данными, записывая и пометив каждое преобразование или шаг, которые вы применяйте к данным. Независимо от того, является ли преобразование подключением к данным, удалением столбцов, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе APPLIED STEPS области «Параметры запроса».

В пользовательском интерфейсе можно внести множество преобразований. Каждое преобразование записуется в качестве шага в фоновом режиме. Вы даже можете изменить и написать собственные действия с помощью языка Power Query M в расширенный редактор.

Все преобразования, которые применяются к вашим подключениям к данным, в совокупности образуют запрос, который представляет исходный (без изменений) источник данных. При обновлении запроса каждый шаг выполняется автоматически. Запросы заменяют необходимость вручную подключать и формировать данные в Excel.

Вы можете объединить несколько запросов в книге Excel путем их слияния или объединения. Операции «Приложение» и «Слияние» выполняются с любым запросом в виде таблицы и не зависят от источников данных.

«Приложение» При операции создания запроса создается новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Операции с приложением можно выполнять двумя типами:

Промежуточная досье Создает новый запрос для каждой операции.

Inline Append Данные будут сданы в существующем запросе, пока вы не дойдете до конечного результата.

Слияние Операция слияния создает новый запрос на основе двух существующих запросов. Этот запрос содержит все столбцы главной таблицы, один из них служит ссылкой на связанную таблицу. Связанная таблица содержит все строки, которые соответствуют каждой строке из общего значения столбца в главной таблице. Более того, столбцы из связанной таблицы можно расширить или добавить в главную.

Существует два основных способа загрузки запросов в книгу:

В редакторе Power Query можно использовать команды «Закрыть» и «Загрузить» в группе «Закрыть» на вкладке «Главная».

В области «Запросы книги Excel» («Выбор запросов и & подключений») щелкните запрос правой кнопкой мыши и выберите «Загрузить в».

Кроме того, вы можете настроить параметры загрузки в диалоговом окне «Параметры запроса» (выберите «Параметры файла> и параметры > Параметры запроса»), чтобы выбрать способ просмотра данных и место их загрузки на компьютере или в модель данных (реляционный источник данных из нескольких таблиц, которые находятся в книге).

Интеграция данных & (power Query) с Excel за годы внося изменения.

Excel 2010 и 2013 для Windows

В Excel 2010 для Windows мы впервые представили Power Query, и он был доступен как бесплатная надстройка, которую можно скачать здесь: скачайте надстройки Power Query. После включения функции Power Query были доступны на вкладке Power Query на ленте.

Мы обновили Power Query, чтобы сделать его основным в Excel для импорта и очистки данных. Вы можете получить доступ к мастерам и инструментам импорта данных Power Query в группе «& Преобразовать данные» на вкладке «Данные» ленты Excel.

В этом режиме были улучшены функции импорта данных, переустановка команд на вкладке «Данные», новая боковая & запросы и возможность формировать данные с помощью сортировки, изменения типов данных, разделения столбцов, агрегирования данных и так далее.

Этот новый опыт также заменил старые мастеры импорта данных, которые были заменены командой «Данные» в группе «Внешние данные». Однако доступ к ним по-прежнему можно получить в диалоговом окне «Параметры Excel» (выберите «Параметры файла> Параметры > Данные> Показать устаревшие мастеры импорта данных).)

Excel 2016 и 2019 для Windows

Мы добавили тот же & » Get & Transform Data» с учетом технологии Power Query, что и в Microsoft 365.

Excel для Microsoft 365 для Mac

В 2019 г. мы начали работу по поддержке Power Query в Excel для Mac. С этого момента мы добавили возможность обновлять запросы Power Query из TXT-, CSV-, XLSX-, JSON- и XML-файлов. Мы также добавили возможность обновления данных с SQL сервера и из таблиц & диапазонов в текущей книге.

В октябре 2019 г. мы добавили возможность обновлять существующие запросы Power Query и использовать VBA для создания и изменения новых запросов.

В январе 2021 г. мы добавили поддержку обновления запросов Power Query из источников OData и SharePoint.

Примечание Power Query не поддерживается в Excel 2016 и Excel 2019 для Mac.

Депрекация каталога данных

С помощью каталога данных можно просмотреть общие запросы, а затем выбрать их для загрузки, редактирования или использования в текущей книге. Эта функция постепенно была неподготовлена к:

1 августа 2018 г. мы прекратили передачу новых клиентов в каталог данных.

3 декабря 2018 г. пользователи не могли делиться новыми или обновленными запросами в каталоге данных.

4 марта 2019 г. каталог данных перестал работать. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами каталога данных, используя параметр «Открыть» в области задач «Запросы моего каталога данных».

Отключение надстройки Power Query

В начале 2019 г. мы официально не добавили надстройки Power Query, необходимую для Excel 2010 и 2013 для Windows. Во время пользования надстройка может быть вам предоставлена, но впоследствии она может измениться.

Неавторяемая соединитела данных Facebook

Импорт и обновление данных из Facebook в Excel прекращены в апреле 2020 г. Все подключения к Facebook, созданные до этой даты, больше не работают. Мы рекомендуем как можно скорее исправить или удалить существующие запросы Power Query, которые используют соединител Facebook, чтобы избежать непредвиденных результатов.

Источник