Chuẩn hoá dữ liệu đơn giản trên Excel bằng Power Query

Trong bài viết này, Gitiho.com sẽ hướng dẫn bạn cách để chuẩn hoá dữ liệu với công cụ Power Query trong Excel nhé.

Hướng dẫn sử dụng Power Query trong Excel để chuẩn hoá dữ liệu thông qua ví dụ cụ thể

Có một dữ liệu tốt là yếu tố quan trọng nhất trong phân tích dữ liệu. Nếu dữ liệu không được chính xác, bạn sẽ mất thêm khá nhiều thời gian và công sức để thêm các cột mới, sao chép dữ liệu, thậm chí sẽ phải sử dụng những công thức Excel nâng cao.

Với Power Query, các thao tác làm việc với dữ liệu trong Excel trở nên dễ dàng hơn rất nhiều. Sau đây sẽ là một vài ví dụ cụ thể về việc dùng Power Query trong Excel để chuẩn hóa dữ liệu:

Ví dụ 1

Cho bảng dữ liệu bên dưới như sau:

Như bạn có thể thấy, dữ liệu gốc hiển thị riêng mỗi cột theo mỗi quý. Cách làm này là ổn, nhưng liệu có thể tối ưu hóa hơn được không? Câu trả lời là có! chúng ta có thể đưa các quý đó vào 1 cột riêng và đặt tên cho cột đó là "Quý". Như vậy việc phân loại dữ liệu sẽ được trực quan và rõ ràng hơn rất nhiều

Với Power Query, việc thiết kế khung dữ liệu sẽ trở nên nhanh chóng hơn bao giờ hết.

Đầu tiên, bạn nên đưa dữ liệu trên về dạng Table (Phím tắt là Ctrl + T) và đặt tên cho Table đó. Power Query sẽ hỗ trợ cho bạn khá nhiều kiểu để nhập dữ liệu vào như Table, Name range (Define name), Name động hay thậm chí bạn chỉ cần quét chọn 1 vùng thì Power Query sẽ nhận vùng đó để đưa vào Power Query Editor. Tuy nhiên, với dữ liệu được lấy từ Excel, tốt nhất là bạn nên tạo Table cho nó.

Tiếp theo, bạn hãy chọn 1 ô bất kỳ trong bảng đó vào chọn From Table/Range

Lúc này, Cửa sổ Power Query Editor sẽ mở lên.

Ở mục Applied Steps, bạn sẽ nhận thấy 2 điều:

• Source: Đây là nguồn dữ liệu mà bạn nhập vào Power Query (trong bài viết này là dữ liệu từ Excel)
• Changed Type: Đây là bước mà Power Query tự nhận kiểu định dạng cho mỗi trường thông tin (cột) khi nhập dữ liệu vào. Nếu bạn không muốn Power Query tự động thay đổi định dạng, bạn có thể bỏ bước này bằng cách ấn vào dấu X ngay đầu dòng. Ở đây mình sẽ bỏ changed type đi

Sau đó, tại cửa sổ Power Query Editor, bạn vào Tab Transform và để ý tới mục Unpivot Column, chúng ta sẽ sử dụng đến nó xuyên suốt bài viết này.

Từ bước này, ta sẽ có cách làm như sau:

Cách 1: Sử dụng Unpivot Columns hoặc Unpivot Only Selected Columns

Với cách này, bạn chọn các cột Quý 1, Quý 2, Quý 3, Quý 4. Sau đó chọn Unpivot Columns hoặc Unpivot Only Selected Columns.

Ở đây mình lựa chọn Unpivot Columns. Sau đó bạn có thể đổi tên cột Attribute thành Quý. Power Query sẽ thực hiện và ghi lại bước đó trong mục Applied Steps và bạn đã có thể có riêng một cột quý rồi. Rất nhanh gọn và đơn giản.

Giờ bạn chỉ cần quay lại Tab Home, chọn Close & Load to thôi là bạn đã có thể có được 1 bảng dữ liệu đúng “Chuẩn” rồi. Ở đây, mình chọc Close & Load To…. và chọn cách xem dữ liệu trả về là Table và đặt tại ô I3 trong Sheet VD_1.

Kết quả trả về là 1 Table với dữ liệu được chuyển đổi về đúng Chuẩn hóa thông tin mình cần.

Cách 2: Sử dụng Unpivot Other Columns

Với cách này. Thay vì lựa chọn 4 cột Quý 1, Quý 2, Quý 3, Quý 4 như cách 1 thì bạn sẽ lựa chọn các cột còn lại trong bảng đó là cột STT và cột Hàng hoá. Giống như trên, bạn hãy vào Transform và lựa chọn Unpivot Other Columns

Kết quả trả về cũng sẽ tương tự như bước 1.

 

Nếu bạn làm theo cách 1, giả sử khi bạn có thêm 1 hoặc nhiều cột nữa vào dữ liệu gốc thì khi bạn cập nhật, Power Query sẽ không tự hiểu để Unpivot cho những Columns quý mà bạn thêm mới đó. 

Cách làm thứ 2 sẽ khắc phục được điều trên. Tùy vào từng điều kiện cụ thể mà bạn có thể áp dụng các cách sao cho phù hợp.

Xem thêm: Cách gộp nhiều file Excel vào một file bằng Power Query

Ví dụ 2

Ở ví dụ này, bảng dữ liệu gốc lại được cấu trúc như sau:

Với tổ chức dữ liệu như này, nó còn phức tạp hơn ví dụ 1 ở trên . Ở đây đáng ra nên tổ chức thành các cột là STT, Hàng hoá, Tháng, Doanh thu (DT), Chi phí (CP) thì sẽ hợp lý hơn rất nhiều.

Cũng như ở vi dụ 1, đầu tiên bạn tạo 1 Table và đặt tên cho nó, rối sau đó ta nhập dữ liệu vào Power Query Editor. Vẫn với Unpivot nhưng lần này ta sẽ Unpivot như nào để dữ liệu này ra được dạng Chuẩn?

Các bước thực hiện sẽ như sau:

Bước 1: Thực hiện Unpivot

Chọn Unpivot Other Columns, sau đó cột sẽ hiện:

Bước 2: Tách cột Attribute thành 2 cột

Ta thấy, những ký tự bên phải dấu _ chính là tháng. Ở đây, ta sẽ thực hiện tách cột Attribute thành 2 cột bằng tính năng Split Columns với delimiter là dấu _

Trong trường hợp delimiter không có thì bạn có thể tạo ra bằng cách sửa tên cột cho phù hợp, để đáp ứng được cho việc xử lý dữ liệu.

Sau khi làm xong ta sẽ thấy hiện thêm cột Attribute 2, đó chính là cột tháng mà ta cần.

Bước 3: Pivot cột Attribute 1

Cột Attribute 1 đang chứa cả DT (doanh thu) và CP (chi phí). Bây giờ chúng ta cần tách nó ra làm 2 cột.

Bạn chọn cột Attribute 1 và cột Value để thực hiện Pivot. Sau khi làm kết quả sẽ như sau:

Lúc này nhìn cách sắp sếp dữ liệu trở nên hợp lý rồi đúng không? Bây giờ bạn chỉ cần đổi tên và định dạng dữ liệu cho cấc cột là có thể Close & Load vào Excel được rồi.

Xem thêm: Hướng dẫn các thao tác căn bản trong Power Query (Phần 1)

Ví dụ 3

Ví dụ lần này sẽ khó hơn lần trước nhiều đấy.

Cũng với dữ liệu giống như các ví dụ trước, nhưng lần này tiêu đề của bạn lại có Merge.

Với trường hợp tiêu đề của bảng có Merge, ta có thể xử lý theo những cách sau:

Cách 1: Xử lý tiêu đề trực tiếp trên Excel

Lúc này bạn chỉ cần xóa merge trên tiêu đề đi và áp dụng các bước ở ví dụ 2 là xong.

Cách 2: Xử lý bên trong Power Query Editor

Như ở các ví dụ trước, đầu tiên bạn tạo cho nó 1 Table và đặt tên

Lưu ý: Trường hợp này ta sẽ bỏ chọn phần “My table has headers” trong hộp thoại tạo Table.

Thực hiện nhập dữ liệu vào Power Query, sau đó bảng sẽ hiện ra như thế này, những ô không có dữ liệu sẽ hiện null.

Tiếp đến, ta làm các bước sau:

Bước 1: Thực hiện đổi tên cho các cột (có quy tắc nhất định)

Ở bước này, ta sẽ đổi tên cho các cột này theo một quy tắc nhất định. để làm điều này, ta sẽ xoá 2 dòng đầu tiên trong dữ liệu bằng chức năng Remove Rows trong Tab Home.

Bước 2: Thực hiện các bước như ví dụ 2

Sau khi thực hiện 2 bước trên, dữ liệu lúc này sẽ giống như dữ liệu gốc ở Ví dụ 2, và bạn sẽ thực hiện các bước tương tự để trả về đúng dữ liệu dạng chuẩn. Chọn Unpivot -> Tách cột -> Pivot, để ra được kết quả giống kết quả của Ví dụ 2.

Lưu ý trong việc sử dụng Power Query trong Excel để chuẩn hóa dữ liệu.

• Các thao tác được thực hiện trong Power Query Editor sẽ được M Code ghi lại (M Code là ngôn ngữ mà Power Query sử dụng để ghi lại các bước được áp dụng, gần giống với Code VBA trong Excel). Bạn có thể cập nhập, thêm bớt, chỉnh sửa dữ liệu bằng cách refresh, ngay lập tức những quy trình bạn thực hiện trong Power Query Editor sẽ thực thi và cập nhật kết quả 1 cách nhanh chóng, chính xác. 
• Ở Bước 1 và Bước 2 trong Vi dụ 3 vừa rồi ta phải làm thủ công, điều này có thể sẽ có một số vấn đề như khi bạn thêm các trường (cột) dữ liệu khác vào dữ liệu gốc thì việc đổi tên cột đó Power Query Editor sẽ không hiểu để đổi cho bạn thành tên gì. Do đó, tốt hơn hết là bạn nên tổ chức dữ liệu 1 cách hiệu quả hơn.
• Các thao tác mà ta thực hiện trong Power Query Editor cần có tính chung, thống nhất, ta phải tạo ra 1 quy trình chung trong xử lý dữ liệu để mỗi lần cập nhật dữ liệu gốc thì quy trình này vẫn hoạt động đúng và trả về kết quả chính xác cho chúng ta.

Xem thêm: Hướng dẫn các thao tác căn bản trong Power Query (Phần 2)

Kết luận

Hy vọng qua bài viết này, bạn đọ có thể nắm được cách dùng Power Query trong Excel để có thể hỗ trợ cho việc chuẩn hóa dữ liệu. Chúc các bạn áp dụng vào công việc hiệu quả và thành công.

Bên cạnh đó, hãy tham khảo khóa học về Power Query để có thành thạo hơn trong việc sử dụng Excel làm công cụ phân tích chuyên sâu nhé.