Power BI PL300 lab 2: Load Data in Power BI

Cập nhật theo giáo trình mới nhất của Microsoft, ngày 13/09/2023.

Bài hướng dẫn Lab 2: Load Transformed Data in Power BI Desktop (Làm sạch và chuyển đổi dữ liệu trong Power BI Desktop) là một bài thực hành nằm trong chuỗi bài luyện tập Power BI của Microsoft – tương đương với Lab 2 trong giáo trình của Datapot. Mục tiêu của chuỗi bài này là giúp học viên hiểu về các bước xử lý dữ liệu và từ đó thiết kế báo cáo trong Power BI. 

Xuyên suốt chuỗi bài này, chúng ta sẽ sử dụng bộ dữ liệu của công ty Adventure Works – một công ty sản xuất và kinh doanh đồ thể thao mạo hiểm đa quốc gia – để xây dựng một giải pháp Power BI cho công ty này.  

Datapot khuyến khích học viên thực hành các bài lab theo thứ tự sau để củng cố các kĩ năng cơ bản một cách tốt nhất: 

• Lab 1: Prepare Data in Power BI Desktop (Chuẩn bị dữ liệu trong Power BI Desktop) 
• Lab 2: Load Transformed Data in Power BI Desktop (Làm sạch và chuyển đổi dữ liệu trong Power BI Desktop) 
• Lab 3: Design a model in Power BI – Part 1 (Xây dựng Model trong Power BI – Phần 1) 
• Lab 3 nâng cao:  Design a model in Power BI – Part 2 (Xây dựng Model trong Power BI – Phần 2) 
• Lab 4: Create DAX Calculations in Power BI Desktop (Tạo các phép tính DAX trong Power BI Desktop) 
• Lab 5: Create Advanced DAX Calculations in Power BI Desktop (Tạo các phép tính DAX nâng cao trong Power BI Desktop) 
• Lab 6: Design a report in Power BI Desktop – Part 1 (Thiết kế báo cáo trong Power BI Desktop – Phần 1) 
• Lab 7: Design a report in Power BI Desktop – Part 2 (Thiết kế báo cáo trong Power BI Desktop – Phần 2)
• Lab 8: Perform Advanced Analytics with AI Visuals (Phân tích nâng cao với biểu đồ AI)
• Lab 9: Create a Power BI Dashboard (Tạo dashboard trên Power BI) 
• Lab 10: Enforce Row-Level Security (Cài đặt Row-Level Security) 

Chuẩn bị trước khi thực hành 

Để bắt đầu thực hành chuỗi bài Lab này, chúng ta cần chuẩn bị:  

• Cài đặt Power BI Desktop – Hướng dẫn cài đặt Power BI Desktop – YouTube 
• Database AdventureWorksDW2020 (Kết nối với SQL Server) 
• 2 file ColorFormats.csv và ResellerSalesTargets.csv 

Đối với học viên của Datapot, các bạn đã được cung cấp thông tin để kết nối đến SQL Server có chứa dataset AdventureWorksDW2020 và link download file. Các bạn đã đủ công cụ để bắt đầu thực hành 11 bài Lab.

Trong trường hợp tự thực hành, các bạn cần: 

• Cài đặt SQL Server và SQL Server Management Studio 
• Tải file dữ liệu (đuôi .bak) và 2 file ColorFormats.csv và ResellerSalesTargets.csv tại https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/AllfilesDownload.zip 
• Restore file .bak – Xem hướng dẫn tại đây.

Sau khi hoàn thành, các bạn sẽ sử dụng server name để kết nối với Power BI Desktop: 

Mục tiêu của bài lab 2

Trong bài thực hành này, chúng ta sẽ thực hiện các thao tác biến đổi cho từng truy vấn mà chúng ta đã tạo trong buổi trước, sau đó thực hiện apply để tải dữ liệu vào mô hình dữ liệu. Thông qua đó, bạn sẽ có thể:

• Thực hiện đa dạng các thao tác biến đổi và làm sạch dữ liệu
• Tải dữ liệu vào trong mô hình dữ liệu

Hướng dẫn thực hành Load Data

Hướng dẫn bằng Video:

Hướng dẫn từng bước:

Task 1: Chuẩn bị

Mở file mà bạn đã sử dụng trong lab của buổi trước, chọn Transform data để mở giao diện Power Query Editor. 

Task 2: Configure the Salesperson query

2.1 Đổi tên truy vấn

• Trong cửa sổ Power Query Editor, trong ngăn Queries, chọn truy vấn DimEmployee. Đổi tên truy vấn này bằng cách sử dụng ngăn Query Settings ở bên phải màn hình, trong hộp Name, thay đổi đoạn văn bản thành Salesperson, sau đó nhấn Enter. 

Kết quả thu được sau khi đổi tên truy vấn như sau:

Lưu ý: Tên truy vấn sẽ quyết định tên bảng trong mô hình, do vậy, bạn nên đặt tên cho các truy vấn ngắn gọn và dễ hiểu để tiện dùng về sau.

2.2 Lọc truy vấn dựa vào các cột được chọn 

Bây giờ chúng ta sẽ lọc để truy vấn của mình chỉ chứa các thông tin về các nhân viên (employee) mà công việc của họ là người bán hàng (salesperson).

• Để đi đến một cột nhất định, trên thẻ Home trên thanh ribbon, trong nhóm Manage Columns, chọn mũi tên thả xuống ở ô Choose Columns và chọn Go to Column. 

Mẹo: thao tác này sẽ rất có lợi khi bạn có rất nhiều cột trong bảng. Ngược lại, nếu bảng của bạn chỉ có một vài cột hoặc không có quá nhiều cột, bạn có thể chỉ cần lăn chuột lên xuống để tìm cột mình cần.

• Sắp xếp các cột theo tên để tìm dễ dàng hơn: trong cửa sổ Go to Column, để sắp xếp các cột theo tên, click là nút AZ sort, sau đó chọn Name.

• Chọn cột SalesPersonFlag sau đó chọn OK.
• Để lọc cột SalesPersonFlag để cột chỉ chứa các Salesperson: trên tiêu đề cột SalesPersonFlag, chọn mũi tên bên phải để mở giao diện lọc dữ liệu, sau đó bỏ chọn ở lựa chọn FALSE, và nhấn OK. 

• Sau khi thực hiện bước này, trong ngăn Query Settings sẽ bổ sung thêm một bước (step), tương ứng với bước vừa thực hiện trong bảng dữ liệu.

Lưu ý: mỗi bước biến đổi mà bạn thực hiện sẽ được liệt kê trong hộp Applied steps như trên. Bạn cùng có thể lựa chọn từng bước mà mình đã thực hiện để xem lại kết quả ở bước đó và có thể xóa, sửa bước đó.

2.3 Loại bỏ đi các cột dữ liệu không cần thiết

• Để xóa cột, trên thanh ribbon, thẻ Home, trong nhóm Manage Columns chọn biểu tượng Choose Columns.

• Trong cửa sổ ChooseColumns, để bỏ chọn tất cả các cột, chúng ta bỏ tick ở hộp Select All Columns

• Để thêm cột vào, tick chọn 6 cột dữ liệu sau và nhấn OK
  • EmployeeKey
  • EmployeeNationalIDAlternateKey
  • FirstName
  • LastName
  • Title
  • EmailAddress
• Chú ý: Trong danh sách Applied Steps, hãy chú ý rằng thao tác vừa rồi đã được ghi lại thành một bước mới trong danh sách.

2.3 Gộp các cột dữ liệu (Merge columns) 

Chúng ta sẽ gộp hai cột FirstName và LastName để tạo thành một cột tên duy nhất.

• Chọn cột FirstName và LastName bằng cách giữ phím CTRL

• Click chuột phải vào tiêu đề các cột được chọn, trong menu xuất hiện, chọn Merge Columns.

Lưu ý: Ngoài các thao tác biến đổi cơ bản mà bạn thấy ở menu này, bạn có thể tìm thấy thêm nhiều cách biến đổi khác trên thẻ Transform trong thanh ribbon.

• Trong cửa sổ Merge Columns:
  • Trong phần Separator, chọn Space.
  • Trong phần New Column Name, đổi tên thành Salesperson.

2.4 Đổi tên các cột dữ liệu 

Bạn có thể đổi tên các cột bằng cách click đúp chuột vào tiêu đề cột và thay tên cũ bằng cách nhập vào tên mới. Đổi tên các cột dữ liệu sau: 

2.5 Kiểm tra lại 

Ở góc dưới bên trái, trong thanh trạng thái, hãy chắc chắn rằng truy vấn của bạn có 5 cột và 18 dòng.

Chú ý: Bạn phải chắc chắn rằng kết quả của bạn không sai, nếu không bạn sẽ không thể thực hiện các thao tác ở các labs phía sau. Nếu kết quả sai, hãy dừng lại và xem xét lại từng bước mà bạn đã thực hiện ở trên để sửa lỗi.

Task 3: Configure the SalespersonRegion query

Trong task này chúng ta sẽ làm việc với truy vấn: DimEmployeeSalesTerritory. Trong ngăn Queries, chọn truy vấn DimEmployeeSalesTerritory.  Thực hiện các thao tác biến đổi dữ liệu như sau: 

• Đổi tên truy vấn thành SalespersonRegion.
• Loại bỏ đi hai cột dữ liệu cuối cùng: DimEmployee và DimSalesTerritory bằng cách lựa chọn cả hai cột, sau đó nhấn chuột phải tại một trong hai tiêu đề cột được chọn đó, trong menu thả xuống, chọn Remove Columns. 

• Kiểm tra lại kết quả

Task 4: Configure the Product query

Làm việc với truy vấn: DimProduct. Thực hiện các thao tác biến đổi sau:

• Đổi tên truy vấn thành Product.
• Đi đến cột FinishedGoodsFlag và lọc các giá trị TRUE lọc dữ liệu của cột chỉ chứa thông tin các sản phẩm đã hoàn tất sản xuất.
• Xóa tất cả các cột dữ liệu, ngoại trừ:
  • ProductKey
  • EnglishProductName
  • StandardCost
  • Color
  • DimProductSubcategory

Chú ý rằng cột DimProductSubcategory đang thể hiện giá trị của một bảng có quan hệ với bảng này. Thực tế, cột DimProductSubcategory chứa giá trị của một bảng khác trong dữ liệu gốc có quan hệ với bảng này, giá trị thể hiện trong bảng là đường dẫn về giá trị trong bảng quan hệ đó.

•  Ở bên tay phải tên cột, nhấn vào biểu tượng mở rộng cột.

• Bỏ chọn tất cả các cột và chỉ chọn hai cột: EnglishProductSubcategoryName và DimProductCategory. 

• Bỏ tùy chọn Use Original column name as prefix sau đó nhấn OK.

Sau đó, thực hiện các thao tác biến đổi như sau:

• Mở rộng cột DimProductCategory và chỉ chọn duy nhất cột EnglishProductCategoryName.
• Đổi tên các cột như sau:

• Kiểm tra kết quả

Task 5: Configure the Reseller query

Làm việc với truy vấn: DimReseller. Thực hiện các thao tác biến đổi sau:

• Đổi tên truy vấn thành Reseller
• Xóa tất cả các cột dữ liệu ngoại trừ:
  • ResellerKey
  • BusinessType
  • ResellerName
  • DimGeography
• Mở rộng cột DimGeography và chỉ thêm vào các cột sau đây:
  • City
  • StateProvinceName
  • EnglishCountryRegionName

Thay thế các giá trị sai trong cột BusinessType:

• Trong tiêu đề cột BusinessType chọn mũi tên thả xuống để có thể xem các giá trị khác nhau có trong cột này. Sau khi xem xét, có thể nhận thấy có giá trị “Ware House” bị lỗi chính tả. 

• Click chuột phải vào tiêu đề cột BusinessType và chọn Replace Values.

• Trong hộp Value to Find, nhập vào Ware House.
• Trong hộp Replace With, nhập vào Warehouse.

• Click OK.

Sau đó, đổi lại tên cột như sau:

• Kiểm tra kết quả

Task 6: Configure the Region query

Làm việc với truy vấn: DimSalesTerritory. Thực hiện các thao tác biến đổi sau:

• Đổi tên truy vấn thành Region
• Áp dụng bộ lọc cho cột SalesTerritoryAlternateKey để xóa các giá trị 0 (zero).

• Xóa tất cả các cột khác ngoại trừ:
  • SalesTerritoryKey
  • SalesTerritoryRegion
  • SalesTerritoryCountry
  • SalesTerritoryGroup
• Đổi tên các cột như sau:

• Kiểm tra kết quả:

Task 7: Configure the Sales query

Làm việc với truy vấn: FactResellerSales. 

7.1. Thực hiện các thao tác biến đổi sau:

• Đổi tên truy vấn thành Sales.
• Xóa tất cả các cột dữ liệu ngoại trừ:
  • SalesOrderNumber
  • OrderDate
  • ProductKey
  • ResellerKey
  • EmployeeKey
  • SalesTerritoryKey
  • OrderQuantity
  • UnitPrice
  • TotalProductCost
  • SalesAmount
  • DimProduct

Chú ý: Có thể bạn sẽ nhận ra trong phần lab của buổi Prepare Data in Power BI Desktop, có một số bản ghi trong bảng FactResellerSales bị thiếu giá trị trong cột TotalProductCost. Cột DimProduct đã được thêm vào để lấy ra các giá trị trong cột product standard cost để sửa lỗi thiếu dữ liệu đó.

• Mở rộng cột DimProduct và chọn cột StandardCost.

7.2. Tạo một cột tùy chọn (custom column)

• Trên thẻ Add Column trong nhóm General chọn Custom Column.

• Trong cửa sổ Custom Column, trong hộp New Column Name, nhập vào tên cột mới là Cost.

• Trong hộp Custom Column Formula, nhập vào câu lệnh sau đây (nhập vào sau biểu tượng dấu “=”) sau đó chọn OK:

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

Sau đó thực hiện các thao tác biến đổi như sau:

• Loại bỏ hai cột sau:
  • TotalProductCost
  • StandardCost
• Đổi tên các cột như sau:

7.3. Thay đổi kiểu dữ liệu

• Trong tiêu đề cột Quantity, ở phía bên trái của tên cột, chọn biểu tượng 1.2 và chọn Whole Number.

Lưu ý: Việc thay đổi kiểu dữ liệu là rất quan trọng. Đặc biệt, khi dữ liệu của bạn ở dạng số, việc chọn đúng kiểu dữ liệu sẽ giúp bạn thực hiện các phép tính toán dễ dàng và chính xác.

• Thay đổi kiểu dữ liệu của các cột dưới đây thành Fixed Decimal Number:
  • Unit Price
  • Sales
  • Cost

Kiểu dữ liệu Fixed Decimal Number sẽ lưu trữ giá trị ở dạng thức chính xác nhất và do đó nó tiêu tốn nhiều bộ nhớ hơn so với kiểu Decimal Number. Kiểu Fixed Decimal Number cần được sử dụng cho các giá trị dữ liệu về tài chính, chẳng hạn như tý giá hối đoái.

• Kiểm tra kết quả: 10 cột và 999+ dòng.

Task 8: Configure the Targets query

Làm việc với truy vấn: ResellerSalesTargets. Thực hiện các thao tác biến đổi như sau:

• Đổi tên truy vấn thành Targets.

8.1. Unpivot cột dữ liệu:

• Chọn cùng lúc hai cột Year và EmployeeID.

• Nhấn chuột phải vào một trong hai tiêu đề cột đã chọn, trong menu thả xuống, chọn Unpivot Other Columns => Hãy chú ý rằng bây giờ tên các cột xuất hiện ở trong cột Attribute còn các giá trị xuất hiện ở cột Value. 

• Sử dụng bộ lọc cho cột Value để bỏ các giá trị có dấu gạch ngang (-).
• Đổi tên cột như sau:

8.2. Tạo cột ngày tháng (date) bằng cách sử dụng tính năng Columns from Examples:

• Click chuột phải vào tên cột MonthNumber và chọn Replace Vaues. Trong cửa sổ Replace Values trong hộp Vaue To Find, nhập vào M, chọn OK.

• Đổi kiểu dữ liệu của cột MonthNumber thành Whole Number.

• Trên thẻ Add Column, trong nhóm General, chọn biểu tượng Column from Examples.

• Chú ý rằng dòng đầu tiên là của năm 2017, tháng 7. Trong cột Column1, dòng đầu tiên, nhập vào giá trị 7/1/2017, sau đó nhấn Enter. Tiếp tục nhập vào dòng thứ hai theo logic tương tự. Bằng cách đó, chúng ta khiến cho Power Query Editor “học” được từ ví dụ được nhập vào và tạo ra một cột mới theo logic đó.
• Chú ý rằng các ô mới sẽ được cập nhật theo giá trị được Power BI dự đoán và đồng thời bạn sẽ thấy có công thức cho cột vừa tạo đó xuất hiện ở phía trên truy vấn.

• Để đổi tên cột mới, click đúp chuột vào cột Merge, đổi tên thành TargetMonth.

Tiếp tục thực hiện các thao tác sau:

• Loại bỏ các cột sau:
  • Year
  • MonthNumber
• Thay đổi kiểu dữ liệu cho các cột như sau:
  • Cột Target: Fixed Decimal Number.
  • Cột: TargetMonth: Date.
• Nhân giá trị cột Target với 1000: Chọn cột Target, trên thẻ Transform, trong nhóm Number Column, chọn Standard và chọn Multiply.

• Trong cửa sổ Multiply, trong hộp Value, nhập vào 1000.

• Kiểm tra lại kết quả: Truy vấn của bạn bây giờ sẽ có 3 cột và 809 dòng.

Task 9: Configure the ColorFormats query

Làm việc với truy vấn: ColorFormat. Thực hiện các thao tác sau:

• Chú ý rằng dòng đầu tiên trong bảng chính là tên cột. Trên thẻ Home, trong nhóm Transform, chọn Use First Row as Header. 

• Kiểm tra lại: truy vấn ColorFormats bây giờ có 3 cột và 10 dòng.

Task 10: Cập nhật truy vấn Product query

Cập nhật truy vấn Product bằng cách merge (gộp) với truy vấn ColorFormats.

• Chọn truy vấn Product. Trên thẻ Home, trong nhóm Combine click vào Merge Queries.

• Trong cửa sổ Merge , trong giao diện lưới của truy vấn Product chọn tên cột Color

• Ở dưới truy vấn Product, trong danh sách thả xuống, chọn truy vấn ColorFormats. Trong giao diện lưới của truy vấn ColorFormats chọn tên cột là Color. 

• Khi cửa sổ PrivacyLevels xuất hiện, với mỗi nguồn dữ liệu, chọn Organizational ở thẻ thả xuống tương ứng và sau đó chọn Save. 

• Trong cửa sổ Merge chọn OK.
• Mở rộng cột ColorFormats để thêm vào các cột sau đây:
  • Background Color Format
  • Font Color Format

• Kiểm tra lại kết quả của bạn: Đảm bảo rằng truy vấn Product có 8 cột và 397 dòng. 

Task 11: Cập nhật truy vấn ColorFormats

Ngăn không cho tải truy vấn ColorFormats:

• Chọn truy vấn ColorFormats. Trong ngăn Query Settings chọn All Properties.

• Trong cửa sổ Query Properties, bỏ chọn Enable Load To Report rồi chọn OK.

Task 12: Kết thúc

• Hãy chắc chắn rằng bạn có tổng cộng 8 truy vấn được đặt tên đúng như sau:
  • Salesperson
  • SalespersonRegion
  • Product
  • Reseller
  • Region
  • Sales
  • Targets
  • ColorFormats (Không được load vào trong data model)
• Để tải data model (mô hình dữ liệu), trong giao diện của thẻ File, chọn Close & Apply. Mọi truy vấn được cho phép tải (load-enabled) sẽ được đưa vào mô hình 

• Trên ngăn Fields ở phía bên phải màn hình, bạn sẽ thấy có 7 bảng được tải vào mô hình như sau: 

• Lưu file Power BI Desktop

Xem ngay Lab 3: Design a model in Power BI – Part 1 (Xây dựng Model trong Power BI – Phần 1)

Chuỗi bài hướng dẫn thực hành Power BI PL300 Lab: https://datapot.vn/category/power-bi/power-bi-pl300-lab-video/