Загрузка данных в Power BI Desktop
Приблизительное время выполнения лабораторной работы – 45 минут.
В этой лабораторной работе вы начнете применять преобразования к каждому из запросов, созданных в предыдущей лабораторной работе. Затем вы примените запросы для загрузки каждого из них в виде таблицы в модель данных. После выполнения работы, вы будете знать, как:
- Применять различные трансформации
- Применять запросы, чтобы загрузить их в модель данных.
Для лабораторной работы вам понадобятся следующие материалы:
-
SQL сервер со следующими данными:
https://docs.microsoft.com/ru-ru/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms -
Набор файлов и расширений:
https://github.com/MicrosoftLearning/DA-100KO-Analyzing-Data-with-Power-BI/tree/master/Allfiles/DA-100-Allfiles
История лабораторной работы
Эта лабораторная работа – одна из многих в серии лабораторных работ, которые были разработаны в виде полного рассказа от подготовки данных до публикации в виде отчетов и информационных панелей. Вы можете выполнять лабораторные работы в любом порядке. Но, если вы намереваетесь выполнить несколько лабораторных работ, для первых 10 лабораторных работ мы предлагаем вам выполнить их в следующем порядке:
- Подготовка данных в Power BI Desktop.
- Загрузка данных в Power BI Desktop.
- Данные модели в Power BI Desktop, часть 1
- Данные модели в Power BI Desktop, часть 2
- Создание вычислений DAX в Power BI Desktop, часть 1
- Создание вычислений DAX в Power BI Desktop, часть 2
- Создание отчета в Power BI Desktop, часть 1
- Создание отчета в Power BI Desktop, часть 2
- Создание информационной панели Power BI.
- Создание отчета Power BI с разбивкой на страницы.
- Выполнение анализа данных в Power BI Desktop.
- Обеспечение безопасности на уровне строк
Упражнение 1: Загрузка данных
В этом упражнении вы примените преобразования к каждому из запросов, созданных в предыдущей лабораторной работе.
Задача 1. Начинаем
В этой задаче вы настроите среду для лабораторной работы.
Важно: если вы продолжаете предыдущую лабораторную работу (и вы ее уже успешно выполнили), не выполняйте эту задачу; вместо этого просто переходите к следующему заданию.
- Чтобы открыть Power BI Desktop, на панели задач щелкните на ярлык Microsoft Power BI Desktop.
- Чтобы закрыть окно начала работы, в левом верхнем углу окна нажмите X.
- Чтобы открыть начальный файл Power BI Desktop, щелкните на вкладку File на ленте, чтобы открыть представление Backstage.
- Выберите Open Report.
- Щелкните на Browse Reports.
- В окне Open перейдите в папку D:\DA100\Labs\02-load-data-with-power-query-in-power-bi-desktop\Starter.
- Выберите файл Sales Analysis.
- Щелкните на Open.
- Закройте все информационные окна, которые могут открыться.
- Обратите внимание на желтое предупреждающее сообщение под лентой.
Сообщение предупреждает вас о том, что запросы не применялись для загрузки в качестве таблиц модели. Вы примените запросы позже в этой лабораторной работе.
- Чтобы закрыть предупреждающее сообщение, справа от желтого предупреждающего сообщения нажмите X.
- Чтобы создать копию файла, щелкните на вкладке File на ленте, чтобы открыть представление Backstage.
- Выберите Save As.
- Если будет предложено применить изменения, нажмите Apply Later.
- В окне Save As перейдите в папку D:\DA100\MySolution.
- Щелкните на Save.
- Чтобы открыть окно Power Query Editor, на вкладке ленты Home в группе Queries щелкните на значок Transform Data.
Задача 2: Настроить запрос продавца
В этой задаче вы настроите запрос продавца.
- В окне Power Query Editor на панели Queries выберите запрос DimEmployee .
- Чтобы переименовать запрос, на панели Query Settings (расположенной справа) в поле Name замените текст на Salesperson и нажмите Enter.
Имя запроса будет определять имя таблицы модели. Рекомендуется давать краткие, но понятные имена.
- На панели Queries убедитесь, что имя запроса обновлено.
Теперь вы отфильтруете строки запроса, чтобы получить только сотрудников, которые являются продавцами.
- Чтобы найти определенный столбец, на вкладке ленты Home в группе Manage Columns щелкните на стрелке вниз Choose Columns и выберите Go to Column.
Совет: этот метод полезен, когда в запросе есть много столбцов. Если столбцов не слишком много, вы можете просто прокрутить их по горизонтали, чтобы найти интересующий столбец.
- В окне Go to Column, чтобы отсортировать список по имени столбца, нажмите кнопку AZ и выберите Name.
- Выберите столбец SalesPersonFlag и нажмите OK.
- Чтобы отфильтровать запрос, в заголовке столбца SalesPersonFlag щелкните на стрелке вниз и снимите флажок FALSE.
- Щелкните OK.
- На панели Query Settings в списке Applied Steps обратите внимание на добавленный шаг Filtered Rows .
Каждое преобразование, которое вы создаете, приводит к дополнительной пошаговой логике. Есть возможность редактировать или удалять шаги. Также можно выбрать шаг для предварительного просмотра результатов запроса на этом этапе преобразования запроса.
- Чтобы удалить столбцы, на вкладке ленты Home в группе Manage Columns щелкните на значок Choose Columns .
- В окне Choose Columns, чтобы снять отметку со всех столбцов, снимите флажок с пункта (Select All Columns).
-
Чтобы включить столбцы, отметьте следующие шесть столбцов:
- EmployeeKey
- EmployeeNationalIDAlternateKey
- FirstName
- LastName
- Title
- EmailAddress
- Щелкните OK.
- В списке Applied Steps обратите внимание на добавление еще одного шага запроса.
- Чтобы создать столбец с одним именем, сначала выберите заголовок столбца FirstName.
- Удерживая нажатой клавишу Ctrl, выберите столбец LastName.
- Щелкните правой кнопкой мыши любой из заголовков выбранных столбцов, а затем в контекстном меню выберите Merge Columns.
Многие общие преобразования можно применить, щелкнув правой кнопкой мыши на заголовок столбца и выбрав их из контекстного меню. Однако обратите внимание, что на ленте доступно больше преобразований.
- В окне Merge Columns в раскрывающемся списке Separator выберите Space.
- В поле New Column Name замените текст на Salesperson.
- Щелкните OK.
- Чтобы переименовать столбец EmployeeNationalIDAlternateKey, дважды щелкните на заголовке столбца EmployeeNationalIDAlternateKey.
- Замените текст на EmployeeID и нажмите Enter.
Важно! Получив указание переименовать столбцы, важно, чтобы вы переименовали их в точности, как описано.
- Используйте предыдущие шаги, чтобы переименовать столбец EmailAddress в UPN.
UPN – это аббревиатура от имени участника-пользователя. Значения в этом столбце будут использоваться при настройке безопасности на уровне строк в «Данных модели в Power BI Desktop, часть 2».
- В левом нижнем углу строки состояния убедитесь, что запрос содержит пять столбцов и 18 строк.
Важно: важно не продолжать, если ваш запрос не дает правильного результата – выполнить последующие лабораторные работы будет невозможно. Если столбцы или строки запроса не совпадают, вернитесь к шагам в этой задаче, чтобы исправить любые проблемы.
Задача 3. Настройка запроса SalespersonRegion.
В этой задаче вы настроите запрос SalespersonRegion.
- На панели Queries выберите запрос DimEmployeeSalesTerritory,.
- На панели Query Settings переименуйте запрос в SalespersonRegion.
- Чтобы удалить последние два столбца, сначала выберите заголовок столбца DimEmployee.
- Удерживая нажатой клавишу Ctrl, выберите заголовок столбца DimSalesTerritory .
- Щелкните правой кнопкой мыши на любой из заголовков выбранных столбцов, а затем в контекстном меню выберите Remove Columns.
- В строке состояния убедитесь, что запрос содержит два столбца и 39 строк.
Задача 4: Настроить запрос продукта
В этой задаче вы настроите запрос Product.
Важно: когда подробные инструкции уже предоставлены, действия в лабораторных работах будут содержать более краткие инструкции. Если вам нужны подробные инструкции, вы можете вернуться к предыдущим задачам.
- Выберите запрос DimProduct .
- Переименуйте запрос на Product.
- Найдите столбец FinishedGoodsFlag и затем отфильтруйте столбец, чтобы получить продукты, которые являются готовой продукцией (т. е. ИСТИНА).
-
Удалите все столбцы, кроме следующих:
- ProductKey
- EnglishProductName
- StandardCost
- Color
- DimProductSubcategory
- Обратите внимание, что столбец DimProductSubcategory представляет связанную таблицу (он содержит ссылки на значения).
- В заголовке столбца DimProductSubcategory справа от имени столбца нажмите кнопку «Развернуть».
- Чтобы снять отметку со всех столбцов, снимите отметку с пункта (Select All Columns).
- Проверьте столбцы EnglishProductSubcategoryName и DimProductCategory.
При выборе этих двух столбцов будет применено преобразование для присоединения к таблице DimProductSubcategory, а затем будут включены эти столбцы. Столбец DimProductCategory, по сути, является еще одной связанной таблицей в источнике данных.
- Снимите флажок Use Original Column Name as Prefix.
Если оставить этот флажок, то он будет указывать расширенное имя столбца перед каждым столбцом (в данном случае DimProductSubcategory). Поскольку известно, что имена выбранных столбцов не конфликтуют с именами столбцов в запросе Product, этот параметр не выбран.
- Щелкните ОК.
- Обратите внимание, что преобразование привело к добавлению двух столбцов и что столбец DimProductSubcategory удален.
- Разверните столбец DimProductCategory, а затем введите только столбец EnglishProductCategoryName .
-
Переименуйте следующие четыре столбца:
- EnglishProductName на Product
- StandardCost на Standard Cost (вместе с пробелом)
- EnglishProductSubcategoryName на Subcategory
- EnglishProductCategoryName на Category o EnglishProductName to Product
- В строке состояния убедитесь, что запрос содержит шесть столбцов и 397 строк.
Задача 5: Настроить запрос реселлера
В этой задаче вы настроите запрос Reseller.
- Выберите запрос DimReseller.
- Переименуйте запрос в Reseller.
-
Удалите все столбцы, кроме следующих:
- ResellerKey
- BusinessType
- ResellerName
- DimGeography
- Разверните столбец DimGeography, чтобы включить в него только следующие три столбца:
- City
- StateProvinceName
- EnglishCountryRegionName
- В заголовке столбца Business Type щелкните стрелку вниз, затем просмотрите отдельные значения столбца и обратите внимание на неправильное написание слова «склад».
- Щелкните правой кнопкой мыши заголовок столбца Business Type и выберите Replace Values.
-
В окне Replace Values настройте следующие значения:
- В поле Value to Find введите Ware House.
- В поле Replace With введите Ware House.
- ЩелкнитеOK.
-
Переименуйте следующие четыре столбца:
- BusinessType на Business Type (включите пробел)
- ResellerName на Reseller
- StateProvinceName на State-Province
- EnglishCountryRegionName на Country-Region
- В строке состояния убедитесь, что запрос содержит шесть столбцов и 701 строку.
Задача 6: Настроить запрос региона
В этой задаче вы настроите запрос Region.
- Выберите запрос DimSalesTerritory.
- Переименуйте запрос в Region.
- Примените фильтр к столбцу SalesTerritoryAlternateKey, чтобы удалить значение 0 (ноль).
-
Удалите все столбцы, кроме следующих:
- SalesTerritoryKey
- SalesTerritoryRegion
- SalesTerritoryCountry
- SalesTerritoryGroup
- Переименуйте следующие три столбца:
- SalesTerritoryRegion на Region
- SalesTerritoryCountry на Country
- SalesTerritoryGroup на Group
- В строке состояния убедитесь, что запрос содержит четыре столбца и 10 строк.
Задача 7: Настроить запрос продаж
В этой задаче вы настроите запрос Sales.
- Выберите запрос FactResellerSales.
- Переименуйте запрос в Sales.
-
Удалите все столбцы, кроме следующих:
- SalesOrderNumber
- OrderDate
- ProductKey
- ResellerKey
- EmployeeKey
- SalesTerritoryKey
- OrderQuantity
- UnitPrice
- TotalProductCost
- SalesAmount
- DimProduct
Вы можете вспомнить в лабораторной работе «Подготовка данных в Power BI Desktop», что у небольшого процента строк FactResellerSales отсутствовали значения TotalProductCost. Столбец DimProduct был включен для извлечения столбца стандартной стоимости продукта, чтобы помочь исправить отсутствующие значения.
- Разверните столбец DimProduct, снимите флажки со всех столбцов и включите только столбец StandardCost .
- Чтобы создать настраиваемый столбец, на вкладке ленты Add Column в группе General щелкните Custom Column.
- В окне Custom Column в поле New Column Name замените текст на Cost.
- В поле Custom Column Formula введите следующее выражение (после символа равенства):
- Для вашего удобства вы можете скопировать выражение из файла D:\DA100\Labs\02-load-data-with-power-query-in-power-bi-desktop\Assets\Snippets.txt.
Power Query
CodeCopy
if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]
Это выражение проверяет, отсутствует ли значение TotalProductCost. Если это так, производит значение путем умножения значения OrderQuantity на значение StandardCost; в противном случае используется существующее значение TotalProductCost .
- Щелкните OK.
-
Удалите следующие два столбца:
- TotalProductCost
- StandardCost
- Переименуйте следующие три столбца:
- OrderQuantity на Quantity
- UnitPrice на Unit Price (включая пробел)
- SalesAmount на Sales
- Чтобы изменить тип данных столбца, в заголовке столбца Quantity слева от имени столбца щелкните значок 1.2, а затем выберите Whole Number».
Важно правильно настроить тип данных. Если столбец содержит числовое значение, также важно выбрать правильный тип, если вы планируете выполнять математические вычисления.
-
Измените следующие три типа данных столбца на Fixed Decimal Number.
- Unit Price
- Sales
- Cost
Тип данных с фиксированным десятичным числом хранит значения с полной точностью и поэтому требует больше места для хранения, чем десятичное число. Важно использовать тип фиксированного десятичного числа для финансовых значений или курсов (например, обменных курсов).
- В строке состояния убедитесь, что запрос содержит 10 столбцов и более 999 строк.
В качестве данных предварительного просмотра для каждого запроса будет загружено не более 1000 строк.
Задача 8: настроить запрос целей
В этой задаче вы настроите запрос Targets.
- Выберите запрос ResellerSalesTargets.
- Переименуйте запрос в Targets.
- Чтобы развернуть столбцы за 12 месяцев (M01-M12), сначала выберите несколько заголовков столбцов Year и EmployeeID.
- Щелкните правой кнопкой мыши любой из заголовков выбранных столбцов, а затем в контекстном меню выберите Unpivot Other Columns.
- Обратите внимание, что имена столбцов теперь отображаются в столбце Attribute, а значения – в столбце Value.
- Примените фильтр к столбцу Value, чтобы удалить значения с дефисом (-).
Вы можете вспомнить, что символ дефиса использовался в исходном CSV-файле для обозначения нуля (0).
-
Переименуйте следующие два столбца:
- Attribute на MonthNumber (между двумя словами нет пробела – он будет удален позже)
- Value на Target
Теперь применим преобразования, чтобы создать столбец даты. Дата будет получена из столбцов Year и MonthNumber. Вы создадите столбец с помощью функции Columns From Examples.
- Чтобы подготовить значения столбца MonthNumber, щелкните правой кнопкой мыши заголовок столбца MonthNumber и выберите Replace Values.
- В окне Replace Values в поле Value To Find введите M.
- Щелкните ОК.
- Измените тип данных столбца MonthNumber на Whole Number.
- На вкладке ленты Add Column в группе General щелкните значок Column From Examples.
- Обратите внимание, что первая строка соответствует 2017 году и месяцу 7.
- В столбце Column1 в первой ячейке сетки введите 7/1/2017, а затем нажмите Enter.
На виртуальной машине используются региональные настройки США, поэтому на самом деле это 1 июля 2017 года
- Обратите внимание, что ячейки сетки обновляются прогнозируемыми значениями.
Функция точно предсказала, что вы комбинируете значения из столбцов Year и MonthNumber.
- Обратите также внимание на формулу, представленную над сеткой запроса.
- Чтобы переименовать новый столбец, дважды щелкните заголовок Merged.
- Переименуйте столбец в TargetMonth.
- Чтобы добавить новый столбец, нажмите OK.
-
Удалите следующие столбцы:
- Year
- MonthNumber
- Измените следующие типы данных столбца:
- Target как фиксированное десятичное число
- TargetMonth как дата
- Чтобы умножить Target на 1000, выберите заголовок столбца Target, а затем на вкладке ленты Transform в группе Number Column щелкните Standard, а затем выберите Multiply.
Вы можете вспомнить, что целевые значения были сохранены в виде тысяч.
- В окне Multiply в поле Value введите 1000.
- Щелкните ОК.
- В строке состояния убедитесь, что запрос содержит три столбца и 809 строк.
Задача 9: Настроить запрос ColorFormats
В этой задаче вы настроите запрос ColorFormats.
- Выберите запрос ColorFormats.
- Обратите внимание, что первая строка содержит имена столбцов.
- На вкладке ленты Home в группе Transform щелкните Use First Row as Headers.
- В строке состояния убедитесь, что запрос содержит три столбца и 10 строк.
Задача 10: Обновить запрос продукта
В этой задаче вы обновите запрос Product , объединив запрос ColorFormats .
- Выберите запрос Product.
- Чтобы объединить запрос ColorFormats, на вкладке ленты Home внутри группы Combine щелкните Merge Queries.
Объединение запросов позволяет интегрировать данные, в данном случае из разных источников данных (SQL Server и файл CSV).
- В окне Merge в таблице запроса Product выберите заголовок столбца Color.
- Под сеткой запроса Product в раскрывающемся списке выберите запрос ColorFormats.
- В таблице запроса ColorFormats выберите заголовок столбца Color.
- Когда откроется окно Privacy Levels, для каждого из двух источников данных в соответствующем раскрывающемся списке выберите Organizational.
Уровни конфиденциальности можно настроить для источника данных, чтобы определить, могут ли данные использоваться источниками совместно. Настройка каждого источника данных как Organizational позволяет им при необходимости обмениваться данными. Обратите внимание, что частные источники данных никогда не могут использоваться совместно с другими источниками данных. Это не означает, что личные данные не могут быть переданы в общий доступ; это означает, что механизм Power Query не может обмениваться данными между источниками.
- Щелкните Save.
- В окне Merge используйте тип соединения по умолчанию, сохраняя выбор Join Kind, и нажмите OK.
-
Разверните столбец ColorFormats, чтобы включить в него следующие два столбца:
- Background Color Format
- Font Color Format
- В строке состояния убедитесь, что запрос теперь содержит восемь столбцов и 397 строк.
Задача 11: Обновить запрос ColorFormats
В этой задаче вы обновите ColorFormats, чтобы отключить его загрузку.
- Выберите запрос ColorFormats.
- На панели Query Settings щелкните ссылку All Properties.
- В окне Query Properties снимите флажок Enable Load To Report.
Отключение загрузки означает, что она не будет загружаться как таблица в модель данных. Это происходит потому, что запрос был объединен с запросом Product, который может загружаться в модель данных.
- Щелкните OK.
Задача 12: Завершить
В этом задании вы выполните лабораторную работу.
-
Убедитесь, что у вас есть восемь запросов с правильными названиями:
- Salesperson
- SalespersonRegion
- Product
- Reseller
- Region
- Sales
- Targets
- ColorFormats (which will not load to the data model)
- Чтобы загрузить модель данных, в представлении File backstage выберите Close & Apply.
Все запросы с включенной загрузкой теперь загружены в модель данных.
- На панели Fields (расположенной справа) обратите внимание на семь таблиц, загруженных в модель данных.
- Сохраните файл Power BI Desktop.
- Если вы собираетесь начать следующую лабораторную работу, оставьте Power BI Desktop открытым.
Вы будете настраивать таблицы модели данных и отношения в лабораторной работе «Данные модели в Power BI Desktop, часть 1».