Hướng dẫn sử dụng Power Query để chuẩn hóa dữ liệu

Thông thường, dữ liệu cần được chuyển đổi từ cấu trúc này sang một cấu trúc khác phù hợp trước khi được sử dụng trong Power BI để phân tích (mặc dù một số bạn mới làm quen với data sẽ không nhận ra điều này). Một ví dụ điển hình bên dưới, data bên trái cần được chuyển đổi sang cấu trúc như bảng bên phải. Như bạn có thể thấy trong bảng bên trái, cột A chứa tên thuộc tính và cột B chứa các giá trị tương ứng. Mỗi 4 dòng sẽ là 1 record. Vấn đề này rất thường thấy khi bạn lấy data (file CSV) được trích xuất từ một hệ thống khác, đặt biệt là các hệ thống cũ không thể tùy chỉnh format data xuất ra. Vậy trong bài chia sẻ này mình sẽ giúp các bạn sử dụng Power Query để chuẩn hóa dữ liệu.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Cách chuẩn hóa dữ liệu bằng Power Query

Đối với những định dạng dữ liệu như trên, bạn không thể xoay dữ liệu mộ cách trực tiếp. Đây là lý do vì sao bạn không thể. Hãy thử click vào column A chọn “Pivot Column” trong Power Query, bạn sẽ thấy kết quả như bên dưới.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Cách giải quyết này không khả thi vì Power Query không nhận biết được sự lặp lại ở các bộ dữ liệu, do vậy, mình sẽ giúp cho bạn giải quyết vấn đề ở phía trên bằng một cách rất đơn giản như sau:

Bước 1:

Tải dữ liệu vào Power Query, chọn tab Data (1) và chọn From Table/Range (2). Lưu ý là dữ liệu của bạn đã được định dạng table.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 2:

Trong cửa sổ Power Query, chọn tab Add Column (1) , tiếp đó click chọn Index Column (2), sau đó một cột dữ liệu số được thêm vào (3).

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 3:

Sau đó, mình chuyển đổi cột dữ liệu số này thành số hiệu duy nhất cho mỗi record. Để làm được điều đó mình chọn Transform tab (1), Standard (2) và chọn Integer-Divide (3).

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Mình điền giá trị 4 (1) đó là số lượng dòng trong bảng dữ liệu gốc hiện cần dùng để chuyển thành 1 dòng.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bây giờ, cột giá trị mới của mình hiển thị số hiệu định danh mỗi record. ( xem 0, 1, 2, 3, 4, 5) trong bảng bên dưới.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 4: Đây là bước cuối cùng.

Mình chọn cột đầu tiên (1), vào Transform tab (2) và click chọn Pivot Column (3).

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Trong cửa sổ Pivot Column (1) mình chọn Column2 (tên cột thứ 2 của bảng dữ liệu) cho Values Column (2) và sau đó tiếp tục chọn Advanced Options (3) và thiết lập Don’t Aggregate (4).

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Dữ liệu sau khi xoay sẽ trông như thế này.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Mình không cần dùng cột số hiệu nên mình xóa nó. Sau đó là các bước chỉnh format.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Sau đó các bạn vào Home (B1), Close & Load (B2), Close & Load to…(B3).

cách tổng hợp dữ liệu từ nhiều file excel
cách tổng hợp dữ liệu từ nhiều file excel

Sau đó file sẽ hiện ra 1 bảng Import Data cho phép bạn trích xuất dữ liệu vừa chuyển đổi dưới dạng mà các bạn mong muốn, tại bên các bạn chọn Table (B1), New Worksheet (B2), OK (B3). Dữ liệu của bạn sẽ được đưa về định dạng chuẩn như mình có giới thiệu ban đầu

Vậy là sau vài thao tác đơn giản, các bạn đã có thể chuyển định dạng bảng dữ liệu như phía trên trái thành dữ liệu chuẩn như bảng bên phải thông qua vào thao tác nhỏ. Đây chính là điểm mạnh của Power Query trong chuẩn hóa dữ liệu.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Cách tự động cập nhập file Excel bằng Power Query

Giả sử, hàng ngày bạn đều nhận được một file tương tự như tình huống trên. Thì Power Query cho phép bạn tự động hóa file này bằng cái Refresh dữ liệu, mà không cần lặp đi lặp lại các thao tác trên.

Bước 1: Kết nối file dữ liệu hàng ngày nhận được thông qua thao tác.

Data --> Get Data --> From File --> From Workbook

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 2: chọn tên file mà bạn muốn cập nhập hàng ngày

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 3. Thực hiện thao tác load file ra sheet. Lưu ý, các file bạn chọn có thể file được định dạng table hoặc không, do đó nên chọn tên sheet để load ra. Như hình phía dưới thì table sẽ có ký hiệu như Table1 và tên sheet sẽ có ký hiệu như DATA.

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Bước 4: Lặp lại các thao tác thiết lập ban đầu như mình hướng dẫn ở phần đầu, để chuẩn hóa dữ liệu

Bước 5: Cập nhập các file mới nhận được hàng ngày bằng cách

Data --> Refresh All --> Refresh All

sử dụng Power Query để chuẩn hóa dữ liệu
sử dụng Power Query để chuẩn hóa dữ liệu

Vậy là toàn bộ các file mà hàng ngày bạn phải chuyển đổi thủ công hằng ngày có thể được cập nhập tự động bằng cách này. Tất cả những thiết lập này duy nhất 1 lần và bạn có thể sử dụng mãi mãi cho các lần sau này.

Power Query ngoài giúp bạn chuẩn hóa dữ liệu, hay tự động cập nhập các file thì còn rất nhiều tính năng khác. Các bạn có thể tham khảo bài viết Power Query là gì để có thể có một góc nhìn khác về các tính năng của Excel hiện đại.

Học Power Query ở đâu?

Với thiết kế thân thiện với người dùng, Power Query là một công cụ tuyệt vời cho những người thường xuyên phải làm việc trên Excel. Việc sử dụng công cụ này rất đơn giản, chỉ với các thao tác kết nối dữ liệu, chuyển đổi, hoặc kết hợp và cuối cùng chia sẻ dữ liệu. Do vậy, người dùng có thể tự học với sự hướng dẫn từ khóa học nền tảng của chúng tôi để có thể tự động hóa 80% công việc hàng ngày mà các bạn đang phải thao tác thủ công hàng ngày, đồng thời biết thêm các tính năng vượt trội khác của Power Query. Bên cạnh đó, bạn có thể tham khảo bài viết chia sẻ giữa việc học Power Query và VBA, tôi tin rằng mức độ phổ biến và ứng dụng Power Query trong công việc rộng rãi và đơn giản hơn rất nhiều.

Power Query là một công cụ vô cùng hữu ích cho tất cả những bạn thường xuyên phải sử dụng Excel trong công việc. Thêm nữa, Power Query được thiết kế dành cho tất cả người dùng không có khả năng viết ngôn ngữ lập trình (coding) nhưng vẫn có thể dùng để tự động hóa công việc hàng ngày.

Uniace hy vọng sẽ được đồng hành cùng các bạn trong các khóa học theo từng cấp độ của chúng tôi.

Trả lời