Trong bài viết này, Học Excel Online sẽ hướng dẫn cách chuẩn hoá dữ liệu, nói cách khác là đưa dữ liệu về dạng “Chuẩn” với công cụ Power Query trong Excel.

Chuẩn bị dữ liệu tốt có lẽ là yếu tố thành công quan trọng nhất trong phân tích dữ liệu. Nếu bố cục dữ liệu không đúng, bạn sẽ tiếp tục chiến đấu với Excel; phải sử dụng các công thức Excel nâng cao, rất phức tạp; thêm các cột không cần thiết hoặc thậm chí sao chép dữ liệu,….

Nhưng nếu dữ liệu nằm trong cấu trúc phù hợp, Excel sẽ trở nên dễ dàng. Hãy cùng xem Power Query sẽ giúp gì cho chúng ta trong trường hợp này qua một số ví dụ trong bài viết này của Học Excel Online.

Ví dụ 1

Ở đây, mình có bảng dữ liệu như sau:

Bạn có thể thấy, dữ liệu gốc hiển thị một cột riêng cho mỗi quý. Tuy nhiên, thay vì có một cột cho mỗi quý thì tại sao ta không đưa các quý đó vào 1 cột riêng và đặt tên tiêu đề cột là Quý. Đây là cách bố trí dữ liệu mà Học Excel Online đang cố thực hiện trong bài viết này – Một cách bố trí dữ liệu “Chuẩn”.

Vậy làm như thế nào để ta có thể thiết kế lại dữ liệu như đã mô tả trên?

Bạn sẽ làm điều đó trong Excel như thế nào?. Bạn sẽ Copy và Paste một cách thủ công và rất dễ sai sót, nhầm lẫn,…và mỗi lần dữ liệu gốc thay đổi bạn lại Copy lại???. Hay là bạn sẽ xử lý nó với kỹ năng VBA mà mình có???

KHÔNG, Không nên một chút nào nếu như bạn đã có công cụ Power Query thì hãy để nó xử lý việc đó cho bạn một cách rất nhanh chóng và hiệu quả.

Đầu tiên bạn nên đưa dữ liệu trên về dạng Table (Ctrl + T or Ctrl + L) và đặt tên cho Table nó 1 cái tên (ở đây mình đã đặt tên là VD_1). Power Query hỗ trợ cho bạn khá nhiều kiểu để Input 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 thì tốt nhất là bạn nên tạo Table cho nó.

Tiếp theo, bạn chỉ cần 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 có thể thấy có 2 bước:

• Source: Đây là nguồn dữ liệu mà bạn Input vào Power Query (trong bài viết này là 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 nào Input dữ liệu vào. Nếu bạn không muốn Power Query Editor tự động thay đổi định dạng như vậy thì bạn có thể bỏ bước này bằng cách ấn vào dấu X phía trước bước đó. Mình sẽ làm điều đó.

Quay lại với mục đích ban đầu là đưa dữ liệu về dạng “Chuẩn”. Tại cửa sổ Power Query Editor bạn vào Tab Transform và để ý tới mục Unpivot Columns (ta sẽ sử dụng nó xuyên suốt bài viết này).

Ta sẽ có 2 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 đó thực hiệ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ó kết quả sau khi thực hiện như sau:

Như vậy là xong rồi, thật đơn giản và nhanh chóng phải không nào .

Giờ bạn chỉ cần quay lại Tab Home chọn Close & Load vào Excel 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” với các trường thông tin (cột) như hình dưới.

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á. Vào Transform và lựa chọn Unpivot Other Columns

Bạn có thể thấy, kết quả trả về cũng sẽ giống như cách 1

Vậy, câu hỏi đặt ra là sự khác biệt giữa 2 cách này là gì?

Sự khác biệt ở đây là: Nếu bạn làm theo cách 1 thì giả sử khi bạn có thêm 1 hoặc nhiều cột nữa vào dữ liệu gốc (VD: thêm cột Quý 5, Quý 6,….) 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òn với cách làm thứ 2 lựa chọn Unpivot Other Columns sẽ khắc phục được điều đó. Tuy nhiên, bạn cũng cần hiểu rõ bản chất hơn nữa để lựa chọn cách Unpivot cho phù hợp.

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, thậm chí còn tồi tệ 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ì có vẻ hợp lý hơn phải không nào. Vậy, làm điều đó như thế nào đây???. Copy, Paste hay VBA đây???. Hãy quên những cách đó đi vì đã có Power Query.

Đầu tiền, bạn cần tạo 1 Table và đặt cho nó 1 cái tên (Mình đã đặt VD_2 là tên của Table này). Cũng với các bước như ví dụ 1, ta thực hiện Input dữ liệu vào Power Query Editor. Vẫn với Unpivot, và vấn đề là ta sẽ Unpivot như nào để dữ liệu này ra được dạng “Chuẩn” ?.

Ta sẽ thực hiện điều đó với các bước sau:

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

Chọn Unpivot Other Columns, sau bước này ta được kết quả như sau:

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 _ nó chỉnh là tháng. 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.

Kết quả sau khi thực hiện bước này bạn sẽ được như sau, lúc cột Attribute.2 nó 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í), đúng ra thì nó phải là 2 cột phải không, 1 cột là DT và 1 cột là CP.

Bạn chọn cột Attribute.1 và cột Value để thực hiện Pivot. Kết quả sau bước này bạn sẽ có bảng dữ liệu đúng “Chuẩn” như hình

Nhìn dữ liệu sau khi chuyển đổi có vẻ khả quan và hợp lý hơn ban đầu phải không nào. Bây giờ bạn chỉ cần thực hiện một số thao tác đơn giản như đổi tên tiêu đề các cột, định dạng dữ liệu cho các cột là có thể Close & Load vào Excel được rồi.

Có trường hợp dữ liệu nào khó đỡ hơn nữa không đây???. Hãy cùng Học Excel Online sang ví dụ 3.

Ví dụ 3

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ó Merger (một điều mà bạn nên hạn chế sử dụng).

Với trường hợp tiêu đề của bảng có Merger thì ta có một số hướng xử lý như sau:

Cách 1: Xử lý tiêu đề trên Excel luôn

Với cách này, bạn sẽ bỏ Merger ngay trong Excel luôn và đổi tên lại các tiêu đề đó phù hợp, để có thể đưa về được dạng như Ví dụ 2 mà Học Excel Online đã giới thiệu ở trên.

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

Tất nhiên là ta cũng cần phải chuyển dữ liệu này về dạng như ví dụ 2. Xử lý như nào đây trong Power Query???

Đầu tiên thì cũng như các Ví dụ trước là bạn tạo cho nó 1 Table và đặt tên (mình đã đặt tên ở đây là VD_3).

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 Input dữ liệu vào Power Query và ta cũng sẽ cần một số bước để biến đổi dữ liệu này về dạng “Chuẩn”, cụ thể như sau:

Sau khi Input dữ liệu vào Power Query ta sẽ trông nó như này đây, những ô không có dữ liệu gì sẽ hiện null.

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 để có thể vận dụng được các bước trong Ví dụ 2, cụ thể như sau:

Bước 2: Thực hiện xoá dòng

Tiếp theo 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ề được dữ liệu đúng “Chuẩn”. bạn Unpivot -> Tách cột -> Pivot, để ra được kết quả giống kết quả của Ví dụ 2.

Một vài lưu ý

Qua 3 ví dụ xử lý dữ liệu trên, ta có một vài lưu ý như sau:

– Các thao tác ta 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 như Code VBA trong Excel. Điều này rất thuận khi chúng ta cập nhật thêm, bớt, chỉnh sửa,…. dữ liệu ở DATA gốc thì bạn chỉ cần 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ả cho bạn 1 cách nhanh chóng, chính xác.

– Trong bài viết này, ở Bước 1 và Bước 2 trong Vi dụ 3 này 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 DATA 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.

– Từ đây bạn có thể rút ra: 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.

Kết luận

Học Excel Online hy vọng tới đây bạn đã nắm được cách xử lý dữ liệu, chuyển đổi dữ liệu về dạng “Chuẩn” với Power Query trong Excel. Power Query là một công cụ mạnh được Excel hỗ trợ giúp cho bạn xử lý dữ liệu, biến đổi dữ liệu một cách khá đơn giản, hiệu quả và hiệu suất cao. Với Power Query bây giờ việc chuẩn hoá dữ liệu, gộp file, gộp các Sheets trong Excel trở nên đơn giản hơn bao giờ hết

Power Query là một công cụ rất mạnh. Tuy nhiên, còn khá mới mẻ với chúng ta. Việc nắm được cách sử dụng công cụ này sẽ là cầu nối giữa Excel tới Power BI – Một công cụ để làm báo cáo tuyệt vời.

Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query

Hàm DAX trong Power BI

PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay