load data in power bi

Power BI PL300 lab 2: Load Data in Power BI

Ngày đăng: 10/03/2022

Đây là bài thực hành nằm trong chuỗi bài luyện tập thứ hai Power BI của Microsoft. Bạn có thể tham khảo thêm, bài thực hành thứ nhất: Power BI PL300 lab 1: Prepare Data in Power BI Desktop. 

Xuyên suốt chuỗi bài này, chúng ta sẽ đi từ khâu đầu tiên trong quá trình phân tích dữ liệu bằng Power BI – chuẩn bị dữ liệu, cho đến những bước cuối cùng – xuất bản báo cáo dưới dạng report hoặc dashboard. Mục tiêu cuối cùng của series này là xây dựng một giải pháp Power BI cho công ty Adventure Works – công ty sản xuất và kinh doanh đồ thể thao mạo hiểm đa quốc gia.

Xem học liệu gốc của Microsoft tại đây: https://microsoftlearning.github.io/DA-100-Analyzing-Data-with-Power-BI/ 

Bạn có thể thực hiện từng bài thực hành theo thứ tự bất kỳ mà bạn mong muốn, tuy nhiên, để thuận lợi nhất cho quá trình luyện tập, chúng mình đề xuất thứ tự luyện tập như sau: 

  1. Prepare Data in Power BI Desktop
  2. Load Data in Power BI Desktop 
  3. Model Data in Power BI Desktop, Part 1
  4. Model Data in Power BI Desktop, Part 2
  5. Create DAX Calculations in Power BI Desktop, Part 1
  6. Create DAX Calculations in Power BI Desktop, Part 2
  7. Design a Report in Power BI Desktop, Part 1
  8. Design a Report in Power BI Desktop, Part 2
  9. Create a Power BI Dashboard
  10. Create a Power BI Paginated Report
  11. Perform Data Analysis in Power BI Desktop
  12. Enforce Row-Level Security

Mục tiêu

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

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.

Picture 20

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.

Picture 1

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

Picture 87

Note: 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.

Picture 88

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

Picture 94

  • 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. 

Picture 95

  • 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.

Picture 98

Note: 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.

Picture 99

  • 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

Picture 102

  • Để 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.

Picture 112

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  LastName bằng cách giữ phím CTRL

Picture 116

  • 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.

Picture 117

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.

Picture 119

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:

Tên cột ban đầuTên mới thay thế
EmployeeNationalIDAlternateKeyEmployeeID
EmailAddressUPN

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. 

Picture 5638

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.

Picture 5640

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

Picture 5641

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.

Picture 5644

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

Picture 5646

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

Picture 5647

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:
Tên cột ban đầuTên mới thay thế
EnglishProductNameProduct
StandardCostStandard Cost
EnglishProductSubcategoryNameSubcategory
EnglishProductCategoryNameCategory
  • Kiểm tra kết quả

Picture 5651

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ả.

Picture 2

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

Picture 4

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

Picture 5

  • Click OK.

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

Tên cột ban đầuTên mới thay thế
BusinessTypeBusiness Type
ResellerNameReseller
StateProvinceNameState-Province
EnglishCountryRegionNameCountry-Region
  • Kiểm tra kết quả

Picture 5657

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).

Picture 5660

  • 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:
Tên cột ban đầuTên mới thay thế
SalesTerritoryRegionRegion
SalesTerritoryCountryCountry
SalesTerritoryGroupGroup
  • Kiểm tra kết quả:

Picture 5661

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.

Picture 5664

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

Picture 5665

  • 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:
Tên cột ban đầuTên cột thay thế
OrderQuantityQuantity
UnitPriceUnit Price
SalesAmountSales

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.

Picture 5667

Note: 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

Picture 5668

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.

Picture 5673

  • 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.

Picture 5674

  • 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:
Tên cột ban đầuTên cột thay thế
AttributeMonthNumber
ValueTarget

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.

Picture 5676

Picture 5677

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

Picture 5678

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

Picture 5675

  • 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 PBI 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.

Picture 5679

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

Picture 5680

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.

Picture 5682

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

Picture 5683

  • 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

Picture 5688

  • 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.

Picture 5654

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

Picture 5655

  • Ở 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.

Picture 21

  • 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.

Picture 5691

  • 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

Picture 5694

  • 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.

Picture 322

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

Picture 323

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

Picture 326

  • 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: 

Picture 3

  • Lưu file Power BI Desktop

Video hướng dẫn thực hành làm sạch, biến đổi và load dữ liệu trong Power BI:

-7%
108 giờ ++
Beginner
25.000.000 
10 giờ học
Beginner, Fresher
1.500.000 
-8%
52 giờ
Beginner
9.000.000 9.500.000 
21 giờ học
Intermediate/Advanced
7.500.000 
Fresher, Junior
2.000.000 
-6%
Fresher, Junior
6.190.000 
Fresher, Junior
3.000.000 

Xem tiếp: Lab 3 – Model Data in Power BI Desktop

Xem thêm các bài viết về Power BI Lab:

Lab 1 – Prepare data in Power BI Desktop 

Lab 4 – Tạo quan hệ Many-to-many – Model Data in Power BI Desktop 

Lab 5 – Create DAX Calculations in Power BI Desktop, Part 1

Lab 6 – Create DAX Calculations in Power BI Desktop, Part 2

Lab 7 – Thiết kế Báo cáo trong Power BI – Phần 1

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *