Power Query Editor trong Power BI Desktop cho phép bạn định hình (chuyển đổi) dữ liệu đã import của mình. Bạn có thể thực hiện các hành động như đổi tên column hay table, thay đổi văn bản thành số, xóa hàng, đặt hàng đầu tiên làm tiêu đề, v.v. Điều quan trọng là định hình dữ liệu của bạn để đảm bảo dữ liệu đó đáp ứng nhu cầu của bạn và phù hợp để sử dụng trong các báo cáo.
Để bắt đầu định hình dữ liệu của bạn, hãy mở Power Query Editor bằng cách vào menu Home chọn Transform data. Khi bạn làm việc trong Power Query Editor, tất cả các bước bạn thực hiện để định hình dữ liệu của mình đều được ghi lại. Sau đó, mỗi khi truy vấn kết nối với nguồn dữ liệu, nó sẽ tự động áp dụng các bước của bạn để dữ liệu của bạn luôn được định hình theo cách bạn đã chỉ định.
Bước đầu tiên trong việc định hình dữ liệu ban đầu của bạn là xác định các tiêu đề và tên column trong dữ liệu, sau đó đánh giá vị trí của chúng để đảm bảo rằng chúng ở đúng vị trí.
Với Power BI Desktop, bạn có thể kết nối với nhiều loại nguồn dữ liệu khác nhau, sau đó định hình dữ liệu để đáp ứng nhu cầu của mình, cho phép bạn tạo báo cáo trực quan để chia sẻ với người khác. Định hình dữ liệu có nghĩa là chuyển đổi dữ liệu: đổi tên column hay table, thay đổi văn bản thành số, xóa hàng, đặt hàng đầu tiên làm tiêu đề, v.v.
Lập hồ sơ dữ liệu liên quan đến việc nghiên cứu các sắc thái của dữ liệu: xác định điểm bất thường, kiểm tra và phát triển cấu trúc dữ liệu cơ bản, và truy vấn thống kê dữ liệu như số lượng hàng, phân phối giá trị, giá trị tối thiểu và tối đa, giá trị trung bình, v.v. Khái niệm này rất quan trọng vì nó cho phép bạn định hình và sắp xếp dữ liệu sao cho việc tương tác với dữ liệu và xác định việc phân phối dữ liệu không phức tạp, do đó giúp bạn thực hiện nhiệm vụ làm việc với dữ liệu ở giao diện người dùng để phát triển các thành phần báo cáo dễ dàng.
Module 3 ctl.edu.vn hướng dẫn bạn cách dùng Power Query Editor xác định các điểm bất thường, xem xét kích thước, định dạng của dữ liệu và thực hiện các bước chuyển đổi và làm sạch dữ liệu, để tải dữ liệu vào Power BI.
Task 1: Get started
Clean, Transform, and Load Data in Power BI
- 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
- 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
Task 2: Làm Việc với Query Nhân viên bán hàng
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimEmployee query
- 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.
- 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)
- 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
Task 3: Làm việc với Query Khu vực bán hàng
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimEmployeeSalesTerritory
- 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.
- 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
- 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
- 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
- Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Apply
Task 4: Làm Việc với query Sản Phẩm
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimProduct
- 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.
- 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)
- Cửa sổ Go to Column chọn FinishedGoodsFlag, ấn Ok
- 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)
- 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.
- 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
Task 5: Làm Việc với query Đại Lý Bán Hàng
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimReseller
- 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.
- 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.
- 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
Task 6: Làm Việc với query Khu Vực
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn DimSalesTerritory
- Cửa sổ Query Settings (bên phải), trong phần Name đổi tên DimSalesTerritory thành Khu Vực, ấn Enter.
- Click vào mũi tên bên phải SalesTerritoryAlternateKey column, bỏ chọn checkbox có giá trị 0
- 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.
- 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
Task 7: Làm Việc với query Hóa Đơn Bán hàng
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn FactResellerSales
- 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.
- 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.
- 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
Task 8: Làm việc với query Targets
- 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
- 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
Task 9: Làm việc với query ColorFormats
- 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
- Copy url trên thanh address của trình duyệt.
- 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.
- 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
- 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
- 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
Task 10: Cập nhật query Sản Phẩm
Phần này bạn sẽ merge Sản phẩm query với ColorFormats query.
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn Sản Phẩm
- Tại màn hình Power Query Editor, chọn menu Home, chọn Merge Queries.
- 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.
- Trong phần ColorFormats query chọn Column Color
- Nếu Xuất hiện cửa sổ Privacy Levels, trong 2 combobox của 2 data sources chọn Organizational, ấn Save
- 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
Task 11: Cập nhật query ColorFormats
- Màn hình Power Query Editor, cửa sổ Queries bên trái, chọn ColorFormats
- Cửa sổ Query Settings bên phải, click vào link All Properties.
- 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)
- Cửa sổ Power Query Editor, trên thanh công cụ chọn Close & Apply và chọn Close & Apply
- Vào menu File chọn Save và lưu file lab3.pbix
Task 12. Additional Exercise (Optional)
- 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
Hoàn Tất Bài Lab
Mr – CTL