Power Query là gì? Sử dụng Power Query để làm sạch dữ liệu trong Power BI như thế nào?

marketing foundation

Tomorrow MarketersLàm sạch dữ liệu là bước quan trọng trong quy trình các bước làm việc với dữ liệu, không có dữ liệu “sạch” bạn sẽ không thể thực hiện các bước tiếp theo như trực quan hóa dữ liệu, phân tích & xây dựng báo cáo. Trong bài viết này, Tomorrow Marketers sẽ cùng bạn đi tìm hiểu về công cụ Power Query, và cách sử dụng công cụ này để làm sạch dữ liệu trong Power BI nhé!

Nội dung bài viết:

  1. Power Query là gì?
  2. Giao diện của Power Query
  3. Sử dụng Power Query để làm sạch dữ liệu trong Power BI thế nào?
    3.1. Transpose table
    3.2. Promote Headers
    3.2. Pivot/Unpivot
    3.4. Split/Merge Columns
    3.5. Removing duplicates
    3.6. Replace value
    3.7. Change data types
    3.8. Filter Data
  4. Tips để làm sạch dữ liệu hiệu quả hơn với Power Query
    4.1. Hiểu dữ liệu hơn với tính năng Data Profiling
    4.2. Advanced Editor với M query

1. Power Query là gì? 

Power Query là một công cụ được tích hợp sẵn trong Microsoft Excel và Power BI cho phép người dùng thực hiện các thao tác làm sạch và chuẩn hóa dữ liệu trước khi tải vào Excel hoặc Power BI để trực quan hóa và phân tích.  

Một số tính năng nổi bật của Power Query: 

  • Kết nối với đa dạng các nguồn dữ liệu khác nhau: Bạn có thể sử dụng Power Query để kết nối kéo dữ liệu từ nhiều nguồn khác nhau bao gồm: Excel Workbook, Text/CSV, Folder, Google Sheets, SQL Server database,… về một nơi là Power Query để xử lý. 

Đọc thêm: Cách kết nối các nguồn dữ liệu khác nhau trong Power BI 

  • Chuyển đổi dữ liệu (Transforming data): Không cần có bất cứ kiến thức gì về coding, bạn vẫn có thể thực hiện các thao tác để xử lý, làm sạch dữ liệu phục vụ cho quá trình phân tích như:
  • Pivot / Unpivot
  • Tách/ Hợp nhất các cột (Split / Merge)
  • Tạo thêm cột mới
  • Xóa hàng, cột, giá trị trống, lỗi
  • Thay đổi kiểu dữ liệu

  • Kết hợp các bảng: Power Query cho phép kết hợp nhiều bảng dữ liệu có liên quan lại với nhau thông qua một cột chung, đây là giải pháp có thể thay thế cho hàm VLOOKUP của Excel giúp bạn có thể tra cứu giá trị từ các bảng khác nhau. 
  • Tự động hóa các bước: Với mỗi bước xử lý dữ liệu, Power Query sẽ tự động ghi nhớ các thao tác xử lý dữ liệu mà bạn đã thực hiện. Khi báo cáo được làm mới hay các dữ liệu mới được cập nhật thêm từ nguồn dữ liệu, Power Query sẽ tự động xử lý những dữ liệu này theo các bước mà bạn đã thực hiện và lưu lại trước đó mà không cần bạn phải thực hiện bất cứ thao tác nào thêm. 

2. Giao diện của Power Query

Bạn có thể truy cập vào Power Query ngay khi import dữ liệu vào Power BI, hoặc bằng cách nhấp chuột vào nút “Transform data” trong tab Home để truy cập vào Power Query. 

cách mở Power Query

Dưới đây là giao diện của Power Query: 

giao diện của Power Query

Giao diện của Power Query bao gồm 5 thành phần chính: 

(1) Ribbon: Chứa các công cụ cho phép người dùng thực hiện các thao tác xử lý, biến đổi dữ liệu và tải dữ liệu vào Power BI sau khi hoàn tất. 

(2) Queries pane: Hiển thị tất cả các truy vấn (Queries) mà bạn đang thực hiện. 

(3) Current view: Hiển thị bản xem trước của bảng dữ liệu sau khi đã áp dụng các thao tác xử lý dữ liệu. 

(4) Query settings: Nơi lưu lại lần lượt các bước truy vấn, xử lý dữ liệu mà bạn đã thực hiện.

(5) Status bar: Hiển thị thông tin liên quan đến truy vấn như số hàng, cột trong bảng. Tại đây bạn có thể thay đổi Current view từ hiển thị 1000 dòng đầu tiên thành hiển thị toàn bộ dữ liệu.

Status bar

Bạn có thể đọc thêm về giao diện của Power Query trong tài liệu: The Power Query user interface

Vậy là bạn đã nắm được giao diện cơ bản của Power Query, trong phần tiếp theo TM sẽ cùng bạn đi tìm hiểu cách sử dụng Power Query để làm sạch dữ liệu nhé!

3. Sử dụng Power Query để làm sạch dữ liệu trong Power BI thế nào?

Trong những nội dung tiếp theo, TM sẽ hướng dẫn bạn một vài thao tác để xử lý dữ liệu trong Power BI nhé!

3.1. Transpose table (Đảo chiều dữ liệu từ dòng thành cột)

Chọn bảng cần đảo chiều dữ liệu từ dòng thành cột (ở đây là bảng Product Category), vào tab Transform và chọn “Transpose”

Transpose table

Dữ liệu bảng sau khi đảo chiều dữ liệu sẽ có dạng như hình dưới đây:  

kết quả sau khi Transpose

3.2. Promote Headers (Biến dữ liệu thành tên cột)

Có thể thấy bảng kết quả chưa có tên cột mà đang để mặc định là Column 1 và Column 2. Bạn sẽ cần phải biến dòng đầu tiên: product_category_name & product_category_name_english thành tên cột.

Để làm được điều này, quay trở lại tab Home và lựa chọn “Use First Row as Headers” (1) và bạn sẽ nhận được kết quả như hình dưới đây (2): 

Use First Row as Headers

Pivot/Unpivot (Chuyển bảng một chiều thành hai chiều và ngược lại)

Nếu bạn muốn biến dữ liệu từ bảng một chiều thành bảng hai chiều và ngược lại, có thể sử dụng tính năng “Pivot/Unpivot Columns” trong tab Transform. 

Chuyển bảng một chiều thành hai chiều và ngược lại

Nguyên lý hoạt động tương tự với Pivot Table trong Excel & Google Sheet

Split/Merge Columns (Tách/ Kết hợp các cột)

Ví dụ: Bạn đang có một cột dữ liệu chứa First Name, một cột dữ liệu chứa Last Name, bạn muốn kết hợp 2 cột dữ liệu lại với nhau để có 1 cột Full Name, bạn có thể sử dụng tính năng Merge Columns trong Power Query. 

Để thực hiện Merge Columns, bạn lựa chọn cùng lúc 2 cột muốn gộp với nhau, sau đó vào Tab Transform và nhấn “Merge Columns”.

Merge Columns

Sau đó nhập dấu để ngăn cách các ký tự khi gộp 2 cột (Separator) và tên của cột mới, và nhấn “OK” là bạn đã có 1 cột Full Name mới được gộp bởi 2 cột First Name và Last Name. 

Ví dụ Merge Columns

Ngược lại khi muốn tách một cột ra thành nhiều cột, bạn có thể sử dụng tính năng “Split Columns” với các tùy chọn như hình dưới đây để tách các cột. 

Split Columns

Removing duplicates (Loại bỏ các giá trị trùng lặp)

Để loại bỏ các giá trị trùng lặp, bạn click vào tên cột lựa chọn cột có chứa giá trị trùng, nhấp chuột phải, sau đó chọn “Remove Duplicate” 

Loại bỏ các giá trị trùng lặp

Replace value (Thay thế giá trị bằng giá trị khác)

Tính năng này có thể dùng để xử lý những dữ liệu không đồng bộ, hoặc để thay thế các giá trị null (ô trống) bằng giá trị khác. 

Chọn ô có chứa giá trị mà bạn muốn thay thế, nhấp chuột phải sau đó chọn “Replace Value”

Replace Value

Nhập giá trị bị lỗi vào ô “Value To Find” và nhập giá trị muốn thay thế vào “Replace With” 

thay thế giá trị bằng một giá trị khác

Ngoài ra bạn cũng có thể xử lý lỗi Missing Value bằng cách thay thế giá trị bị thiếu bằng giá trị khác với tính năng “Replace Value” ở trên. Ở mục “Value To Find” bạn sẽ nhập chữ null và ở “Replace With” bạn sẽ nhập giá trị muốn thay thế. 

thay thế giá trị null

Change data types (Thay đổi định dạng dữ liệu)

Khi import dữ liệu vào Power BI, Power Query sẽ tự động giúp bạn xác định kiểu dữ liệu, tuy nhiên trong một vài trường hợp kiểu dữ liệu có thể bị xác định sai, lúc này bạn có thể sửa lại bằng cách nhấn vào icon ở đầu mỗi cột, sau đó lựa chọn định dạng dữ liệu mà bạn mong muốn. 

Change data types

Filter Data (Lọc dữ liệu để loại bỏ giá trị ngoại lai)

Dữ liệu ngoại lai là những dữ liệu khác biệt “bất thường” so với những dữ liệu khác, nếu không xử lý sẽ có thể gây ra sự sai lệch trong kết quả phân tích. 

Đọc thêm: Dữ liệu ngoại lai là gì? Xử lý những dữ liệu này thế nào trong phân tích

Bạn có thể sử dụng tính năng Filter trong Power Query để loại bỏ các giá trị này. Dưới đây là cách thực hiện: 

Chọn cột muốn filter, nhấp chuột vào icon tam giác, tùy vào kiểu dữ liệu (data types) mà các tùy chọn filter khác nhau sẽ được hiển thị: Number Filters, Text Filters, Date/Time Filters. 

Đọc thêm: Tài liệu của Microsoft Filter data (Power Query) 

Lọc dữ liệu để loại bỏ giá trị ngoại lai

Sau đó nhập điều kiện Filter vào. Ở đây ví dụ bạn muốn loại bỏ giá trị 15, chọn “is less than or equal to” và điền giá trị 10, sau đó nhấn “OK”. Vậy là bạn đã loại bỏ được giá trị ngoại lai ra khỏi bảng dữ liệu của mình. 

Filter các giá trị nhỏ hơn 10

Tham khảo thêm các phương pháp xử lý dữ liệu khác, cũng như cách áp dụng các cách xử lý này với từng lỗi cụ thể trong khóa Data Analysis của Tomorrow Marketers

4. Tips để làm sạch dữ liệu hiệu quả hơn với Power Query

4.1. Hiểu dữ liệu hơn với tính năng Data Profiling

Data Profiling là công cụ cho pháp bạn hiểu hơn về tệp dữ liệu của mình. Để bật tính năng này, bạn truy cập vào Tab View và tick vào các ô như hình dưới:  

Data Profiling

Công cụ này bao gồm 3 thành phần chính: 

(1) Column quality: Giúp đánh giá chất lượng của dữ liệu: Bao nhiêu % chứa giá trị hợp lệ (Valid), bao nhiêu % chứa giá trị lỗi (Error) và bao nhiêu % số ô trong bảng dữ liệu bị trống (Empty). 

Column quality

(2) Column distribution: Cho thấy sự phân bổ của dữ liệu trong các cột & số lượng giá trị riêng biệt (distinct) và duy nhất (unique). Bạn có thể phát hiện ra liệu dữ liệu có bị duplicate không nhờ tính năng này. 

Column distribution

(3) Column profile: Hiển thị giá trị thống kê của cột: Max, Min, Agerage, Standard deviation,… Ngoài ra bạn còn có thể xem chi tiết hơn sự phân bổ của dữ liệu trong các cột, nhờ vậy mà có thể phát hiện các điểm “khác biệt” trong dữ liệu một cách dễ dàng hơn. 

Column profile

Ví dụ: Giá trị 20 xuất hiện nhiều hơn hẳn so với các giá trị khác, cần chú ý đến điểm này khi phân tích 

Việc hiểu kỹ về tệp dữ liệu của mình sẽ giúp bạn có một định hướng rõ ràng hơn trước khi bắt tay vào xử lý dữ liệu. 

Bạn có thể đọc thêm về Data Profiling trong tài liệu: Data Profiling Tools 

4.2. Advanced Editor với M query

Trong Power BI, mỗi khi bạn thực hiện thao tác xử lý dữ liệu, các câu lệnh Query M lại được tạo ra một cách tự động. 

Câu lệnh M Query được tạo ra khi thực hiện thao tác Promoted Headers

Câu lệnh M Query được tạo ra khi thực hiện thao tác Promoted Headers

Và bạn hoàn toàn có thể “custom” các dòng code này trong cửa sổ Advanced Editor để thực hiện các thao tác xử lý dữ liệu nâng cao hơn không có sẵn trong Power Query. 

cách advanced để sử dụng M query

Để truy cập vào Advanced Editor, chọn bảng muốn xử lý, sau đó vào Tab Home và chọn “Advanced Editor” 

Cách mở Advanced Editor

Cửa sổ Advanced Editor sẽ hiện ra, và bạn có thể chỉnh sửa đoạn code tùy theo nhu cầu xử lý dữ liệu của mình. 

chỉnh sửa đoạn code M query

Bạn có thể đọc thêm về ngôn ngữ này trong tài liệu Power Query M formula language

Tạm kết

Hy vọng những kỹ thuật được hướng dẫn trên sẽ hữu ích cho bạn trong quá trình làm sạch dữ liệu để chuẩn bị cho việc phân tích. 

Nếu bạn muốn tìm hiểu thêm về công cụ Power BI, cũng như muốn cải thiện kỹ năng đọc số phục vụ cho việc ra quyết định, hãy tham khảo khóa Data Analysis của Tomorrow Marketers nhé!

Khóa học sẽ giúp bạn: 

  • Nắm vững quy trình 6 bước phân tích dữ liệu: Define Problem, Data Collection, Data Preparation, Data Exploration (EDA), Building report, Insight & recommendation.
  • Biết cách đặt câu hỏi, xác định đúng bài toàn mà doanh nghiệp đang gặp phải thông qua các analytical framework.
  • Thành thạo cách sử dụng công cụ Power BI để xử lý và trực quan hóa dữ liệu phục vụ cho việc phân tích.
  • Trau dồi khả năng đọc số với các bộ dữ liệu khác nhau như sales,customer, digital,…

Tìm hiểu thêm về khóa Data Analysis – Phân tích dữ liệu cho quyết định chiến lược

Tagged: