Lab Power BI – Module 3 Clean, Transform, and Load Data in Power BI

Clean, Transform, and Load Data in Power BI

1. Tại Power BI Desktop, vào menu File, chọn Open Report,ấn Browse Report, chọn file lab2-task1.pbix (file này bạn đã thực hiện trong Lab2 Task1), ấn Open
2. Tại Power BI Desktop, vào menu Home, chọn Transform Data và chọn tiếp Transform Data để vào Power Query Editor

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimEmployee query
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimEmployee thành nhân viên bán hàng, ấn Enter.
3. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Go to Column. (di chuyển nhanh đến một column trong bảng có quá nhiều column)
4. Cửa sổ Go to Column chọn SalesPersonFlag, ấn Ok

5. Click vào mũi tên bên phải của SalesPersonFlag column bỏ checkbox FALSE, ấn Ok (chỉ hiển thị giá trị TRUE)

(quan sát cửa sổ Query Settings bên phải, trong phần Applied Steps xuất hiện thêm dòng Filtered Rows, mỗi lần bạn thực hiện thao tác thay đổi dữ liệu, Power Bi sẽ ghi lại bước thực hiện này, bạn có thể chỉnh sửa hay xóa bước này nếu cần)

6. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Choose Columns.

7. Cửa sổ Choose Columns, bỏ chọn checkbox Select All Columns, và chọn lại các columns sau và ấn OK:

• EmployeeKey
• EmployeeNationalIDAlternateKey
• FirstName
• LastName
• Title
• EmailAddress

(chỉ hiển thị các columns cần thiết, quan sát cửa sổ Query Settings bên phải, trong phần Applied Steps xuất hiện thêm dòng Removed Other Columns)

8. Chọn Column FirstName giữ phím Ctrl chọn column LastName, phải chuột vào 2 column vừa chọn, chọn Merge Columns.

9. Cửa sổ Merge Columns, Trong phần Separator chọn Space, trong phần New Column Name nhập vào Tên Nhân Viên Bán Hàng, ấn OK.

(Merge 2 column FirstName và FirstName thành column Tên Nhân Viên Bán Hàng)

10. Double-click vào Column EmployeeNationalIDAlternateKey đổi thành TargetID, tương tự đổi EmailAddress thành UPN, đổi EmployeeKey thành Mã Nhân Viên, và đổi Title thành Chức Danh

11. Quan sát gốc dưới bên trái cửa sổ Power Query Editor, nhân viên bán hàng hiện có 5 columns và 18 rows.

12. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimEmployeeSalesTerritory
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimEmployeeSalesTerritory thành Khu vực bán hàng, ấn Enter.
3. Chọn Column DimEmployee giữ phím Ctrl chọn column DimSalesTerritory, phải chuột vào 2 column vừa chọn, chọn Remove Columns, ấn Ok
4. Double-click vào Column EmployeeKey đổi tên thành Mã nhân viên, tương tự đổi SalesTerritoryKey thành Mã Khu vực
5. Quan sát gốc dưới bên trái cửa sổ Power Query Editor, SalespersonRegion hiện có 2 columns và 39 rows
6. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimProduct
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimProduct thành Sản Phẩm, ấn Enter.
3. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Go to Column. (di chuyển nhanh đến một column trong bảng có quá nhiều column)
4. Cửa sổ Go to Column chọn FinishedGoodsFlag, ấn Ok
5. Click vào mũi tên bên phải của FinishedGoodsFlag column bỏ checkbox FALSE, ấn Ok (chỉ hiển thị giá trị TRUE = thành phẩm)
6. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Choose Columns.
7. Cửa sổ Choose Columns, bỏ chọn checkbox Select All Columns, và chọn lại các columns sau và ấn OK:

• ProductKey
• EnglishProductName
• StandardCost
• Color
• DimProductSubcategory

(Lưu ý 1. rằng cột DimProductSubcategory đại diện cho một bảng có liên quan (nó chứa các liên kết Value)).

(Lưu ý 2. Quan sát gốc dưới bên trái cửa sổ Power Query Editor, Sản Phẩm hiện có 5 columns và 397 rows)

8. Click vào Icon bên phải của Column DimProductSubcategory, bỏ chọn Select All Columns, và check vào EnglishProductSubcategoryName và DimProductCategory columns, bỏ chọn check box Use Original Column Name as Prefix, ấn OK

(Bằng cách thực hiện này sẽ lấy giá trị EnglishProductSubcategoryName bằng cách truy vấn vào 2 table DimProductCategory và DimProductSubcategory)

(Lưu ý 3. Quan sát gốc dưới bên trái cửa sổ Power Query Editor, Product hiện có 6 columns và 397 rows, vì khi thực hiện bước 8, column DimProductSubcategory được đổi thành EnglishProductSubcategoryName và thêm column DimProductCategory)

 

9. Click vào Icon bên phải của Column DimProductCategory, bỏ chọn Select All Columns, và check vào EnglishProductCategoryName, bỏ chọn check box Use Original Column Name as Prefix, ấn OK.

10. Đổi tên các Columns sau:

• EnglishProductName thành Tên Sản Phẩm
• StandardCost thành Giá Mua(có khoảng trắng)
• EnglishProductSubcategoryName thành Danh Mục cấp 2
• EnglishProductCategoryName thành Danh Mục cấp 1
• ProductKey thành Mã Sản Phẩm

11. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimReseller
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimReseller thành Đại Lý bán Hàng, ấn Enter.
3. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Choose Columns.
4. Cửa sổ Choose Columns, bỏ chọn checkbox Select All Columns, và chọn lại các columns sau và ấn OK:

• ResellerKey
• BusinessType
• ResellerName
• DimGeography

5. Click vào Icon bên phải của Column DimGeography, bỏ chọn Select All Columns, và check vào City, StateProvinceName và EnglishCountryRegionName columns, bỏ chọn check box Use Original Column Name as Prefix, ấn OK

6. Click vào Icon bên phải của Column Business Type, quan sát thấy có 2 giá trị là Warehouse và Ware House do lỗi nhập liệu (bạn đã nhận biết trong lab 1), ấn Cancel

7. Phải chuột vào Business Type column chọn Replace Values

8. Cửa sổ Replace Values nhập các thông tin sau ấn Ok:

• Value to Find: nhập vào Ware House
• Replace With: nhập vào Warehouse

9. Đổi tên các Columns sau:

• BusinessType thành Loại Đại Lý (có khoảng trắng)
• ResellerName thành Tên Đại Lý
• StateProvinceName thành Tỉnh Thành – Bang
• EnglishCountryRegionName thành Quốc Gia
• City thành Thành Phố
• ResellerKey thành Mã Đại Lý

10. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimSalesTerritory
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimSalesTerritory thành Khu Vực, ấn Enter.
3. Click vào mũi tên bên phải SalesTerritoryAlternateKey column, bỏ chọn checkbox có giá trị 0
4. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Choose Columns.
5. Cửa sổ Choose Columns, bỏ chọn checkbox Select All Columns, và chọn lại các columns sau và ấn OK:

• SalesTerritoryKey
• SalesTerritoryRegion
• SalesTerritoryCountry
• SalesTerritoryGroup

6. Đổi tên các Columns sau:

• SalesTerritoryKey thành Mã Khu Vực
• SalesTerritoryRegion thành Khu Vực bán hàng
• SalesTerritoryCountry thành Quốc Gia bán hàng
• SalesTerritoryGroup thành Nhóm Quốc Gia

7. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn FactResellerSales
2. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên FactResellerSales thành Hóa Đon Bán hàng, ấn Enter.
3. Tại Màn hình Power Query Editor, vào menu Home trong phần Manage Columns chọn Choose Columns và chọn Choose Columns.
4. Cửa sổ Choose Columns, bỏ chọn checkbox Select All Columns, và chọn lại các columns sau và ấn OK:

• SalesOrderNumber
• OrderDate
• ProductKey
• ResellerKey
• EmployeeKey
• SalesTerritoryKey
• OrderQuantity
• UnitPrice
• TotalProductCost
• SalesAmount
• DimProduct

(quan sát TotalProductCost column có các giá trị Null. DimProduct column được đưa vào để tính giá trị thay thế các giá trị null này.)

5. Click vào Icon bên phải của Column DimProduct, bỏ chọn Select All Columns, và check vào StandardCost bỏ chọn check box Use Original Column Name as Prefix, ấn OK

6. Tại màn hình Power Query Editor, vào menu Add Column, chọn Custom Column

7. Cửa sổ Custom Column, trong phần New Column Name nhập vào Tổng Giá Vốn, trong phần Custom Column Formula nhập vào công thức sau và ấn Ok

if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]

8. Chọn Column TotalProductCost giữ phím Ctrl chọn column StandardCost, phải chuột vào 2 column vừa chọn, chọn Remove Columns.

9. Đổi tên các Columns sau:

• OrderQuantity thành Số lượng
• UnitPrice thành Giá Bán(có khoảng trắng)
• SalesAmount thành Thành Tiền
• SalesOrderNumber thành Mã Hóa Đơn
• OrderDate thành Ngày hóa Đơn
• ProductKey thành Mã Sản Phẩm
• ResellerKey thành Mã Đại Lý
• EmployeeKey thành Mã Nhân Viên
• SalesTerritoryKey thành Mã Khu Vực

10. Chọn vào Icon bên trái của cột Số lượng chọn Whole Number.

11. Tương tự đổi kiểu dữ liệu của 3 cột Giá Bán, Thành Tiền, Tổng Giá Vốn thành Fixed Decimal Number

12. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Vào trình duyệt, truy cập trang https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/blob/Main/Allfiles/Resources/ResellerSalesTargets.csv quan sát table ResellerSalesTargets.csv, ấn vào nút Raw
2. Copy url trên thanh address của trình duyệt.

3. Trở lại Power BI, tại màn hình Power Query Editor, chọn menu Home, chọn New Source, chọn Web.

4. Cửa sổ From Web, trong phần URL paste url đã copy ở bước 2 vào, ấn OK, tiếp tục ấn Ok

5. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn ResellerSalesTargets

6. Cửa sổ Query Settings (bên phải), trong phần Name đổi tên ResellerSalesTargets thành Targets, ấn Enter.

7. Chọn Column Year giữ phím Ctrl chọn column EmployeeID, phải chuột vào 2 column vừa chọn, chọn Unpivot Other Columns, xuất hiện 2 column Attribute và value

8. Chọn Icon filter bên phải column Value, bỏ chọn checkbox (-), ấn OK  ( loại bỏ các row có giá trị là -)

9. Đổi tên các Columns sau:

• Attribute thành Doanh Số Tháng
• Value thành Doanh Số Mục Tiêu
• EmployeeID thánh TargetID

10. Phải chuột vào column Doanh Số Tháng chọn Replace Values.

11. Cửa sổ Replace Values nhập các thông tin sau ấn Ok:

• Value to Find: nhập vào M
• Replace With: (để trống)

12. Chọn vào Icon bên trái của cột Doanh Số Tháng chọn Whole Number.

13. Tại màn hình Power Query Editor, vào menu Add Column, chọn Column From Examples

14. Quan sát dòng đầu tiên cột Year là 2017, Cột Doanh Số Tháng là 07, nhập vào dòng đầu tiên của Column1 là 7/1/2017, ấn Enter, quan sát các cột bên dưới đã được dự đoán giá trị dựa vào 2 column year và MonthNumber, ấn Ok.

15. Rename cột Merged thành Tháng

16. Chọn Column Year giữ phím Ctrl chọn column Doanh số Tháng, phải chuột vào 2 column vừa chọn Remove Columns

17. Chọn vào Icon bên trái của cột Doanh Số Mục Tiêu chọn fixed decimal number.

18. Chọn vào Icon bên trái của cột Tháng chọn date.

19. Chọn cột Doanh Số Mục tiêu, Chọn menu Transform trong phần Number Column chọn Standard, và chọn Multiply.

20. Cửa sổ Multiply trong phần Value nhập vào 1000 ấn OK (nhân Doanh Số mục tiêu với 1000)

21. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Vào trình duyệt, truy cập trang https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/blob/Main/Allfiles/Resources/ColorFormats.csv quan sát table ColorFormats.csv, ấn vào nút Raw
2. Copy url trên thanh address của trình duyệt.
3. Trở lại Power BI, tại màn hình Power Query Editor, chọn menu Home, chọn New Source, chọn Web.
4. Cửa sổ From Web, trong phần URL paste url đã copy ở bước 2 vào, ấn OK, tiếp tục ấn Ok
5. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn ColorFormats, quan sát thấy Column name là Column1, Column2, Column3
6. Màn hình Power Query Editor, vào menu Home trong phần Transform chọn Use First Row as Header

7. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

Phần này bạn sẽ merge Sản phẩm query với ColorFormats query.

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn Sản Phẩm
2. Tại màn hình Power Query Editor, chọn menu Home, chọn Merge Queries.
3. Cửa sổ Merge, trong phần query Sản Phẩm chọn Column Color, Combobox phía dưới query Sản Phẩm chọn ColorFormats.
4. Trong phần ColorFormats query chọn Column Color
5. Nếu Xuất hiện cửa sổ Privacy Levels, trong 2 combobox của 2  data sources chọn Organizational, ấn Save
6. Trở về cửa sổ Merge, trong phần Join Kind chọn Left Outer(…) ấn OK.

7. Click vào mũi tên bên phải ColorFormats column, bỏ chọn checkbox Color, bỏ chọn check box Use Original Column Name as Prefix ấn OK

8. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply

1. Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn ColorFormats
2. Cửa sổ Query Settings bên phải, click vào link All Properties.
3. Cửa sổ Query Properties, bỏ chọn checkbox Enable Load To Report, ấn Ok (không load ColorFormats vào data model, vì đã merged với Product query)
4. Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Close & Apply
5. Vào menu File chọn Save và lưu file lab3.pbix

1. Load file lab2.task2.pbix thực hiện các yêu cầu sau:

• Tạo thêm cột SKUType trong Product query, trích xuất các giá trị nằm giữa 2 dấu (-) của Column ProductSKU
• Thay thế các giá trị 0 trong trường ProductStyle bằng “Null”
• Tạo thêm cột DiscountPrice có giá trị bằng ProductPrice * 0.85 (giảm 15%)
• Product query chỉ hiển thị 6 column sau:
  • ProductKey
  • ProductName
  • SKUType
  • DiscountPrice
  • ProductCost
  • ProductPrice
• Customer query: Tạo thêm Column BirthYear, trích xuất từ Column BirthDate
• Tạo Column Full Name, là Merge của 2 Column FirstName và LastName
• Customer query chỉ hiển thị 5 column sau:
  • CustomerKey
  • Full name
  • Gender
  • EmailAddress
  • BirthYear

2. Load file lab2.task3.pbix thực hiện các yêu cầu sau:

• Kiểm tra Column nào có giá trị null thay bằng giá trị 0
• Kiểm tra Column nào có giá trị (–) thay bằng giá trị 0
• Thay đổi kiểu dữ liệu phù hợp cho các Column