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

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

Примечание :  Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция "Скачать и преобразовать" в Excel 2016.

В этом учебнике вы использовать редактор запросов Power Query для импорта данных из локальных файла Excel, которая содержит сведения о продукте, а из канала OData, сведения о заказе продукта. Выполните действия, преобразование и статистической обработки и объединять данные из обоих источников для создания отчета Total Sales per Product и год.

Для выполнения заданий из этого учебника нужна книга Products and Orders. В диалоговом окне Сохранение документа присвойте файлу имя Products and Orders.xlsx.

В этом учебнике

Задача 1. Импорт товаров в книгу Excel

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

Шаг 2. Преобразование первой строки в заголовки столбцов таблицы

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

Созданные шаги Power Query

Шаг 4. Импорт запроса товаров

Задача 2. Импорт данных о заказах из веб-канала OData

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

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

Ссылка для развертывания таблицы Order_Details

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

Удаление выделенных столбцов

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

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

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

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

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

Итоговый запрос

Созданные шаги Power Query

Шаг 8. Отключение скачивания запросов в книгу Excel

Отключение скачивания запроса

Задача 3. Объединение запросов Products и Total Sales

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

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

Ссылка для развертывания таблицы NewColumn

Созданные шаги Power Query

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

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

Итоговый запрос Total Sales per Product

Задача 1. Импорт товаров в книгу Excel

В этой задаче нужно импортировать товары из файла Products and Orders.xlsx в книгу Excel.

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

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

  2. На вкладке ленты POWER QUERY щелкните Из файла > Из Excel.

  3. В диалоговом окне Excel найдите или введите путь к файлу Products and Orders.xlsx, чтобы импортировать его или создать на него ссылку.

  4. В области Навигатор дважды щелкните лист Products или щелкните Products и выберите Изменить запрос. Когда вы изменяете запрос или подключаетесь к новому источнику данных, открывается окно Редактор запросов.

    Примечание : В конце этой статьи есть небольшое видео о том, как вывести редактор запросов.

Шаг 2. Преобразование первой строки в заголовки столбцов таблицы

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

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

  2. Щелкните Использовать первую строку в качестве заголовков.

Преобразование первой строки в заголовки столбцов таблицы

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

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

  1. В области предварительного просмотра запроса выберите столбцы ProductID, ProductName, CategoryID и QuantityPerUnit. Для этого щелкните их, удерживая нажатой клавишу CTRL или SHIFT.

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

    Скрытие других столбцов

Созданные шаги Power Query

По мере выполнения действий с запросом в Power Query создаются шаги запроса, которые указываются на панели Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ. Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о языке формул Power Query см. в статье Формулы Power Query.

Задача

Шаг запроса

Формула

Подключение к книге Excel

Источник

Source{[Name="Products"]}[Data]

Преобразование первой строки в заголовки столбцов таблицы

FirstRowAsHeader

Table.PromoteHeaders

(Products)

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

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Шаг 4. Импорт запроса товаров

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

  1. На ленте редактора запросов нажмите кнопку Применить и закрыть. Результаты появятся на новом листе Excel.

К началу страницы

Задача 2. Импорт данных о заказах из веб-канала OData

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

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

  1. На вкладке ленты POWER QUERY щелкните Из других источников > Из канала OData.

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

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

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

Примечание :  При наведении указателя мыши на таблицу вы увидите всплывающее окно с предварительным просмотром таблицы.

Наведение указателя на источник данных

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

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

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

Развертывание таблицы Order_Details

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

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

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

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

    1. Чтобы очистить все столбцы, щелкните (Выделить все столбцы).

    2. Щелкните ProductID, UnitPrice и Quantity.

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

      Ссылка для развертывания таблицы Order_Details

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

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

В этом шаге вы удалите все столбцы, кроме OrderDate, ProductID, UnitPrice и Quantity. В предыдущей задаче вы использовали команду Удалить другие столбцы. Для выполнения этой задачи вы удалите выделенные столбцы.

Удаление выделенных столбцов

  1. В области предварительного просмотра запроса выберите все столбцы:

    1. Щелкните первый столбец (OrderID).

    2. Щелкните последний столбец (Shipper), удерживая клавишу SHIFT.

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

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

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

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

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

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

  2. Щелкните Вставить столбец > Пользовательский.

  3. В диалоговом окне Вставить пользовательский столбец в поле Пользовательская формула столбца введите [Order_Details.UnitPrice] * [Order_Details.Quantity].

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

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

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

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

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

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

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

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

    2. щелкните столбец OrderDate правой кнопкой мыши, выберите команду Переименовать и введите Year.

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

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

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

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

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

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

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

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

    Диалоговое окно "Группировать по" для операций агрегирования

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

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

  1. На панели Параметры запроса в текстовом поле Имя введите Total Sales.

Итоговый запрос

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

Total Sales

Созданные шаги Power Query

По мере выполнения действий с запросом в Power Query создаются шаги запроса, которые указываются на панели Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ. Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о языке формул Power Query см. в статье Формулы Power Query.

Задача

Шаг запроса

Формула

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

Источник

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

InsertedColumns

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Преобразование столбца OrderDate для вывода года

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

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

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Шаг 8. Отключение скачивания запросов в книгу Excel

Так как запрос Total Sales не позволяет получить окончательный отчет Total Sales per Product and Year, нужно отключить его скачивание в книгу Excel. Если для переключателя Загрузка на лист в области Параметры запроса установлено значение Отключено, данные, полученные в результате этого запроса, не скачиваются, но сам запрос можно объединять с другими для получения желаемого результата. Вы узнаете, как объединить этот запрос с запросом Products, в следующей задаче.

Отключение скачивания запроса

  1. На панели Параметры запроса снимите флажок Загрузка на лист.

  2. На ленте редактора запросов нажмите кнопку Применить и закрыть. В области Запросы книги в запросе Total Sales отображается текст Загрузка отключена.

    Отключение скачивания запроса

К началу страницы

Задача 3. Объединение запросов Products и Total Sales

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

В этой задаче вы объедините запросы Products и Total Sales с помощью шага запроса Слияние или Расширить.

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

  1. В книге Excel перейдите к запросу Products на листе Лист2.

  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 анализирует каждый источник данных и определяет для него уровень конфиденциальности: общедоступный, организационный и частный. Дополнительные сведения об уровнях конфиденциальности см. в статье Уровни конфиденциальности.

    Диалоговое окно "Слияние"

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

Завершение слияния

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

В этом шаге нужно развернуть столбец слияния с именем NewColumn для создания двух новых столбцов в запросе Products: Year и Total Sales.

Ссылка для развертывания таблицы NewColumn

  1. В области предварительного просмотра запроса щелкните значок развертывания NewColumn ( Расширить ).

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

    1. Чтобы очистить все столбцы, щелкните (Выделить все столбцы).

    2. Щелкните Year и Total Sales.

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

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

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

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

Ссылка для развертывания таблицы

Созданные шаги Power Query

По мере выполнения действий слияния с запросом в Power Query создаются шаги запроса, которые указываются на панели Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ. Каждому шагу запроса соответствует формула Power Query, что также называют языком "M". Дополнительные сведения о языке формул Power Query см. в статье Формулы Power Query.

Задача

Шаг запроса

Формула

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

Source (источник данных для операции Слияние)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Развертывание столбца слияния

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

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

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

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

  1. На панели Параметры запроса снимите флажок Загрузка на лист и установите флажок Загрузить модель данных.

  2. Чтобы загрузить запрос в модель данных Excel, нажмите Применить и закрыть.

Загрузка модели данных Excel

Итоговый запрос Total Sales per Product

После выполнения всех шагов у вас будет запрос Total Sales per Product, который объединяет данные из файла Products and Orders.xlsx и канала OData Northwind. Этот запрос можно применять к модели Power Pivot. Кроме того, при изменении запроса в Power Query итоговая таблица в модели Power Pivot будет изменяться и обновляться.

К началу страницы

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

Отображение редактора запросов в Excel

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×