В этом учебнике используйте редактор запросов Power Query для импорта данных из локального файла Excel, содержащего сведения о продукте, и из веб-канала OData, содержащего информацию о заказе продукта. Выполняйте этапы преобразования и агрегирования и объединяйте данные из обоих источников для создания отчета " Общий объем продаж по каждому продукту и году ".
Чтобы ознакомиться с этим руководством, вам понадобится книга "Товары ". В диалоговом окне Сохранение документа присвойте файлу имя Products and Orders.xlsx.
Задача 1. Импорт товаров в книгу Excel
В ней товары импортируются из файла "Продукты и Orders.xlsx " (скачанного и переименованного в предыдущем разделе) в книгу Excel. Затем строки можно преобразовать в заголовки столбцов, удалить некоторые столбцы и загрузить запрос на лист.
Шаг 1. Подключение к книге Excel
- Создайте книгу Excel.
- Выберите элемент "Данные>" "Получить данные>из файла>из книги".
- В диалоговом окне "Импорт данных " найдите Products.xlsx файл, который вы скачали, а затем нажмите кнопку "Открыть".
- В области "Навигатор " дважды щелкните таблицу "Продукты ". Откроется редактор Power Query.
По умолчанию Power Query автоматически добавляет несколько действий для вашего удобства. Изучите каждый шаг в разделе "Примененные шаги " в панели параметров запроса , чтобы узнать больше.
- Щелкните правой кнопкой мыши на шаге "Источник" и выберите пункт "Изменить параметры". Этот шаг был создан при импорте книги.
- Щелкните правой кнопкой мыши на шаге навигации и выберите пункт "Изменить параметры". Этот шаг создается при выборе таблицы в диалоговом окне навигации .
- Щелкните правой кнопкой мыши на шаге "Измененный тип " и выберите "Изменить параметры". Этот шаг был создан Power Query, который вывел типы данных каждого столбца. Щелкните стрелку вниз справа от строки формул, чтобы просмотреть всю формулу.
Шаг 3. Удаление других столбцов для отображения только интересующих столбцов
На этом шаге удаляются все столбцы, кроме ProductID,ProductName, CategoryID и QuantityPerUnit.
- В режиме предварительного просмотра данных выберите столбцы "КодТовара","НазваниеТовара", "КодТипа" и "КоличествоPerUnit" (нажмите CTRL+CLICK или SHIFT+ЩЕЛЧОК).
- Выберите "Удалить столбцы",>"Удалить другие столбцы".
Шаг 4. Загрузка запроса продуктов
На этом шаге запрос "Продукты " загружается на лист Excel.
- Выберите "Главная",>"Закрыть& "Загрузить". Запрос появится на новом листе Excel.
Сводка. Действия Power Query, созданные в задаче 1
При выполнении запросов в Power Query он создает шаги запроса и перечисляет их в области параметров запроса в списке "Примененные шаги". Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о формулах Power Query см. в документации по Power Query.
| Задача | Шаг запроса | Формула |
|---|---|---|
| Импорт книги Excel | Источник данных | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Выберите таблицу "Продукты" | Переход. | = Source{[Item="Products",Kind="Table"]}[Data] |
| Power Query автоматически определяет типы данных столбцов | Changed Type | = Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued ", type logical}}) |
| Удаление ненужных столбцов | Удалены другие столбцы | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Задача 2. Импорт данных о заказах из веб-канала OData
В этой задаче вы импортируете данные в книгу Excel из образца веб-канала Northwind OData по адресу http://services.odata.org/Northwind/Northwind.svc, разверните Order_Details таблицу, удалите столбцы, вычислите итог по строке, преобразуйте OrderDate, группируйте строки по кодProductID и году, переименуйте запрос и отключите загрузку запроса в книгу Excel.
Шаг 1. Подключение к веб-каналу OData
- Выбор "Данные">Получение данных>из других источников>из веб-канала OData.
- В диалоговом окне Канал OData введите URL-адрес канала OData Northwind.
- Нажмите кнопку ОК.
- В области "Навигатор " дважды щелкните таблицу "Заказы ".
Шаг 2. Развертывание таблицы Order_Details
В этом шаге вы развертываете таблицу Order_Details, которая относится к таблице Orders, чтобы объединить столбцы ProductID, UnitPrice и Quantity из таблицы Order_Details с таблицей Orders. Операция Расширить объединяет столбцы из связанной таблицы с конечной таблицей. При выполнении запроса строки из связанной таблицы (Order_Details) объединяются в строки с главной таблицей (Заказы).
В Power Query столбец, содержащий связанную таблицу, имеет значение "Запись" или "Таблица" в ячейке. Такие столбцы называются структурированными. Запись обозначает одну связанную запись и представляет отношение "один-к-одному" с текущими данными или главной таблицей. Таблица указывает на связанную таблицу и представляет связь "один-ко-многим" с текущей или главной таблицей. Структурированный столбец представляет связь в источнике данных с реляционной моделью. Например, структурированный столбец указывает на сущность с связью внешнего ключа в веб-канале OData или связью внешнего ключа в базе данных SQL Server.
После развертывания Order_Details таблицы в таблицу "Заказы " добавляются три новых столбца и дополнительные строки — по одному на каждую строку вложенной или связанной таблицы.
В режиме предварительного просмотра данных прокрутите экран по горизонтали до Order_Details столбца.
В столбце Order_Details щелкните значок развертывания (
).В раскрывающемся списке Расширить:
Выберите (Выбрать все столбцы), чтобы очистить все столбцы.
Выберите "КодТовара","Цена" и "Количество".
Нажмите кнопку ОК.
Примечание
В Power Query можно развернуть таблицы, связанные из столбца, и агрегировать столбцы связанной таблицы, прежде чем развертывать данные в тематической таблице. Дополнительные сведения о выполнении агрегатных операций см. в разделе Агрегатные данные из столбца (Power Query).
Шаг 3. Удаление других столбцов для отображения только интересующих столбцов
На этом шаге удаляются все столбцы, кроме столбцов " ДатаЗаказа", "КодТовара","Цена" и "Количество ".
В области предварительного просмотра данных выберите следующие столбцы:
- Выделите первый столбец "КодЗаказа".
- SHIFT+ЩЕЛКНИТЕ последний столбец, Грузоотправитель.
- Щелкните столбцы OrderDate, Order_Details.ProductID, Order_Details.UnitPrice и Order_Details.Quantity, удерживая клавишу CTRL.
Щелкните правой кнопкой мыши заголовок выделенного столбца и выберите команду "Удалить другие столбцы".
Шаг 4. Вычисление итога по строке для каждой Order_Details строки
В этом шаге создается пользовательский столбец для вычисления общей суммы для каждой строки Order_Details.
- В режиме предварительного просмотра данных выберите значок таблицы (
) в левом верхнем углу окна предварительного просмотра. - Выберите "Добавить настраиваемый столбец".
- В диалоговом окне "Настраиваемый столбец " в поле "Формула настраиваемого столбца " введите [Order_Details.Цена] * [Order_Details.Количество].
- В поле "Имя нового столбца " введите "Итог по строке".
- Нажмите кнопку ОК.
Шаг 5. Преобразование столбца "ДатаЗаказа" год
В этом шаге вы преобразуете столбец OrderDate для отображения года заказа.
В режиме предварительного просмотра данных щелкните правой кнопкой мыши столбец "ДатаЗаказа " и выберите "Преобразовать>год".
Переименуйте столбец OrderDate в Year:
- Дважды щелкните столбец "Датазаказа " и введите "Год " или
- Щелкните правой кнопкой мыши столбец "ДатаЗаказа ", выберите "Переименовать" и введите "Год".
Шаг 6. Группировка строк по кодТовара и году
В представлении "Предварительные данные" выберите "Год " и "Order_Details.КодТовара".
Щелкните правой кнопкой мыши один из заголовков и выберите пункт "Группировать по".
В диалоговом окне Группировать по:
- В текстовом поле Имя нового столбца введите Total Sales.
- В раскрывающемся списке Операция выберите Сумма.
- В раскрывающемся списке Столбец выберите Line Total.
Нажмите кнопку ОК.
Перед импортом данных продаж в Excel переименуйте запрос:
- В области "Параметры запроса " в поле "Имя " введите "Общий объем продаж".
Результаты: последний запрос для задачи 2
После выполнения каждого шага у вас будет запрос "Общий объем продаж" по веб-каналу Northwind OData.
Сводка. Действия Power Query, созданные в задаче 2
При выполнении запросов в Power Query он создает шаги запроса и перечисляет их в области параметров запроса в списке "Примененные шаги". Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о формулах Power Query см. в документации по Power Query.
| Задача | Шаг запроса | Формула |
|---|---|---|
| Подключение к каналу OData | Источник | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Выбор таблицы | Навигация | = Источник{[Название="Заказы"]}[Данные] |
| Развертывание таблицы Order_Details | Развертывание Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Удаление ненужных столбцов | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Вычисление общей суммы для каждой строки Order_Details | Добавлен пользовательский |
= Table.AddColumn(RemovedColumns, "Custom", Each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Измените имя на более понятное, Lne Total | Переименованные столбцы | = Таблица.RenameColumns(InsertedCustom,{{"Custom", "Итог строки"}}) |
| Преобразование столбца OrderDate для вывода года | Извлеченный год | = Table.TransformColumns(#"Сгруппированные строки";{{"Year", Date.Year, Int64.Type}}) |
| Изменить на более осмысленные имена, OrderDate и Year |
Переименованные столбцы 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Группировка строк по значениям ProductID и Year | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
Задача 3. Объединение запросов Products и Total Sales
Power Query позволяет объединить несколько запросов, объединив или добавив их. Вы можете выполнить операцию слияния для любого запроса Power Query с табличной фигурой независимо от источника данных. Дополнительные сведения об объединении источников данных см. в разделе Объединение нескольких запросов (Power Query).
В этой задаче выполняется объединение запросов " Товары" и "Общий объем продаж " с помощью операции "Слияние " и " Расширение ", а затем загрузка запроса "Общий объем продаж по продукту" в модель данных Excel.
Шаг 1. Объединение поля ProductID в запрос "Общий объем продаж"
В книге Excel перейдите к запросу " Товары " на вкладке листа "Товары ".
Выделите ячейку в запросе и выберите команду "Объединить запрос>".
В диалоговом окне " Слияние " выберите "Товары " в качестве главной таблицы, а затем "Общий объем продаж " в качестве вторичного или связанного запроса для объединения. Общий объем продаж станет новым структурированным столбцом со значком развертывания.
Чтобы сопоставить Total Sales и Products по столбцу ProductID, выберите столбец ProductID в таблице Products и столбец Order_Details.ProductID в таблице Total Sales.
В диалоговом окне Уровни конфиденциальности:
- Выберите Организационный в качестве уровня изоляции для обоих источников данных.
- Нажмите кнопку Сохранить.
Нажмите кнопку ОК.
Примечание
Уровни конфиденциальности не позволяют пользователю случайно объединить данные из нескольких источников, которые могут быть частными или организационными. В зависимости от запроса пользователь может случайно отправить данные из частного источника данных в другой источник данных, который может быть вредоносным. Power Query анализирует каждый источник данных и классифицирует его по определенному уровню конфиденциальности: общедоступный, организационный и частный. Дополнительные сведения об уровнях конфиденциальности см. в разделе Настройка уровней конфиденциальности (Power Query).
Результат
В результате слияния создается запрос. Результаты запроса содержат все столбцы из главной таблицы ("Товары") и один структурированный столбец таблицы в связанной таблице ("Общий объем продаж"). Щелкните значок "Развернуть ", чтобы добавить в главную таблицу новые столбцы из вторичной или связанной таблицы.
Шаг 2. Развертывание объединенного столбца
На этом шаге необходимо развернуть объединенный столбец с именем NewColumn , чтобы создать два новых столбца в запросе "Товары ": "Год " и "Общий объем продаж".
В режиме предварительного просмотра данных выберите значок "Развернуть " (
) рядом с элементом "Создатьстолбец".В раскрывающемся списке "Развернуть ":
- Выберите (Выбрать все столбцы), чтобы очистить все столбцы.
- Выберите "Год" и "Общий объем продаж".
- Нажмите кнопку ОК.
Переименуйте эти два столбца в Year и Total Sales.
Чтобы узнать, какие продукты и в какие годы получили наибольший объем продаж, выберите сортировку по убываниюобщего объема продаж.
Выберите команду Переименовать, чтобы переименовать запрос в Total Sales per Product.
Результат
Шаг 3. Загрузка запроса "Общий объем продаж по продукту" в модель данных Excel
На этом шаге запрос загружается в модель данных Excel для создания отчета, связанного с результатами запроса. После загрузки данных в модель данных Excel можно использовать Power Pivot для анализа данных.
- Выберите "Главная",>"Закрыть& "Загрузить".
- В диалоговом окне "Импорт данных " выберите пункт "Добавить эти данные в модель данных". Чтобы узнать больше об использовании этого диалогового окна, выберите вопросительный знак (?).
Результат
У вас есть запрос " Общий объем продаж по продукту ", который объединяет данные из файла Products.xlsx и OData-канала Northwind. Этот запрос применяется к модели Power Pivot. Кроме того, изменения, вносимые в запрос, изменяют и обновляют результирующую таблицу в модели данных.
Сводка. Действия Power Query, созданные в задаче 3
При выполнении запросов слияния в Power Query шаги запроса создаются и перечисляются в области параметров запроса в списке "Примененные шаги". Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о формулах Power Query см. в документации по Power Query.
| Задача | Шаг запроса | Формула |
|---|---|---|
| Слияние ProductID с запросом Total Sales | Source (источник данных для операции Слияние) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales Kind", JoinKind.LeftOuter) |
| Развертывание столбца слияния | Увеличенный общий объем продаж | = Таблица.ExpandTableColumn(Источник, "Общие продажи", {"Год", "Общие продажи"}, {"Общие продажи.Год", "Общие продажи.Общие продажи"}) |
| Переименование двух столбцов | Переименованные столбцы | = Table.RenameColumns(#"Расширенный общий объем продаж";{{"Общий объем продаж.Год", "Год"}, {"Общий объем продаж.Общий объем продаж", "Общий объем продаж"}}) |
| Сортировать общий объем продаж по возрастанию | Отсортированные строки | = Таблица.Сорт(#"Переименованные столбцы";{{"Общий объем продаж", порядок.По возрастанию}}) |