power query как включить параметры запроса
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. Выполняем следующие действия:
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!
Управление запросами (Power Query)
Управлять запросами можно в области «Запросы» редактора Power Query или на вкладке Запросы в области «Запросы & подключения» Excel. Это особенно полезно, если у вас много запросов и вы хотите быстро найти их. После того как вы найдете запрос, можно сделать ряд дополнительных действий, таких как дублирование, ссылка, слияние, приложение, совместное действие и удаление запроса.
При создании запроса сведения о подмыве создаются за кадром. Роль запроса заключается в формировании данных. Роль сведений о под соединении заключается в сохранении сведений об источнике данных и их обновлении в соответствии с настройками обновления, такими как частота.
Ниже перечислены команды, которые можно использовать в том порядке, в котором они отображаются в соответствующих контекстных меню на вкладке Запросы в области «Запросы & Подключение» в Excel или в области Запросы в редакторе Power Query. Некоторые команды доступны только в одной области. Многие из этих команд также доступны на контекстной вкладке Запрос на ленте. Все запросы в книге отображаются и сортироваться по дате и времени последнего изменения, начиная с последнего.
Сделайте одно из следующего: В Excel Выберите данные> запросы &
В редакторе 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)
Возможно, вы хорошо знакомы с запросами с параметрами, которые используются в SQL или Microsoft Query. Однако параметры Power Query имеют основные различия.
Параметры можно использовать на любом шаге запроса. Помимо использования в качестве фильтра данных, параметры можно использовать для указания таких параметров, как путь к файлу или имя сервера.
Параметры не будут подсказок на ввод данных. Вместо этого вы можете быстро изменить их значение с помощью Power Query. Вы даже можете хранить и извлекать значения из ячеек в Excel.
Параметры сохраняются в простом запросе с параметрами, но не в запросах данных, в которые они используются. После создания вы можете добавить параметр в запросы.
Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами в Microsoft Query.
С помощью параметра можно автоматически изменять значение в запросе и не изменять его каждый раз. Просто измените значение параметра. После создания параметра он будет сохранен в специальном запросе, который можно легко изменить непосредственно из Excel.
Выберите Данные > получить данные > другие источники > запуск редактора Power Query.
В редакторе Power Query выберите Главная > Параметры > Параметры.
В диалоговом окне Управление параметрами выберите Новый.
При необходимости за установите следующее:
Это должно отразить функцию параметра, но оставить его как можно более кратким.
Они могут содержать любые сведения, которые помогут правильно использовать параметр.
Сделайте одно из следующих значений: Любое значение. В запросе с параметрами можно ввести любое значение любого
Список значений Вы можете ограничить значения определенным списком, введите их в небольшую сетку. Необходимо также выбрать значения по умолчанию и Текущее значение ниже.
Запрос Выберите запрос списка, который напоминает структурированный столбец списка, разделенный запятой и заключенный в скобки.
После создания параметра запрос списка отобразится в его значениях.
Этот параметр определяет тип данных параметра.
При желании добавьте список значений или укажите запрос, чтобы предоставить предложения по входным данным.
Значение по умолчанию
Это отображается только в том случае, если для значения «Предлагаемые значения» установлено значение «Список значений» иуказано, какой элемент списка является элементом по умолчанию. В этом случае необходимо выбрать значение по умолчанию.
В зависимости от того, где используется параметр, если он пустой, запрос может не возвращать никаких результатов. Если выбрано значение Обязательно, текущее значение не может быть пустым.
Чтобы создать параметр, выберите ОК.
Вот как можно управлять изменениями в расположениях источников данных и предотвратить ошибки обновления. Например, предположим, что схема и источник данных похожи, создайте параметр, чтобы легко изменить источник данных и предотвратить ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, руководитель базы данных время от времени меняет сервер, ежемесячный выпуск CSV-файлов переходит в другую папку или вам нужно легко переключаться между средами разработки, тестирования и работы.
Шаг 1. Создание запроса с параметрами
В следующем примере вы импортируете несколько CSV-файлов с помощью операции импорта папки (выберите Данные > Получить данные > Из файлов > из папки )из папки C:\DataFilesCSV1. Но иногда в качестве расположения для передачи файлов иногда используется другая папка C:\DataFilesCSV2. Вы можете использовать параметр в запросе в качестве замены значения для другой папки.
Выберите Главная> Управление параметрами > Новый параметр.
В диалоговом окне Управление параметрами введите следующие сведения:
Альтернативное расположение для перепада файлов
Начало работы в Power Query
Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.
Power Query в меню Excel
В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).
В Excel после 2016 года Power Query уже встроен по умолчанию и находится в меню Данные → раздел Получить и преобразовать данные (в некоторых версиях Excel этот раздел называется Скачать & преобразовать).
Работа с данными в Power Query
Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).
Подключение к данным в Power Query
В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.
Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).
Для примера добавим в Power Query данные из таблицы.
— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).
В открывшемся окне поставьте галочку «Таблица с заголовками».
Таблица с данными при этом превратится в «умную» smart-таблицу.
Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.
Редактор запросов Power Query
Разберем подробнее интерфейс редактора запросов Power Query.
Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.
Итак, в редакторе Power Query есть:
Преобразование данных
Посмотрим на простом примере, как преобразовать данные в Power Query.
Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).
Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:
При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.
Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.
Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.
Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.
Пользовательский интерфейс Power Query
С помощью Power Query можно подключаться к различным источникам данных и преобразовывать данные в нужную форму.
В этой статье вы узнаете, как создавать запросы с Power Query путем обнаружения:
если вы не знакомы с Power Query, вы можете зарегистрироваться для получения бесплатной пробной версии Power BI перед началом работы. вы можете использовать Power BI потоки данных, чтобы испытать Power Query работы в интернете, описанные в этой статье.
Примеры в этой статье позволяют подключить и использовать веб-канал OData Northwind.
Подключение к веб-каналу OData
Для начала откройте соединитель канала OData из интерфейса «получение данных». Можно выбрать другую категорию в верхней части или выполнить поиск OData в строке поиска в правом верхнем углу.
После выбора этого соединителя на экране отобразятся параметры подключения и учетные данные.
Нажмите кнопку Далее.
Затем диалоговое окно загружает данные из таблицы Customers в редактор Power Query.
Дополнительные сведения о соединителе каналов OData см. в разделе веб-канал OData.
Пользовательский интерфейс редактора Power Query
Редактор Power Query представляет пользовательский интерфейс Power Query, в котором можно добавлять или изменять запросы, управлять запросами, группировать или добавлять описания в шаги запроса, а также визуализировать запросы и их структуру с разными представлениями. Пользовательский интерфейс Power Query имеет пять отдельных компонентов.
Представление схема и схема в настоящее время доступно только в Power Query в сети.
Использование редактора Power Query
В этом разделе вы начнете преобразовывать данные с помощью Power Query. Но перед началом работы с преобразованиями данных мы рассмотрим некоторые панели пользовательского интерфейса, которые можно развернуть или свернуть в зависимости от контекста. Выбор соответствующих панелей позволяет сосредоточиться на представлении, наиболее подходящем для вас. Мы также рассмотрим различные представления, доступные в пользовательском интерфейсе Power Query.
Развернуть и свернуть панели
Вы заметите, что в Power Query пользовательском интерфейсе есть значки, помогающие сворачивать или расширять определенные представления или разделы. Например, в правом верхнем углу панели «запросы» есть значок, который сворачивает панель «запросы», если она выбрана, и разворачивает область при повторном выборе.
Переключение между представлениями
Рекомендуется попробовать все эти варианты, чтобы найти представление и макет, с которыми вам удобнее работать. В качестве примера выберите представление схемы на ленте.
В правой части строки состояния также содержатся значки для представлений схемы, данных и схемы. Эти значки можно использовать для переключения между представлениями. Эти значки также можно использовать для включения или отключения выбранного представления.
Что такое представление схемы
Представление схемы обеспечивает быстрый и простой способ взаимодействия только с компонентами схемы для таблицы, такими как имена столбцов и типы данных. Если необходимо выполнить действия, связанные со схемой, такие как удаление столбцов, переименование столбцов, изменение типов данных столбцов, переупорядочение столбцов или дублирование столбцов, рекомендуется использовать представление схемы.
Дополнительные сведения о представлении схемы см. в разделе Использование представления схемы.
Что такое представление диаграммы
Теперь можно вернуться в представление предварительного просмотра данных и включить представление схемы, чтобы увидеть более визуальную перспективу данных и запросов.
Представление схемы позволяет визуализировать, как структурирован запрос и как он может взаимодействовать с другими запросами в проекте. Каждый шаг в запросе имеет отдельный значок, помогающий определить используемое преобразование. Существуют также линии, соединяющие шаги для демонстрации зависимостей. Так как представление «предварительный просмотр данных» и представление схемы включены, представление диаграммы отображается в верхней части предварительной версии данных.
Дополнительные сведения о представлении схемы см. в разделе представление диаграммы.
Начало преобразования данных
С включенным представлением схемы щелкните знак «плюс». Вы можете найти новое преобразование, которое будет добавлено в запрос. Выполните поиск по запросу Group By и выберите преобразование.
Для удобства преобразования в Power Query часто можно получить из нескольких мест, чтобы пользователи могли использовать предпочтительные им возможности.
Добавление нового запроса
Теперь, когда у вас есть запрос, который предоставляет количество клиентов по странам, вы можете добавить контекст к этим данным, выполнив поиск общего числа поставщиков для каждой территории.
Снова появится интерфейс подключения OData. введите параметры подключения, как описано в Подключение к веб-каналу odata для подключения к веб-каналу odata Northwind. В окне « Навигатор » найдите и выберите таблицу « поставщики ».
В диалоговом окне Группировать по задайте для параметра Группировать по значение страна и подсчитайте количество строк поставщика по странам.
Дополнительные сведения о преобразовании Group By см. в разделе группирование или суммирование строк.
Создание ссылок на запросы
Слияние запросов
В запросе на анализ страны выберите параметр объединить запросы на вкладке Главная на ленте.
Дополнительные сведения о функции слияния запросов см. в разделе Общие сведения о запросах слияния.
Примененные шаги
Каждое преобразование, применяемое к запросу, сохраняется как шаг в разделе Примененные действия области Параметры запроса. Если вам когда-либо нужно проверить, как запрос преобразуется с шага на шаг, можно выбрать шаг и предварительный просмотр того, как запрос будет разрешаться в определенной точке.
Это изменение добавляет новый значок рядом с шагом, на который можно навести указатель мыши, чтобы прочитать его описание.
Дополнительные сведения о примененных действиях см. в разделе Использование списка примененных действий.
Добавление нового столбца
Это изменение создает новый столбец с именем » целое число-деление «, который можно переименовать в соотношение. Это изменение является последним этапом запроса, так как вы видите отношение «клиент-поставщик» для стран, в которых данные имеют клиентов и поставщиков.
Профилирование данных
Еще одна Power Queryная функция, которая может помочь лучше понять данные — это Профилирование данных. Включив функции профилирования данных, вы получите отзыв о данных в полях запроса, таких как распределение значений, качество столбца и многое другое.
Дополнительные сведения о профилировании данных см. в разделе использование средств профилирования данных.
Сводка
В этой статье вы создали серию запросов с Power Query, обеспечивающих анализ отношений «клиент-поставщик» на уровне страны для корпорации «Борей».
Вы узнали о компонентах пользовательского интерфейса Power Query, о том, как создавать новые запросы в редакторе запросов, ссылаться на запросы, объединять запросы, подбираться в разделе Примененные действия, добавлять новые столбцы и как использовать средства профилирования данных для лучшего понимания данных.
Power Query — это мощное средство, используемое для подключения к различным источникам данных и преобразования данных в нужную форму. В сценариях, описанных в этой статье, приведены примеры, демонстрирующие, как пользователи могут использовать Power Query для преобразования необработанных данных в важные ценные сведения бизнес-аналитики.