Узнайте, как объединить несколько источников данных (Power Query)

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

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

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

2. Выберите Данные > Получить данные > из файла > из книги.

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

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

Шаг 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 см. вэтой Excel.

Задача	Шаг запроса	Формула
Импорт Excel книги	Исходное значение	= Excel. Книга(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"})

В этой задаче вы импортируете данные в книгу 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 столбец, содержащий связанную таблицу, содержит в ячейке значение Запись или Таблица. Они называются структурированные столбцы. Запись указывает на одну связанную запись и представляет связь "один-к-одному" с текущими данными или главной таблицей. Таблица указывает на связанную таблицу и представляет связь "один-к-многим" с текущей или главной таблицей. Структурированный столбец представляет связь в источнике данных с реляционной моделью. Например, структурированный столбец указывает объект с связью внешнего ключа в канале 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. В поле Имя нового столбца введите Line Total.

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

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

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

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

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

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

   2. Right-Click столбце OrderDate выберите Переименоватьи введите Year.

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

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

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

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

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

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

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

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

   

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

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

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

Результаты: окончательный запрос для задачи 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", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Изменить на более содержательное имя, Lne Total	Переименованные столбцы	= Table.RenameColumns(InsertedCustom,{"Custom", "Line Total"}})
Преобразование столбца OrderDate для вывода года	Извлеченный год	= Table.TransformColumns(#"Grouped Rows",{{"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 с помощью запроса слияния и операции Развернуть, а затем загружает запрос Total Sales per Product в Excel данных.

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

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

2. Выберите ячейку в запросе и выберите запрос> слияние.

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

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

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

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

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

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

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

   

Результат

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

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

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

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

2. В списке Развернуть:

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

   2. Выберите Year и Total Sales.

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

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

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

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

Результат

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

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

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

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

Результат

У вас есть запрос Total Sales per Product, объединяющий данные из 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", 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(#"Переименованные столбцы",{{"Total Sales", Order.Ascending}})