Учебное пособие: Анализ данных о продажах из Excel и потока данных OData
Обычно данные используются в нескольких источниках данных. Например, можно использовать две базы данных, одну для сведений о продукте и другую для сведений о продажах. С помощью Power BI Desktopможно объединить данные из разных источников, чтобы создавать интересные, убедительные анализы и визуализации данных.
В этом руководстве вы объединяете данные из двух источников данных:
- Книга Excel с информацией о продукте
- Канал данных OData, содержащий данные заказов
Вы собираетесь импортировать каждую семантику модели и выполнять операции преобразования и агрегирования. Затем можно использовать данные двух источников для создания отчета по анализу продаж с интерактивными визуализациями. Позже эти методы применяются к запросам SQL Server, CSV-файлам и другим источникам данных в Power BI Desktop.
Заметка
В Power BI Desktop часто существует несколько способов выполнения задачи. Например, можно щелкнуть правой кнопкой мыши или использовать меню Дополнительные параметры в столбце или ячейке, чтобы просмотреть дополнительные элементы ленты. Несколько альтернативных методов описаны в следующих шагах.
Импорт данных продукта Excel
Сначала импортируйте данные о продукте из рабочей книги Excel Products.xlsx в Power BI Desktop.
Скачайте книгу Products.xlsx Excel и сохраните ее как Products.xlsx.
Щелкните стрелку рядом с Получение данных на вкладке Главная ленты Power BI Desktop, а затем выберите Excel в меню Общие источники данных.
Заметка
Вы также можете выбрать сам элемент Получить данные или выбрать Получить данные в диалоговом окне Начало работы Power BI, затем выбрать Excel или Файл>Excel в диалоговом окне Получение данных, а затем выбрать Подключить.
В диалоговом окне Открыть перейдите к файлу Products.xlsx и выберите Открыть.
В навигаторавыберите таблицу продуктов, а затем выберите Преобразование данных.
Откроется предварительная версия таблицы в редакторе Power Query, где можно применить преобразования для очистки данных.
Заметка
Вы также можете открыть редактор Power Query, выбрав Преобразовать данные на ленте Главная в Power BI Desktop или щелкнув правой кнопкой мыши, или выбрав Дополнительные параметры рядом с любым запросом в представлении Отчет, и выбрав Преобразовать данные.
Очистка столбцов
В вашем объединенном отчете используются столбцы рабочей книги Excel: ProductID, ProductName, QuantityPerUnitи UnitsInStock. Вы можете удалить другие столбцы.
В редакторе Power Query выберите столбцы ProductID, ProductName, QuantityPerUnitи UnitsInStock. С помощью клавиш CTRL можно выбрать несколько столбцов или shift, чтобы выбрать столбцы рядом друг с другом.
Щелкните правой кнопкой мыши любой из выбранных заголовков. Выберите Удалить другие столбцы в раскрывающемся меню. Вы также можете выбрать "Удалить столбцы">или "Удалить другие столбцы" из группы "Управление столбцами" на вкладке "Главная".
Импортировать данные заказа из канала OData
Затем импортируйте данные заказа из примера веб-канала OData системы продаж Northwind.
В редакторе Power Query выберите Новый источник, а затем в меню Наиболее распространенные выберите канал OData.
В диалоговом окне веб-канала OData вставьте URL-адрес веб-канала Northwind OData
https://services.odata.org/V3/Northwind/Northwind.svc/. Нажмите кнопку ОК.
В Навигаторвыберите таблицу заказов и нажмите кнопку ОК, чтобы загрузить данные в редактор Power Query.
Заметка
В Навигатореможно выбрать любое имя таблицы, чтобы просмотреть предпросмотр, не устанавливая флажок.
Разверните данные заказа
Ссылки на таблицы можно использовать для создания запросов при подключении к источникам данных с несколькими таблицами, например реляционными базами данных или каналом OData Northwind. Таблица заказов содержит ссылки на несколько связанных таблиц. Операцию развертывания можно использовать для добавления столбцов ProductID, UnitPriceи Quantity из связанной таблицы Order_Details в целевую таблицу (Orders).
Прокрутите таблицу Заказы вправо, пока не увидите столбец Order_Details. Он содержит ссылки на другую таблицу, а не данные.
Выберите значок (
) в заголовке столбца Order_Details.В раскрывающемся меню:
Выберите (Выбрать все столбцы), чтобы очистить все столбцы.
Выберите ProductID, UnitPrice, и количество, а затем выберите OK.
После развертывания таблицы Order_Details три новых вложенных столбца таблицы заменяют столбец Order_Details. В таблице есть новые строки для добавленных данных каждого заказа.
Создание настраиваемого вычисляемого столбца
Редактор Power Query позволяет создавать вычисления и настраиваемые поля для обогащения данных. Вы можете создать пользовательский столбец, умножающий цену единицы на количество элементов, чтобы вычислить общую цену для каждого элемента строки заказа.
На вкладке редактора Power Query Add Column ribbon (Добавить столбец) выберите настраиваемый столбец.
В диалоговом окне настраиваемого столбца введите LineTotal в поле "Имя нового столбца".
В поле , предназначенном для формулы настраиваемого столбца, после =введите [Order_Details.UnitPrice] * [Order_Details.Quantity]. Вы также можете выбрать имена полей в поле прокрутки Доступные столбцы и выбрать команду << Вставить, вместо того чтобы вводить их вручную.
Нажмите кнопку ОК.
Новое поле LineTotal отображается в качестве последнего столбца в таблице Orders.
Установка типа данных нового поля
При подключении редактора Power Query к данным он допускает наилучшее предположение для определения типа данных каждого поля в целях отображения. Значок заголовка указывает на назначенный тип данных каждого поля. Вы также можете посмотреть в разделе Преобразования на вкладке ленты Главная под Тип данных.
Новый столбец LineTotal имеет тип данных Any, но имеет значения валюты. Чтобы назначить тип данных, щелкните правой кнопкой мыши заголовок столбца LineTotal, выберите Изменить тип в раскрывающемся меню, а затем выберите фиксированное десятичное число.
Заметка
Кроме того, можно выбрать столбец LineTotal, затем стрелку рядом с типом данных в области преобразования на вкладке ленты "Главная" и выбрать фиксированное десятичное число.
Очистка столбцов заказов
Чтобы упростить работу модели в отчетах, можно удалить, переименовать и изменить порядок некоторых столбцов.
Отчет будет использовать следующие столбцы:
- ДатаЗаказа
- ShipCity
- Страна доставки
- Order_Details.ProductID
- Order_Details.UnitPrice
- Order_Details.Quantity
- ОбщаяСумма
Выберите эти столбцы и используйте Удалить другие столбцы так же, как вы это делали с данными Excel. Кроме того, можно выбрать не перечисленные столбцы, щелкнуть его правой кнопкой мыши и выбрать Удалить столбцы.
Вы можете переименовать столбцы, начинающиеся с "Order_Details.", чтобы упростить их чтение.
Дважды щелкните по заголовку столбца или коснитесь его и удерживайте, либо щелкните правой кнопкой мыши по заголовку столбца и выберите Переименовать в раскрывающемся меню.
Удалите префикс Order_Details. из каждого имени.
Наконец, чтобы упростить доступ к столбцу LineTotal, перетащите его влево, справа от столбца ShipCountry.
Просмотрите шаги запроса
Действия редактора Power Query для формирования и преобразования данных записываются. Каждое действие отображается справа в области параметров запроса в разделе ПРИМЕНЕННЫЕ ШАГИ. Вы можете вернуться к ПРИМЕНЕННЫМ ШАГАМ, чтобы просмотреть их и при необходимости отредактировать, удалить или переставить. Однако изменение предыдущих шагов является рискованным, так как это может нарушить последующие шаги.
Выберите каждый из ваших запросов в списке Запросы слева в редакторе Power Query, затем ознакомьтесь с примененными шагами в параметрах запроса. После применения предыдущих преобразований данных ПРИМЕНЕННЫЕ ШАГИ для двух запросов должны выглядеть следующим образом:
запрос о продуктах
запрос заказов
Совет
Основой являются формулы языка Power Query , называемые также языком M . Чтобы просмотреть и изменить формулы, выберите Расширенный редактор в группе Запрос на вкладке Главная ленты.
Импорт преобразованных запросов
Когда вы удовлетворены преобразованными данными и готовы импортировать их в представление отчета Power BI Desktop, выберите Закрыть & Применить>Закрыть & Применить в вкладке ленты закрыть группу.
После загрузки данных запросы отображаются в списке полей в представлении отчета Power BI Desktop.
Управление связью между семантическими моделями
Power BI Desktop не требует объединения запросов для создания отчетов на их основе. Однако можно использовать связи между семантических моделей, основанными на общих полях, для расширения и обогащения отчетов. Power BI Desktop может автоматически обнаруживать связи или создавать их в диалоговом окне управления связями Power BI Desktop . Дополнительные сведения см. в статье Создание связей и управление ими в Power BI Desktop.
Общее поле ProductID создает связь между Orders и Products семантическими моделями этого учебника.
В представлении отчета в Power BI Desktop выберите Управление связями в области Связи на вкладке Моделирование ленты.
В диалоговом окне Управление связями вы увидите, что Power BI Desktop уже обнаружил и перечислил активную связь между таблицами Products и Orders. Чтобы просмотреть связь, выберите Изменить.
Редактировать связь открывается, отображая сведения о связи.
Power BI Desktop правильно автоматически определила связь, поэтому можно выбрать Отмена, а затем Закрыть.
В Power BI Desktop слева выберите Модель для просмотра связей запросов и управления ими. Дважды щелкните стрелку на линии, соединяющей два запроса, чтобы открыть диалоговое окно Изменить связь и просмотреть или изменить связь.
Чтобы вернуться к представлению отчета из представления модели, выберите значок отчета.
Создание визуализаций с помощью данных
Вы можете создавать различные визуализации в режиме просмотра Power BI Desktop, чтобы получить аналитику данных. Отчеты могут иметь несколько страниц, и каждая страница может иметь несколько визуальных элементов. Вы и другие пользователи могут взаимодействовать с визуализациями, чтобы проанализировать и понять данные. Дополнительные сведения см. в разделе Взаимодействие с отчетом в режиме редактирования в службе Power BI.
Вы можете использовать оба набора данных, а также связь между ними, чтобы визуализировать и анализировать данные о продажах.
Сначала создайте столбчатую диаграмму с накоплением, которая использует поля из обоих запросов для отображения количества каждого заказанного продукта.
Выберите поле Количество из Заказы в области Поля справа или перетащите его на пустое место на холсте. Создается столбчатая диаграмма с накоплением, показывающая общее количество всех заказанных продуктов.
Чтобы отобразить количество заказанных продуктов, выберите ProductName из Products в области полей или перетащите его на диаграмму.
Чтобы отсортировать продукты от самых заказываемых к наименее заказываемым, выберите Дополнительные параметры многоточие (...) в правом верхнем углу визуализации, а затем выберите Сортировать по>количеству.
Используйте ручки в углах диаграммы, чтобы увеличить её и чтобы было видно больше названий продуктов.
Затем создайте диаграмму, показывающую суммы заказов в долларах (LineTotal) с течением времени (OrderDate).
Если на холсте ничего не выбрано, выберите LineTotal из Orders в области Поля или перетащите его на пустое место на холсте. На столбчатой диаграмме с накоплением показана общая сумма всех заказов в долларах.
Выберите составную диаграмму, а затем выберите OrderDate из Заказовили перетащите его на диаграмму. На диаграмме отображаются итоги по строкам для каждой даты заказа.
Перетащите угла, чтобы изменить размер визуализации и просмотреть дополнительные данные.
Совет (assuming "Tip" is intended as advice)
Если на диаграмме отображаются только года и только три точки данных, щелкните стрелку рядом с OrderDate в поле оси области визуализаций и выберите OrderDate вместо иерархии дат. Или может потребоваться выбрать Параметры и настройки > Параметры из меню Файл, а в разделe Загрузка данныхснять флажок Автоматическая дата/время для новых файлов.
Наконец, создайте визуализацию карты, показывающую объемы заказов из каждой страны или региона.
Если на холсте ничего не выбрано, выберите ShipCountry из Orders в Fields или перетащите его в пустую область на холсте. Power BI Desktop обнаруживает, что данные являются именами стран или регионов. Затем она автоматически создает визуализацию карты, где каждая страна или регион с заказами представлены точкой данных.
Чтобы размеры точек данных отражали объемы заказов в каждой стране или регионе, перетащите поле LineTotal на карту. Вы также можете перетащить его в Добавить поля данных здесь в разделе Размер в области визуализаций. Размеры кругов на карте теперь отражают суммы долларов заказов из каждой страны или региона.
Взаимодействуйте с визуальными элементами отчета для дальнейшего анализа
В Power BI Desktop вы можете взаимодействовать с визуальными элементами, которые перекрестно выделяют и фильтруют друг друга, чтобы выявить дополнительные тенденции. Дополнительные сведения см. в фильтрах и подсветке отчетов Power BI.
Из-за связи между запросами взаимодействие с одной визуализацией влияет на все остальные визуализации на странице.
На визуализации карты выберите круг, центрированный в Канаде. Остальные две визуализации фильтруются, чтобы выделить итоговые данные для Канады и количество заказов.
Выберите продукт на диаграмме количества по продукту, чтобы отобразить карту и применить фильтр на графике дат, отражающий данные этого продукта. Выберите дату диаграммы LineTotal по OrderDate, чтобы просмотреть карту и установить фильтр диаграммы продукта для отображения данных на эту дату.
Совет
Чтобы очистить выделение, снова выберите его или выберите одну из других визуализаций.
Завершение отчета по анализу продаж
Полный отчет объединяет данные из файла Excel Products.xlsx и потока данных OData Northwind во визуальных элементах, которые помогают анализировать информацию о заказах в разных странах или регионах, временные рамки и продукты. Когда отчет будет готов, вы можете отправить его в службу Power BI для предоставления общего доступа другим пользователям Power BI.