Удалить power query из excel

Обновлено: 05.07.2024

Управлять запросами можно в области "Запросы" редактора Power Query или на вкладке Запросы в области "Запросы & подключения" Excel. Это особенно полезно, если у вас много запросов и вы хотите быстро найти их. После того как вы найдете запрос, можно сделать ряд дополнительных действий, таких как дублирование, ссылка, слияние, приложение, совместное действие и удаление запроса.

При создании запроса сведения о подмыве создаются за кадром. Роль запроса заключается в формировании данных. Роль сведений о под соединении заключается в сохранении сведений об источнике данных и их обновлении в соответствии с настройками обновления, такими как частота.

Не все запросы могут быть загружены на таблицу. Это может произойти при импорте данных из внешнего источника, моделировании данных в редакторе Power Query, на домашней> Загрузитьв , а затем в диалоговом окне Импорт данных поместить запрос в модель данных или файл подключения к данным Office (ODC).

Ниже перечислены команды, которые можно использовать в том порядке, в котором они отображаются в соответствующих контекстных меню на вкладке Запросы в области "Запросы & Подключение" в Excel или в области Запросы в редакторе Power Query. Некоторые команды доступны только в одной области. Многие из этих команд также доступны на контекстной вкладке Запрос на ленте. Все запросы в книге отображаются и сортироваться по дате и времени последнего изменения, начиная с последнего.

Сделайте одно из следующего: В Excel Выберите данные> запросы &

Connections> Запросы.

В редакторе Power Query Выберите данные > получить данные > запустить редактор Power Queryи просмотреть области Запросы слева.

Выберите команду управления запросами: Изменение запроса

в редакторе Power Query. Доступно только на вкладке Запросы в области & подключений.

Удалить Удаляет запрос.

Переименование Изменяет имя запроса.

Обновление Позволяет получить данные из внешних источников. Доступно только на вкладке Запросы в области & подключений. Дополнительные сведения см. в этойExcel.

Загрузить в Выводит диалоговое окно Импорт данных, в котором можно выбрать, как вы хотите просмотреть данные, куда их поместить, и добавить их в модель данных. Доступно только на вкладке Запросы в области & подключений.

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

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

Слияние Объединение столбцов в запросе с совпадающих столбцов в других запросах. Дополнительные сведения о слиянии см. в этой теме. Доступно только на вкладке Запросы в области & подключений.

"Приложение" В запрос можно также вовсю вть столбцы. Дополнительные сведения о приложении см. в этой теме. Доступно только на вкладке Запросы в области & подключений.

Переместить в группу Перемещает запрос в группу в списке или, если групп нет, выберите Создать группу, чтобы создать ее. Придай ему осмысленное имя, например Бюджет или Маркетинг. Группы используются для у упорядотки множества запросов по категориям. Вы даже можете создавать группы в группах.

Вверх Перемещает запрос вверх в списке запросов.

Переместить вниз Перемещает запрос вниз в списке запросов.

Функция Create (Создать функцию) выводит диалоговое окно Создание функции. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в справке по функции Power Query M.

Преобразовать в параметр Преобразует запрос в параметр и отображает его в области Предварительный просмотр данных. Если текущий дисплей является параметром, команда перенастроит параметр Преобразовать в запрос. Доступно только в области Запросы редактора Power Query. Дополнительные сведения см. в странице Создание запроса с параметрами (Power Query).

Расширенный редактор Открывает окно Расширенный редактор. Дополнительные сведения см. в этойExcel. Доступно только в области Запросы редактора Power Query.

Экспорт файла подключения Сохраняет запрос в качестве ODC-файла подключения. ODC-файл содержит определение запроса, сведения об подключениях к источнику данных и все этапы преобразования. Это полезно, если вы хотите поделиться запросами с другими пользователями и книгами. Кроме того, можно воспользоваться командой Свойства на контекстной вкладке Запрос ленты Power Query. Дополнительные сведения см. в статьи Создание, изменение и управление подключениями к внешним данным. Доступно только на вкладке Запросы в области & подключений.

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

Свойства Эта команда используется для управления запросом и сведениями об источниках данных. На вкладке Запросы в области & подключенияоткрывается диалоговое окно Свойства запроса. На области Запросы в редакторе Power Query откроется Параметры запроса.

Управление наведении курсором имени запроса на вылет запроса

Вы можете включить или отключить отображение вылетного запроса при наведении курсором мыши на имя запроса на вкладке Запрос в области Запросы & подключения. Этот параметр управляет отображением вылетов только при наведении курсором. Вы по-прежнему можете отобразить вылет запроса, щелкнув правой кнопкой мыши имя запроса и выбрав показать обзор.

Выберите Параметры запроса> получить >данных.

Слева в области GLOBALвыберите Общие.

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

С помощью 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 на ленте.

Microsoft 365

Мы обновили 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

В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.

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

Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:

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

Решение

Запуск Power Query

Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.

Добавить кнопки Power Query на панель быстрого доступа

Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:

Изменить запрос
Запросы и подключения
Запустить редактор запросов

Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.

Предварительные настройки Power Query

Откройте редактор запросов любым удобным способом:

Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.

Создаем первый запрос Power Query

Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:

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

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

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

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

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

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

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
Изменить регистр ячеек в столбце
Подсчитать количество символов в ячейках столбца.

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

Арифметические операции
Возводить в степени, вычислять логарифмы, факториалы, корни
Тригонометрические операции
Округлять до заданных значений
Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Читайте также: