С помощью Power Query можно подключиться к различным источникам данных и преобразовать данные в нужную фигуру.
Из этой статьи вы узнаете, как создавать запросы с помощью Power Query, обнаруживая следующее:
Как работает интерфейс Get Data в Power Query.
Использование и использование пользовательского интерфейса Power Query.
Как выполнять распространенные преобразования, такие как группирование и объединение данных.
Если вы не знакомы с Power Query, вы можете зарегистрироваться на бесплатную пробную версию Power BI перед началом работы. Потоки данных Power BI можно использовать для пробных возможностей Power Query Online, описанных в этой статье.
Чтобы узнать больше о том, где получить данные из каждого продукта Майкрософт, включающего Power Query, перейдите в раздел "Где получить данные".
Чтобы начать, найдите соединитель веб-канала OData из интерфейса получения данных. Вы можете найти OData в строке поиска в правом верхнем углу.
После выбора этого соединителя на экране отображаются параметры подключения и учетные данные.
Для URL-адреса введите URL-адрес веб-канала OData Northwind, показанного в предыдущем разделе.
Для локального шлюза данных оставьте значение "нет".
Для проверки подлинности оставьте анонимным.
Нажмите кнопку Далее.
Откроется навигатор , где вы выбираете таблицы, к которым нужно подключиться из источника данных. Выберите таблицу "Клиенты" , чтобы загрузить предварительный просмотр данных, а затем выберите " Преобразовать данные".
Затем диалоговое окно загружает данные из таблицы Customers в редактор Power Query.
Приведенный выше интерфейс подключения к данным, указание метода проверки подлинности и выбор определенного объекта или таблицы для подключения вызывается интерфейс получения данных. Этот интерфейс описан более подробно в статье "Получение данных ".
Примечание
Дополнительные сведения о соединителе веб-канала OData см. в веб-канале OData.
Пользовательский интерфейс редактора Power Query
Редактор Power Query представляет пользовательский интерфейс Power Query. В этом пользовательском интерфейсе можно добавлять или изменять запросы, управлять запросами, группируя или добавляя описания для шагов запроса, а также визуализировать запросы и их структуру с различными представлениями. Пользовательский интерфейс Power Query содержит пять отдельных компонентов.
Лента: интерфейс навигации ленты, который предоставляет несколько вкладок для добавления преобразований, выбора параметров запроса и доступа к различным кнопкам ленты для выполнения различных задач.
Область запросов: представление всех доступных запросов.
Текущее представление: основное рабочее представление, которое по умолчанию отображает предварительный просмотр данных для запроса. Вы также можете включить представление схемы вместе с представлением предварительного просмотра данных. Вы также можете переключаться между представлением схемы и представлением предварительного просмотра данных при сохранении представления схемы.
Параметры запроса: представление выбранного в данный момент запроса с соответствующими сведениями, такими как имя запроса, шаги запроса и различные индикаторы.
Строка состояния: панель, отображающая важные сведения о запросе, например время выполнения, общее количество столбцов и строк и состояние обработки. Эта панель также содержит кнопки для изменения текущего представления.
Примечание
В настоящее время представление схемы и схемы доступно только в Power Query Online.
Использование редактора Power Query
В этом разделе вы начнете преобразование данных с помощью Power Query. Но прежде чем приступить к преобразованию данных, мы обсудим некоторые области пользовательского интерфейса, которые можно развернуть или свернуть в зависимости от их контекста. Выбор соответствующих панелей позволяет сосредоточиться на представлении, которое наиболее важно для вас. Мы также обсудим различные представления, доступные в пользовательском интерфейсе Power Query.
Лента
Лента — это компонент, в котором вы найдете большую часть преобразований и действий, которые можно выполнить в редакторе Power Query. Она имеет несколько вкладок, значения которых зависят от интеграции продукта. Каждая из вкладок предоставляет определенные кнопки и параметры, некоторые из которых могут быть избыточными во всем интерфейсе Power Query. Эти кнопки и параметры обеспечивают простой доступ к преобразованиям и действиям, которые могут потребоваться.
Интерфейс Power Query реагирует и пытается настроить разрешение экрана, чтобы показать лучший интерфейс. В сценариях, в которых вы хотите использовать компактную версию ленты, также есть кнопка сворачивания в правом нижнем углу ленты, чтобы помочь вам перейти на компактную ленту.
Вы можете вернуться к стандартному представлению ленты, выбрав значок развертывания в правом нижнем углу ленты.
Развертывание и свертывание панелей
Вы можете заметить, что в пользовательском интерфейсе Power Query есть значки, которые помогают свернуть или развернуть определенные представления или разделы. Например, есть значок в правом верхнем углу области "Запросы", которая свернула область запросов при выборе, а затем разверните панель при повторном выборе.
Переключение между представлениями
Помимо того, что вы сможете свернуть определенные области и разделы в пользовательском интерфейсе Power Query, можно также переключить отображаемые представления. Чтобы переключить представления, перейдите на вкладку "Вид " на ленте, где находятся группы предварительного просмотра и макета , которые определяют, как выглядит пользовательский интерфейс Power Query.
Рекомендуется попробовать все эти варианты, чтобы найти представление и макет, с которыми вы чувствуете себя наиболее комфортно. Например, выберите представление схемы на ленте.
В правой части строки состояния также содержатся значки для представлений схем, данных и схем. Эти значки можно использовать для изменения между представлениями. Вы также можете использовать эти значки для включения или отключения выбранного представления.
Что такое представление схемы
Представление схемы предоставляет быстрый и простой способ взаимодействия только с компонентами схемы для таблицы, такими как имена столбцов и типы данных. Мы рекомендуем представление схемы, если вы хотите выполнить действия, связанные с схемой. Эти действия могут быть удалены, переименование столбцов, изменение типов данных столбцов, изменение порядка столбцов или дублирование столбцов.
Например, в представлении схемы выберите знак проверка рядом со столбцами Orders и CustomerDemographics, а на ленте выберите действие "Удалить столбцы". Этот выбор применяет преобразование для удаления этих столбцов из данных.
Что такое представление схемы
Теперь вы можете вернуться к представлению предварительного просмотра данных и включить представление схемы для использования более визуальной перспективы данных и запросов.
Представление схемы помогает визуализировать структуру запроса и способ взаимодействия с другими запросами в проекте. Каждый шаг в запросе имеет отдельный значок для распознавания используемого преобразования. Существуют также строки, которые подключают шаги для иллюстрации зависимостей. Так как представление предварительного просмотра данных и представление схемы включены, представление схемы отображается в верхней части предварительного просмотра данных.
Примечание
Дополнительные сведения о представлении схемы см. в представлении диаграммы.
Начало преобразования данных
С включенным представлением схемы выберите знак плюса. Вы можете найти новое преобразование для добавления в запрос. Найдите группу и выберите преобразование.
Откроется диалоговое окно "Группа по ". Группу можно задать по операции, чтобы сгруппировать по стране и подсчитать количество строк клиентов в каждой стране.
Удерживайте переключатель "Базовый".
Выберите страну для группировки по.
Введите "Клиенты " в качестве имени нового столбца и выберите "Подсчет строк " в качестве операции.
Нажмите кнопку "ОК ", чтобы выполнить операцию. Предварительный просмотр данных обновляется, чтобы отобразить общее количество клиентов по странам.
Альтернативный способ запуска группы по диалогу — использовать группу, нажав кнопку на ленте или щелкнув правой кнопкой мыши столбец "Страна".
Для удобства преобразования в Power Query часто можно получить доступ из нескольких мест, чтобы пользователи могли использовать предпочитаемый им интерфейс.
Добавление нового запроса
Теперь, когда у вас есть запрос, предоставляющий количество клиентов в каждой стране, можно добавить контекст в эти данные, найдя общее количество поставщиков для каждой территории.
Сначала необходимо добавить данные поставщиков . Выберите Получить данные. В разделе "Выбор источника данных" найдите OData. Затем выберите соединитель OData .
Интерфейс подключения OData снова появился. Введите параметры подключения, как описано в Подключение веб-канал OData для подключения к веб-каналу OData Northwind. В интерфейсе навигатора найдите и выберите таблицу "Поставщики ".
Нажмите кнопку "Создать", чтобы добавить новый запрос в редактор Power Query. Разверните область "Запросы", которая теперь отображает запрос "Клиенты" и "Поставщики".
Снова откройте группу по диалоговому окне, нажав кнопку "Группа по" на ленте на вкладке "Преобразование".
В диалоговом окне "Группа по" задайте группу по операции группировать по стране и подсчитывать количество строк поставщика на страну.
Удерживайте переключатель "Базовый".
Выберите страну для группировки по.
Введите "Поставщики " в имя нового столбца и выберите "Счетчик строк " в операции.
Теперь, когда у вас есть запрос для клиентов и запрос к поставщикам, ваша следующая цель — объединить эти запросы в один. Это можно сделать множеством способов, включая использование параметра слияния в таблице Customers , дублирование запроса или ссылку на запрос. В этом примере вы создадите ссылку, щелкнув правой кнопкой мыши таблицу "Клиенты" и выбрав ссылку, которая эффективно создает новый запрос, который ссылается на запрос "Клиенты".
После создания этого запроса измените имя запроса на анализ стран и отключите загрузку таблицы Customers, отменив флажок "Включить загрузку" из запроса "Поставщики".
Объединение запросов
Операция слияния объединяет две существующие таблицы на основе сопоставления значений из одного или нескольких столбцов. В этом примере цель состоит в том, чтобы объединить таблицы "Клиенты и поставщики" только в одну таблицу только для стран или регионов склиентами и поставщиками.
В запросе " Анализ страны" выберите параметр "Объединить запросы " на вкладке "Главная " на ленте.
Появится новое диалоговое окно для операции слияния. Затем можно выбрать запрос для слияния с текущим запросом. В правой таблице для слияния выберите запрос "Поставщики" , а затем выберите поле "Страна " в обоих запросах. Наконец, выберите тип внутреннего соединения, так как вы хотите только страны и регионы, где у вас есть клиенты и поставщики для этого анализа.
После нажатия кнопки "ОК" новый столбец добавляется в запрос "Анализ страны", содержащий данные из запроса "Поставщики". Щелкните значок рядом с полем "Поставщики" , в котором отображается меню, в котором можно выбрать поля, которые нужно развернуть. Выберите только поле "Поставщики" , а затем нажмите кнопку "ОК ".
Результатом этой операции расширения является таблица с только 12 строками. Переименуйте поле "Поставщики.1", дважды щелкнув имя поля и введя новое имя.
Каждое преобразование, применяемое к запросу, сохраняется в разделе "Примененные шаги " области параметров запроса. Если вам когда-либо нужно проверка, как ваш запрос преобразуется с шага на шаг, можно выбрать шаг и просмотреть способ разрешения запроса на этом конкретном этапе.
Вы также можете щелкнуть запрос правой кнопкой мыши и выбрать параметр "Свойства ", чтобы изменить имя запроса или добавить описание запроса. Например, щелкните правой кнопкой мыши шаг "Объединить запросы" из запроса "Анализ страны" и измените имя запроса на слияние с поставщиками и описание получения данных из запроса "Поставщики" для поставщиков по странам.
После этого изменения можно навести указатель мыши на значок запроса слияния в представлении схемы или навести указатель мыши на новый значок рядом с шагом в области "Примененные шаги", чтобы прочитать его описание.
Примечание
Дополнительные сведения о примененных шагах см. в разделе "Использование списка примененных шагов".
Прежде чем перейти к следующему разделу, отключите представление схемы, чтобы использовать только предварительную версию данных.
Добавление нового столбца
С данными для клиентов и поставщиков в одной таблице теперь можно вычислить соотношение клиентов и поставщиков для каждой страны. Выберите последний шаг запроса "Анализ страны", а затем выберите столбцы "Клиенты и поставщики ". На вкладке "Добавить столбец" на ленте и внутри группы номеров выберите "Стандартный", а затем разделите (целое число) из раскрывающегося списка.
Это изменение создает новый столбец с именем Integer-division , который можно переименовать в Ratio. Это изменение является последним шагом запроса и предоставляет соотношение клиентов к поставщику для стран и регионов, где данные имеют клиентов и поставщиков.
Профилирование данных
Еще одна функция Power Query, которая поможет лучше понять данные— профилирование данных. Включив функции профилирования данных, вы получите отзывы о данных в полях запроса, таких как распределение значений, качество столбцов и многое другое.
Мы рекомендуем использовать эту функцию во время разработки запросов, но вы всегда можете включить и отключить эту функцию в удобном режиме. На следующем рисунке показаны все средства профилирования данных, включенные для запроса анализа стран .
Примечание
Дополнительные сведения о профилировании данных см. в разделе "Использование средств профилирования данных".
Расширенный редактор
Если вы хотите наблюдать за кодом, создаваемым редактором Power Query с каждым шагом или хотите создать собственный код формирования, можно использовать расширенный редактор. Чтобы открыть расширенный редактор, выберите вкладку "Вид" на ленте и выберите Расширенный редактор. Откроется окно с существующим кодом запроса.
Вы можете напрямую изменить код в окне Расширенный редактор. Редактор указывает, является ли код свободным от синтаксической ошибки. Чтобы закрыть окно, нажмите кнопку "Готово " или "Отменить ".
Доступ к справке Power Query
Существуют различные уровни справки, к которым можно получить доступ в приложениях Майкрософт, использующих Power Query. В этом разделе описаны уровни справки.
Справка По Power Query в Excel
Существует несколько способов доступа к справке Power Query, которые специально применяются к Power Query в Excel. Одним из способов доступа к документации по Excel Power Query является выбор> справки по файлам> в редакторе Power Query.
Чтобы получить доступ к встроенным сведениям справки Power Query в Excel, перейдите на вкладку справки на ленте Excel и введите Power Query в текстовом поле поиска.
Справка Power Query в Power BI Desktop
На этой вкладке есть вкладка справки в редакторе Power Query, работающем в Power BI Desktop, но на данный момент все параметры переходят к расположениям, которые предоставляют сведения Power BI. Вы также можете получить эти же сведения справки Power BI в редакторе Power Query в Power BI Desktop, выбрав "Справка по файлу>", а затем выбрав один из вариантов справки.
Справка по Power Query в служба Power BI, Power Apps, Аналитика клиента и Фабрика данных Azure
Вкладка справки Power Query в служба Power BI, Power Apps, Dynamics 365 Customer Аналитика и Фабрика данных Azure содержит ссылки на важные источники информации о Power Query.
Сообщество Power Query: ссылки на сообщество Power Query.
Отправьте идею: ссылки на веб-сайт идей продукта Майкрософт, в котором в настоящее время работает Power Query.
Отправка отзывов: открывает окно в Power Query. В окне показано, как оценить опыт работы с Power Query и предоставить дополнительные сведения, которые вы хотите предоставить.
Примечание
В настоящее время службы Azure Analysis Services не содержат встроенных ссылок справки Power Query. Однако вы можете получить справку по функциям Power Query M. Дополнительные сведения содержатся в следующем разделе.
Справка по функциям Power Query M
Помимо функций справки Power Query, перечисленных в предыдущих разделах, вы также можете получить справку по отдельным функциям M из редактора Power Query. Ниже описано, как получить эту встроенную справку.
При открытии редактора Power Query нажмите кнопку "Вставка" ( ).
В строке формул введите имя функции, которую вы хотите проверка.
Если вы используете Power Query Desktop, введите знак равенства, пробел и имя функции.
Если вы используете Power Query Online, введите имя функции.
Выберите свойства функции.
Если вы используете Power Query Desktop, в области "Запрос Параметры" в разделе "Свойства" выберите "Все свойства".
Если вы используете Power Query Online, в области "Запрос Параметры" выберите "Свойства".
Эти действия открывают встроенные сведения справки для выбранной функции и позволяют вводить отдельные свойства, используемые функцией.
Итоги
В этой статье вы создали ряд запросов с помощью Power Query, который предоставляет анализ соотношения между клиентами и поставщиками на уровне страны для корпорации Northwind.
Вы узнали компоненты пользовательского интерфейса Power Query, как создавать новые запросы в редакторе запросов, ссылаться на запросы, объединять запросы, понимать примененные шаги и добавлять новые столбцы. Вы также узнали, как использовать средства профилирования данных для лучшего понимания данных.
Power Query — это мощный инструмент, используемый для подключения к различным источникам данных и преобразования данных в нужную фигуру. В сценариях, описанных в этой статье, приведены примеры использования Power Query для преобразования необработанных данных в важные бизнес-аналитики.
В этом модуле Power Query представлены Microsoft Excelмощные технологии подключения данных, очистки и формирования, которые являются основной Microsoft частью современного набора аналитических средств бизнес-аналитики.
Демонстрация методов и рекомендаций, которые соответствуют бизнес-и техническим требованиям для моделирования, визуализации и анализа данных с помощью Microsoft Power BI.