Преобразование данных с помощью Power Query

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

Из этой статьи вы узнаете, как создавать запросы с Power Query, обнаруживая следующее:

• Как работает интерфейс получения данных в Power Query.
• Как использовать и извлекать пользу из пользовательского интерфейса Power Query.
• Как выполнять распространенные преобразования, такие как группирование и объединение данных.

Если вы не знакомы с Power Query, перед началом работы вы можете sign up для бесплатной пробной версии Power BI. Вы можете использовать Power BI Dataflows, чтобы воспользоваться опытом работы с Power Query Online, описанным в этой статье.

Вы также можете бесплатно скачать Power BI Desktop.

Примеры, приведенные в этой статье, подключаются к веб-каналу Northwind OData.

Подключитесь к каналу OData

Заметка

Чтобы узнать больше о том, где получить данные из каждого из продуктов Microsoft, которые включают Power Query, перейдите в раздел Where, чтобы получить данные.

Для начала найдите соединитель OData-канала в окне "Получение данных". Вы можете найти OData в строке поиска в правом верхнем углу.

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

• В поле URL введите URL канала Northwind OData: https://services.odata.org/V4/Northwind/Northwind.svc/
• Для локального шлюза данныхоставьте без изменений.
• Для типа аутентификацииоставьте анонимным.

Выберите кнопку Далее.

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

Затем диалоговое окно загружает данные из таблицы Customers в редактор Power Query.

Интерфейс подключения к данным, указание метода проверки подлинности и выбор конкретного объекта или таблицы для подключения называется опытом получения данных. Этот опыт описан более подробно в статье "Обзор получения данных".

Заметка

Дополнительные сведения о соединителе канала OData см. в канал OData.

Компоненты редактора Power Query

Подсказка

Чтобы найти редактор Power Query в Power BI, в разделе "Запросы" выберите Transform data.

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

#	Компонент	Description
1	Лента	Интерфейс навигации ленты, который предоставляет несколько вкладок для добавления преобразований, выбора параметров запроса и доступа к различным кнопкам ленты для выполнения различных задач.
2	Область запросов	Просмотр всех доступных вам запросов.
3	Текущее представление	Основное рабочее представление, которое по умолчанию отображает предварительный просмотр данных для запроса. Вы также можете включить представление схемы вместе с представлением предварительного просмотра данных. Кроме того, можно переключаться между представлением схемы и представлением предварительного просмотра данных при сохранении представления схемы.
4	Параметры запроса	Представление выбранного в данный момент запроса с соответствующими сведениями, такими как имя запроса, шаги запроса и различные индикаторы.
5	Строка состояния	На панели отображаются важные сведения о запросе, такие как время выполнения, общее количество столбцов и строк, а также состояние обработки. Эта панель также содержит кнопки для изменения текущего представления.

Заметка

В настоящее время представления схемы и диаграмм доступны только в Power Query Online.

Навигация по редактору Power Query

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

Лента редактора Power Query

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

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

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

Разворачивание и сворачивание панелей Power Query

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

Переключение между представлениями Power Query

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

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

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

Что такое представление схемы в Power Query

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

Заметка

Чтобы узнать больше о представлении схемы, перейдите к Использование представления схемы.

Например, в представлении схемы установите флажок рядом со столбцами Заказы и Демография покупателей, а на ленте выберите действие Удалить столбцы. Этот выбор применяет преобразование для удаления этих столбцов из данных.

Что такое представление схемы в Power Query

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

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

Заметка

Дополнительные сведения о представлении диаграммы см. в представлении диаграммы.

Группировать клиентов по странам или регионам в представлении схемы

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

Откроется диалоговое окно "Группа по". Вы можете настроить операцию Группировать по, чтобы выполнять группировку по странам/регионам и подсчитывать количество строк клиентов для каждой страны/региона.

1. Оставьте выбранным переключатель "Базовый".
2. Выберите страну или регион , чтобы сгруппировать по.
3. Введите Customers в качестве имени нового столбца и выберите подсчет строк в качестве операции.

Нажмите кнопку ОК для выполнения операции. Предварительный просмотр данных обновляется, чтобы отобразить общее количество клиентов по странам или регионам.

Альтернативный способ открыть диалоговое окно Группировать по — использовать кнопку Группировать по на ленте или нажать и удерживать столбец Страна/регион (или щёлкнуть его правой кнопкой мыши).

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

Добавление нового запроса

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

Сначала необходимо добавить данные поставщиков. Выберите Получить данные. В Выбор источника данныхвведите в поиск OData. Затем выберите соединитель OData.

Интерфейс подключения OData снова появился. Введите параметры подключения, как описано в Connect к веб-каналу OData для подключения к веб-каналу OData Northwind. В интерфейсе навигатора найдите и выберите таблицу поставщиков.

Выберите Create, чтобы добавить новый запрос в редактор Power Query. Разверните область "Запросы ", которая теперь отображает запрос "Клиенты " и " Поставщики ".

Снова откройте диалоговое окно Группа, выбрав кнопку Группа на ленте на вкладке Преобразование.

В диалоговом окне «Группировать по» задайте для операции «Группировать по» группировку по стране или региону и подсчёт количества строк поставщиков для каждой страны или региона.

1. Оставьте выбранным переключатель "Базовый".
2. Выберите страну или регион , чтобы сгруппировать по.
3. Введите Поставщики в Имя нового столбца и выберите Подсчитать строки в Операция.

Заметка

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

Справочные запросы

При ссылке на запрос создается новый запрос, указывающий на выходные данные существующего. Теперь, когда у вас есть запрос к клиентам и запрос к поставщикам, ваша следующая цель — объединить их в один. Для выполнения этой задачи можно использовать множество методов, включая параметр слияния в таблице Customers , дедупликацию запроса или ссылку на запрос. В этом примере создайте ссылку, выбрав и удерживая (щелкнув правой кнопкой мыши) таблицу Customers и выбрав ссылку. Это действие создает новый запрос, ссылающийся на запрос "Клиенты ".

После создания этого запроса измените имя запроса на анализ стран или регионов и отключите загрузку таблицы "Клиенты" , очистив параметр "Включить загрузку " из запроса "Поставщики ".

Объединение запросов

Операция объединения запросов соединяет две существующие таблицы на основе совпадения значений из одного или нескольких столбцов. В этом примере цель состоит в том, чтобы соединить таблицы Клиентов и Поставщиков в одну таблицу только для тех стран и регионов, которые имеют как Клиентов, так и Поставщиков.

В запросе " Анализ страны или региона " выберите параметр "Объединить запросы " на вкладке "Главная " на ленте.

Появится новое диалоговое окно для операции слияния. Затем можно выбрать запрос для слияния с текущим запросом. В правой таблице для слияния выберите запрос "Поставщики ", а затем в обоих запросах выберите поле "Страна или регион ". Наконец, выберите тип соединения Inner, так как для этого анализа нужны только страны или регионы, в которых у вас есть и клиенты, и поставщики.

После нажатия кнопки "ОК " новый столбец добавляется в запрос " Анализ страны или региона ", содержащий данные из запроса "Поставщики ". Щелкните значок рядом с полем "Поставщики" , в котором отображается меню, в котором можно выбрать поля, которые нужно развернуть. Выберите только поле поставщиков, а затем нажмите кнопку ОК.

Результатом этой операции разворачивания является таблица, содержащая всего 12 строк. Переименуйте поле Suppliers.1 на поставщики, дважды щелкнув по имени поля и введя новое имя.

Заметка

Чтобы узнать больше о функции слияния запросов, перейдите к обзору слияния запросов.

Примененные шаги

Каждое преобразование, применяемое к запросу, сохраняется как шаг в разделе Примененные шаги области параметров запроса. Если вам когда-либо нужно проверить, как ваш запрос преобразуется с шага на шаг, можно выбрать шаг и просмотреть способ разрешения запроса на этом конкретном этапе.

Вы также можете выбрать и сохранить (щелкните правой кнопкой мыши) запрос и выбрать параметр Свойства, чтобы изменить имя запроса или добавить описание запроса. Например, выберите и удерживайте (щелкните правой кнопкой мыши) шаг "Объединить запросы " из запроса " Анализ страны или региона " и измените имя запроса на слияние с поставщиками и описанием получения данных из запроса "Поставщики" для поставщиков по странам или регионам.

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

Заметка

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

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

Добавление нового столбца

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

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

Профилирование данных

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

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

Заметка

Дополнительные сведения о профилировании данныхсм. в разделе Использование средств профилирования данных.

Расширенный редактор

Если вы хотите наблюдать за кодом, создаваемым редактором Power Query с каждым шагом, или хотите создать собственный код формирования, можно использовать расширенный редактор. Чтобы открыть advanced editor, откройте вкладку View на ленте, а затем выберите Расширенный редактор. Откроется окно с существующим кодом запроса.

Вы можете напрямую изменить код в окне Расширенный редактор. Редактор указывает, является ли код свободным от синтаксической ошибки. Чтобы закрыть окно, нажмите кнопку "Готово" или "Отменить".

Открыть справку по Power Query

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

Справка по Power Query в Excel

Существует несколько способов доступа к справке Power Query, которые специально предназначены для Power Query в Excel. Одним из способов доступа к документации по Excel Power Query в Интернете является выбор File>Help>Help в редакторе Power Query.

Чтобы получить доступ к встроенным сведениям справки Power Query в Excel, выберите вкладку Help на ленте Excel, а затем введите Power Query в текстовом поле поиска.

Справка по Power Query в Power BI Desktop

На вкладке Help в редакторе Power Query, который работает на Power BI Desktop, доступны функции, которые в настоящее время ведут к ресурсам, предоставляющим информацию о Power BI. Вы также можете получить эти же сведения Power BI Help в редакторе Power Query в Power BI Desktop, выбрав File>Help и выбрав один из вариантов справки.

Справка по Power Query в служба Power BI, Power Apps, Dynamics 365 Customer Insights и Фабрика данных Azure

Вкладка Power Query Help в служба Power BI, Power Apps, Dynamics 365 Customer Insights и Фабрика данных Azure содержит ссылки на важные источники информации о Power Query.

Опции на вкладке справки включают ссылки на:

• документация Power Query: ссылки на документацию Power Query
• сочетания клавиш Keyboard: ссылки на статью о сочетаниях клавиш в документации по Power Query.
• Support: ссылки на веб-сайт поддержки продукта Microsoft, в котором в настоящее время работает Power Query.
• веб-сайт Power Query: ссылки на веб-сайт Power Query.
• блог Power Query: ссылки на блог Power Query
• Сообщество Power Query: ссылки на сообщество Power Query.
• Отправить идею: ссылка на сайт с идеями о продукте Microsoft, в котором в данный момент работает Power Query.
• Send feedback: открывает окно в Power Query. В окне показано, как оценить опыт работы с Power Query и предоставить дополнительную информацию, которую вы хотите предоставить.

Заметка

В настоящее время в Azure Analysis Services отсутствуют встроенные справочные ссылки Power Query. Однако вы можете получить справку по функциям M Power Query. Дополнительные сведения содержатся в следующем разделе.

Справка по функциям Power Query M

Помимо функций справки Power Query, перечисленных в предыдущих разделах, вы также можете получить справку по отдельным функциям M из редактора Power Query. Шаги немного отличаются в зависимости от используемой версии: Power Query Desktop (интегрированная в приложения, такие как Power BI Desktop) или Power Query Online (интегрированная в службы, такие как служба Power BI и потоки данных). Ниже описаны шаги, как получить доступ к этой встроенной справке.

1. Откройте редактор Power Query, нажмите кнопку вставки шага ().
2. В строке формул введите имя функции, которую необходимо проверить.
   1. Если вы используете Power Query Desktop, введите знак равенства, пробел и имя функции.
   2. Если вы используете Power Query Online, введите имя функции.
3. Выберите свойства функции.
   1. Если вы используете Power Query Desktop, в области Query Settings в разделе Properties выберите свойства All.
   2. Если вы используете Power Query Online, в области Query Settings выберите Properties.

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

Сводка

В этой статье вы создали ряд запросов с Power Query, которые предоставляют анализ соотношения между клиентами и поставщиками на уровне страны или региона для корпорации Northwind.

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

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