Học cách dùng Power Query để tái cấu trúc dữ liệu với Học Excel Online

Bài viết hôm nay của Siêu Marketing sẽ giới thiệu cách làm sao để “chuẩn hóa” dữ liệu, hay nói cách khác, biến chúng thành định dạng “Chuẩn”, bằng việc dùng công cụ Power Query trên Excel.

Quá trình chuẩn bị dữ liệu cẩn thận có thể coi là yếu tố quyết định sự thành công trong việc phân tích dữ liệu. Dữ liệu không được sắp xếp đúng cách sẽ khiến bạn gặp khó khăn trong việc sử dụng Excel; buộc phải dùng đến các công thức Excel cao cấp, phức tạp; cần thêm vào các cột không cần thiết hoặc phải copy dữ liệu lại và lại….

Nhưng khi dữ liệu được sắp xếp theo đúng cấu trúc, công việc trên Excel sẽ dễ dàng hơn nhiều. Cùng khám phá xem Power Query có thể giúp ích như thế nào cho chúng ta trong trường hợp này thông qua vài ví dụ được đề cập trong bài của Siêu Marketing.

Ví dụ 1

Dưới đây là bảng dữ liệu ví dụ:

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-1

Có thể nhận thấy, dữ liệu ban đầu hiện ra với một cột dành cho mỗi quý. Thay vì vậy, chúng ta nên kết hợp các quý vào một cột và đặt tên tiêu đề cột là Quý. Đây chính là cách bố trí dữ liệu mà Siêu Marketing muốn thực hiện trong bài viết này – đó là một cách bố trí “Chuẩn”.

Ta sẽ thực hiện điều chỉnh dữ liệu như mô tả như thế nào đây?

Bạn sẽ thực hiện điều này trong Excel như thế nào? Bạn có sẽ copy và paste thủ công, mất nhiều thời gian và dễ mắc lỗi, sau đó copy lại mỗi khi dữ liệu gốc có sự thay đổi? Hay bạn sẽ xử lý bằng kỹ năng VBA của mình?

KHÔNG, nếu bạn đã biết đến công cụ Power Query, đừng mất công nghiệp vụ nếu không cần thiết, hãy để Power Query giúp bạn làm nhanh chóng và hiệu quả.

Bắt đầu, bạn nên chuyển đổi dữ liệu sang định dạng Table (sử dụng Ctrl + T hoặc Ctrl + L) và đặt cho nó một tên (trong ví dụ này là VD_1). Power Query cung cấp nhiều phương thức để nhập dữ liệu như Table, Name range (Define name), Name động, hoặc đơn giản chỉ cần chọn một vùng dữ liệu, Power Query Editor sẽ tiếp nhận và chuyển nó vào không gian làm việc. Nhưng nếu dữ liệu đó từ Excel, tốt nhất hãy tạo Table.

Kế đến, chỉ việc chọn một ô trong bảng và nhấn From Table/Range.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-2

Power Query Editor sẽ được mở ra.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-3

Bạn có thể thấy trong mục Applied Steps có hai bước:

  • Source: Là bước chỉ nguồn dữ liệu mà bạn nhập vào Power Query (trong trường hợp này là từ Excel).
  • Changed Type: Là bước Power Query tự động nhận diện và định dạng cột dữ liệu khi nhập vào. Nếu bạn không muốn Power Query Editor tự động thực hiện thì có thể xóa bước này bằng cách nhấp vào dấu X bên cạnh nó. Đó là việc mình sẽ làm.

Quay trở lại mục tiêu ban đầu là đưa dữ liệu về dạng “Chuẩn”. Trong cửa sổ Power Query Editor, chọn Tab Transform và chú ý đến mục Unpivot Columns (chức năng chúng ta sẽ dùng trong cả bài).

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-4

Có hai phương pháp mà chúng ta sẽ xem xét:

Cách 1: Sử dụng Unpivot

Phương án lựa chọn Unpivot cho Các Cột Cụ thể hoặc Chỉ một số Cột được chọn

Bằng phương pháp này, bạn đi đến việc chọn lựa các cột Quý 1, Quý 2, Quý 3, và Quý 4, sau đó tiến hành sử dụng tính năng Unpivot Columns hoặc Unpivot Only Selected Columns.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-5

Khi đó, mình đã chọn Unpivot Columns. Tiếp theo đổi tên cột Attribute sang Quý, và Power Query sẽ thực hành bước này, ghi chép lại vào phần Applied Steps. Dưới đây là kết quả bạn sẽ nhận được:

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-6

Chỉ đơn giản vậy thôi, quá nhanh chóng và dễ dàng chứ nhỉ.

Bây giờ, bạn cần quay lại Tab Home, chọn Close & Load để đưa dữ liệu về Excel là đã hoàn tất việc tạo ra một bảng dữ liệu chuẩn. Mình đã lựa chọn Close & Load To… và tùy chỉnh hiển thị dữ liệu kết quả dưới dạng Table, đặt tại vị trí ô I3 trong Sheet VD_1.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-7

Một Table với dữ liệu được chuyển đổi chuẩn sẽ hiện ra với thông tin chi tiết được trình bày như trong hình dưới đây.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-8

Cách thứ hai: Sử dụng chức năng Unpivot Other Columns

Đối với phương pháp này, thay vì chọn 4 cột quý như trong cách đầu tiên, bạn sẽ chọn những cột khác bao gồm cột STT và Hàng hoá. Bạn vào phần Transform và chọn Unpivot Other Columns.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-9

Bạn sẽ thấy, kết quả thu được cũng tương tự như cách đầu tiên.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-10

Vậy điểm khác biệt giữa hai phương pháp này là gì?

Sự khác biệt nằm ở: Nếu sử dụng cách đầu tiên, giả sử bạn thêm vào một hoặc nhiều cột mới vào dữ liệu gốc (ví dụ như cột Quý 5, Quý 6,…) thì khi cập nhật, Power Query sẽ không tự động nhận biết để Unpivot cho những cột quý mới này.

Nếu sử dụng phương pháp thứ hai với Unpivot Other Columns, bạn có thể khắc phục được vấn đề này. Tuy nhiên, bạn cần phải hiểu rõ bản chất của chúng để chọn lựa phương thức Unpivot phù hợp.

Ví dụ số 2

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

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-11

Với cấu tạo dữ liệu này, nó còn tồi tệ hơn ví dụ trước. Nhưng ở đây, việc sắp xếp dữ liệu theo cột STT, Hàng hoá, Tháng, Doanh thu (DT), Chi phí (CP) thì có vẻ hợp lý hơn, đúng không nào? Vậy làm thế nào để thực hiện việc này? Sử dụng Copy, Paste hay VBA? Hãy quên những phương pháp đó đi bởi vì đã có Power Query.

Đầu tiên, bạn tạo một Table và đặt tên cho nó (Mình đã đặt tên Table là VD_2). Với những bước sau đó,

Chúng ta sẽ nhập dữ liệu vào Power Query Editor giống như ví dụ 1. Sử dụng Unpivot, chúng ta cần Unpivot dữ liệu theo cách nào để đạt được dạng chuẩn?

Bạn sẽ thực hiện những bước sau đây để đạt được mục tiêu:

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

Sự lựa chọn này là Unpivot Other Columns, và sau đó, bạn nhận được một bảng dữ liệu như hình dưới đây:

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-12

Bước 2: Chia tách cột Attribute thành hai phần

Quan sát thấy ký tự ở phía sau ký hiệu “_” đại diện cho tháng. Cần tách cột Attribute thành hai phần dùng tính năng Split Columns với ký hiệu “_” làm delimiter.

Nếu delimiter không tồn tại, bạn có thể chỉnh sửa tên cột để phù hợp, từ đó hỗ trợ quá trình xử lý dữ liệu.

Thực hiện xong bước này, bạn sẽ nhận được kết quả như hình dưới, với Attribute.2 chính là cột Tháng cần thiết.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-14

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

Hiện tại, cột Attribute.1 chứa thông tin của DT (doanh thu) và CP (chi phí). Chúng ta cần tách nó thành hai cột riêng biệt.

Bạn sẽ chọn cột Attribute.1 và Value để tiến hành Pivot. Bảng dữ liệu sau khi pivot sẽ trở nên chuẩn như hình sau:

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-15

Qua đó, dữ liệu đã thể hiện một cách hợp lý hơn sau khi được chuyển đổi. Bước tiếp theo, bạn chỉ cần thay đổi tên cột, điều chỉnh định dạng của dữ liệu, sau đó bạn có thể Close & Load vào Excel.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-16

Liệu còn tình huống dữ liệu nào khó xử lý hơn không? Hãy cùng tìm hiểu qua ví dụ 3 với Siêu Marketing.

Ví dụ 3

Dữ liệu trong ví dụ này giống như những ví dụ trước, nhưng lần này chúng ta có tiêu đề bảng là Merger, điều mà bạn nên hạn chế sử dụng.

Đối với tình huống tiêu đề bị Merge, có một số cách giải quyết như sau:

Cách 1: Giải quyết tiêu đề ngay trên Excel

Bạn có thể gỡ bỏ Merger ngay tại Excel và đổi tên các tiêu đề cho phù hợp để đưa dữ liệu về dạng tương tự như trong Ví dụ 2 mà Siêu Marketing đã giới thiệu.

Cách 2: Giải quyết trong Power Query Editor

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-17

Dĩ nhiên, chúng ta cũng phải chuyển đổi dữ liệu này về dạng chuẩn như trong ví dụ 2. Cách xử lý trong Power Query như thế nào?

Đầu tiên, giống như trong các ví dụ trước, bạn sẽ tạo một bảng và đặt tên (ở đây mình đã đặt là VD_3).

Lưu ý: Trong trường hợp này, chúng ta không chọn “My table has headers” khi tạo bảng.

Bắt đầu nhập dữ liệu vào Power Query và chúng ta sẽ cần…

Chúng ta cần áp dụng một số thao tác nhất định để chuyển dữ liệu nhập vào Power Query thành dữ liệu chuẩn hóa. Các bước cụ thể như sau:

Khi bạn nhập dữ liệu vào Power Query, nó sẽ xuất hiện giống như hình ảnh dưới đây, với các ô trắng không chứa thông tin nào sẽ hiển thị là null.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-19

Bước 1: Đổi tên cột theo một quy tắc cụ thể

Bạn cần đặt tên mới cho các cột theo quy tắc đã xác định trước để áp dụng có hiệu quả những bước tiếp theo giống như trong Ví dụ 2.

Bước 2: Loại bỏ các dòng

Chúng ta sẽ xóa hai dòng đầu tiên trong bảng dữ liệu sử dụng tùy chọn Remove Rows từ Tab Home.

huong-dan-su-dung-power-query-de-chuan-hoa-du-lieu-20

Bước 2: Áp dụng các bước giống như Ví dụ số 2

Sau khi hoàn thành hai bước đầu, dữ liệu của chúng ta sẽ tương đồng với dữ liệu mẫu trong Ví dụ 2 và bạn cần thực hiện những bước tương tự bao gồm Unpivot -> Tách cột -> Pivot để thu được kết quả như mong muốn.

Những điểm lưu ý

Qua ba ví dụ xử lý dữ liệu trên, chúng ta nên ghi nhớ một số điểm sau:

– Mọi thao tác trong Power Query Editor sẽ được ghi lại bằng M Code (ngôn ngữ mà Power Query dùng để lưu các thao tác) tương tự như Code VBA trong Excel. Lợi ích của việc này là bạn chỉ cần làm mới dữ liệu, và quy trình bạn đã thiết lập trong Power Query Editor sẽ tự động thực thi, mang lại kết quả nhanh chóng và chuẩn xác.

– Trong bài viết này, các Bước 1 và 2 ở Ví dụ 3 cần được thực hiện manual, có thể nảy sinh vấn đề khi bạn bổ sung thêm cột dữ liệu mới vào DATA gốc và Power Query Editor không thể tự đổi tên cột đó cho bạn. Vì vậy, bạn nên có cách sắp xếp dữ liệu hiệu quả hơn.

– Các thao tác trong Power Query Editor cần có tính chất chung và thống nhất, điều quan trọng là phải xây dựng một quy trình xử lý dữ liệu chuẩn sao cho mỗi khi cập nhật dữ liệu nguồn, quy trình đó vẫn hoạt động chính xác mang lại kết quả mong muốn.

Kết thúc

Siêu Marketing kỳ vọng rằng qua bài viết này, bạn đã nắm bắt được phương pháp chuyển đổi dữ liệu sang dạng chuẩn sử dụng Power Query trong Excel. Power Query là công cụ hỗ trợ mạnh mẽ của Excel, giúp việc xử lý và biến đổi dữ liệu trở nên dễ dàng, hiệu quả và hiệu suất cao. Giờ đây, việc chuẩn hóa dữ liệu, kết hợp các tệp tin và các Sheets trong Excel trở nên đơn giản hơn bao giờ hết nhờ Power Query.

Power Query, mặc dù còn khá mới mẻ, nhưng việc nắm vững cách sử dụng nó sẽ là cầu nối giữa Excel và Power BI – Một công cụ báo cáo vô cùng tốt.


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

Hàm DAX trong Power BI

PBI101 – Microsoft Power BI Desktop, Business Intelligence đến gần bạn hơn

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop