Сведения об объединении нескольких источников данных (Power Query)

В этой задаче вы импортируете продукты из файла Products and Orders.xlsx (скачанный и переименованный выше) в книгу Excel, продвигаете строки до заголовков столбцов, удаляете некоторые столбцы и загружаете запрос на лист.

Шаг 1. Подключение к книге Excel

1. Создайте книгу Excel.

2. Выберите Data > Get Data > from File > From Workbook (Получить > данных из книги).

3. В диалоговом окне Импорт данных найдите скачанный Products.xlsx файл и нажмите кнопку Открыть.

4. В области Навигатор дважды щелкните таблицу Продукты . Появится Редактор запросовPower.

Шаг 2. Изучение шагов запроса

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

1. Щелкните правой кнопкой мыши шаг Источник и выберите Изменить параметры. Этот шаг был создан при импорте книги.

2. Щелкните правой кнопкой мыши шаг навигации и выберите Изменить параметры. Этот шаг был создан при выборе таблицы в диалоговом окне Навигация .

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

Шаг 3. Удаление ненужных столбцов

В этом шаге вы удалите все столбцы, кроме ProductID, ProductName, CategoryID и QuantityPerUnit.

1. В предварительном просмотре данных выберите столбцы ProductID, ProductName, CategoryID и QuantityPerUnit (нажмите клавиши CTRL или SHIFT+щелчок).

2. Выберите Удалить столбцы > Удалить другие столбцы.

   

Шаг 4. Загрузка запроса на продукты

На этом шаге вы загрузите запрос Products на лист Excel.

• Выберите Главная > Закрыть & загрузить. Запрос отображается на новом листе Excel.

Сводка: Power Query шаги, созданные в задаче 1

При выполнении действий запроса в Power Query шаги запроса создаются и перечислены в области Параметры запроса в списке Примененные шаги. Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о формулах Power Query см. в статье Создание формул Power Query в Excel.

Задача	Шаг запроса	Формула
Импорт книги Excel	Источник данных	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Выберите таблицу Products	Переход.	= 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"})

В этой задаче вы импортируете данные в книгу Excel из примера веб-канала OData Northwind на http://services.odata.org/Northwind/Northwind.svc,разверните таблицу Order_Details, удалите столбцы, вычисляете итог строки, преобразуете orderDate, группируете строки по ProductID и Year, переименовываете запрос и отключаете скачивание запроса в книгу Excel.

Шаг 1. Подключение к веб-каналу OData

1. Выберите Данные > Получить> данных из других источников > из веб-канала OData.

2. В диалоговом окне Канал OData введите URL-адрес канала OData Northwind.

3. Нажмите кнопку ОК.

4. В области Навигатор дважды щелкните таблицу Заказы .

Шаг 2. Развертывание таблицы Order_Details

В этом шаге вы развертываете таблицу Order_Details, которая относится к таблице Orders, чтобы объединить столбцы ProductID, UnitPrice и Quantity из таблицы Order_Details с таблицей Orders. Операция Расширить объединяет столбцы из связанной таблицы с конечной таблицей. При выполнении запроса строки из связанной таблицы (Order_Details) объединяются в строки с основной таблицей (Orders).

В Power Query столбец, содержащий связанную таблицу, имеет значение Record или Table в ячейке. Они называются структурированные столбцы. Запись указывает на одну связанную запись и представляетсвязь "один к одному" с текущими данными или первичной таблицей. Таблица указывает связанную таблицу и представляет связь "один ко многим" с текущей или основной таблицей. Структурированный столбец представляет связь в источнике данных с реляционной моделью. Например, структурированный столбец указывает на сущность со связью внешнего ключа в веб-канале OData или связь внешнего ключа в базе данных SQL Server.

После развертывания таблицы Order_Details в таблицу Orders добавляются три новых столбца и дополнительные строки (по одному столбцу для каждой строки во вложенной или связанной таблице).

1. В режиме предварительного просмотра данных прокрутите по горизонтали до Order_Details столбца.

2. В столбце Order_Details щелкните значок развертывания ().

3. В раскрывающемся списке Расширить:

   1. Выберите (Выбрать все столбцы), чтобы очистить все столбцы.

   2. Выберите ProductID, UnitPrice и Quantity.

   3. Нажмите кнопку ОК.

      

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

Шаг 3. Удаление ненужных столбцов

В этом шаге вы удалите все столбцы, кроме OrderDate, ProductID, UnitPrice и Quantity. 

1. В разделе Предварительный просмотрданных выберите следующие столбцы: 

   1. Выберите первый столбец OrderID.

   2. SHIFT+Щелкните последний столбец Shipper.

   3. Щелкните столбцы OrderDate, Order_Details.ProductID, Order_Details.UnitPrice и Order_Details.Quantity, удерживая клавишу CTRL.

2. Щелкните правой кнопкой мыши заголовок выбранного столбца и выберите Удалить другие столбцы.

Шаг 4. Вычисление общей суммы для каждой строки Order_Details

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

1. В разделе Предварительный просмотр данных выберите значок таблицы () в левом верхнем углу предварительного просмотра.

2. Щелкните Добавить настраиваемый столбец.

3. В диалоговом окне Настраиваемый столбец в поле Формула настраиваемого столбца введите [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. В поле Имя нового столбца введите Итог по строке.

5. Нажмите кнопку ОК.

Шаг 5. Преобразование столбца OrderDate в столбец года

В этом шаге вы преобразуете столбец OrderDate для отображения года заказа.

1. В предварительном просмотре данных щелкните правой кнопкой мыши столбец OrderDate и выберите Преобразовать > год.

2. Переименуйте столбец OrderDate в Year:

   1. Дважды щелкните столбец OrderDate и введите Year или

   2. Right-Click в столбце OrderDate(Дата заказа ), выберите Переименовать и введите Год.

Шаг 6. Группировка строк по значениям ProductID и Year

1. В предварительной версии данных выберите Год и Order_Details.ProductID.

2. Right-Click один из заголовков и выберите Группировать по.

3. В диалоговом окне Группировать по:

   1. В текстовом поле Имя нового столбца введите Total Sales.

   2. В раскрывающемся списке Операция выберите Сумма.

   3. В раскрывающемся списке Столбец выберите Line Total.

4. Нажмите кнопку ОК.

   

Шаг 7. Переименование запроса

Перед импортом данных о продажах в Excel переименуйте запрос:

• В области Параметры запроса в поле Имя введите Всего продаж.

Результаты: окончательный запрос для задачи 2

После выполнения всех шагов у вас будет запрос Total Sales для канала OData Northwind.

Сводка: 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"])
Выбор таблицы	Навигация	= source{[Name="Orders"]}[Data]
Развертывание таблицы 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", каждый [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", каждый [Order_Details.UnitPrice] * [Order_Details.Quantity])
Измените на более понятное имя Lne Total	Переименованные столбцы	= Table.RenameColumns(InsertedCustom,{"Custom", "Line Total"}})
Преобразование столбца 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}})

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

В этой задаче вы объедините запросы Products и Total Sales с помощью запроса слияния и операции expand , а затем загрузите запрос Total Sales per Product в модель данных Excel.

Шаг 1. Слияние ProductID с запросом Total Sales

1. В книге Excel перейдите к запросу "Продукты " на вкладке "Продукты ".

2. Выберите ячейку в запросе, а затем выберите Запрос > Объединить.

3. В диалоговом окне Слияние выберите Продукты в качестве основной таблицы и выберите Total Sales в качестве дополнительного или связанного запроса для слияния. Всего продаж станет новым структурированным столбцом со значком развертывания.

4. Чтобы сопоставить Total Sales и Products по столбцу ProductID, выберите столбец ProductID в таблице Products и столбец Order_Details.ProductID в таблице Total Sales.

5. В диалоговом окне Уровни конфиденциальности:

   1. Выберите Организационный в качестве уровня изоляции для обоих источников данных.

   2. Нажмите кнопку Сохранить.

6. Нажмите кнопку ОК.

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

   

Result (Результат)

Операция слияния создает запрос. Результат запроса содержит все столбцы из основной таблицы (Products) и один структурированный столбец таблицы со связанной таблицей (Total Sales). Щелкните значок Развернуть , чтобы добавить новые столбцы в основную таблицу из дополнительной или связанной таблицы.

Шаг 2. Развертывание объединенного столбца

На этом шаге вы развернете объединенный столбец с именем NewColumn , чтобы создать два новых столбца в запросе Products : Year и Total Sales.

1. В разделе Предварительный просмотр данных выберите значок развернуть () рядом с элементом NewColumn.

2. В раскрывающемся списке Развернуть :

   1. Выберите (Выбрать все столбцы), чтобы очистить все столбцы.

   2. Выберите Год и Всего продаж.

   3. Нажмите кнопку ОК.

3. Переименуйте эти два столбца в Year и Total Sales.

4. Чтобы узнать, какие продукты и за какие годы получили наибольший объем продаж, выберите Сортировать по убыванию по общему объему продаж.

5. Выберите команду Переименовать, чтобы переименовать запрос в Total Sales per Product.

Result (Результат)

Шаг 3. Загрузка запроса Total Sales per Product в модель данных Excel

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

1. Выберите Главная > Закрыть & Загрузить.

2. В диалоговом окне Импорт данных выберите Добавить эти данные в модель данных. Для получения дополнительных сведений об использовании этого диалогового окна выберите вопросительный знак (?).

Result (Результат)

У вас есть запрос Total Sales per Product , который объединяет данные из файла Products.xlsx и веб-канала OData Northwind. Этот запрос применяется к модели PowerPivot. Кроме того, изменения запроса изменяют и обновляют результируемую таблицу в модели данных.

Сводка: 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", JoinKind.LeftOuter)
Развертывание столбца слияния	Развернутый общий объем продаж	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Переименование двух столбцов	Переименованные столбцы	= Table.RenameColumns(#"Expanded Total Sales",{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Сортировка общего объема продаж по возрастанию	Отсортированные строки	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})