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, đặc biệt là các hệ thống cũ không thể tùy chỉnh format data xuất ra. 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.
Tuy nhiên, trước khi đi vào hướng dẫn sử dụng power query để chuẩn hóa dữ liệu. Mình sẽ giới thiệu về Power Query và ứng dụng để giúp các bạn mới hiểu hơn về công cụ này.
High Potential Talent: Chương trình dành cho bất kỳ ai muốn nghiêm túc phát triển bản thân để đạt được những thành công vững chắc và sớm hơn. Xem chi tiết.
Sau 7 năm phát triển, Power Query thực sự đã đem lại cho người dùng nhiều tính năng vượt trội. Tuy nhiên, tới nay nhiều người sử dụng Excel ở Việt Nam vẫn chưa hình dung được Power Query là gì.
Trước đây, những người được cho là chuyên gia về Excel sẽ sử dụng các công cụ VBA để tự động hóa dữ liệu, đơn giản nhất như việc sao chép và dán dữ liệu từ nhiều nguồn.
Nhưng không phải ai cũng có khả năng viết code – ngôn ngữ lập trình VBA này. Vậy giải pháp của Excel hiện đại chính là sử dụng Power Query.
Power Query là gì?
Một cách tổng quan thì Power Query là một tập hợp rất nhiều tính năng, chuyên dụng cho mục đích xử lý dữ liệu một cách tự động hoàn toàn. Hướng tới những người dùng không có khả năng sử dụng ngôn ngữ lập trình.
Thực tế đây là một tính năng cần được add-in (tiện ích mở rộng) từ trong các phiên bản Excel 2010. Tuy nhiên, từ Excel 2016 thì Power Query đã được thể hiện sẵn trong phần Data.
Các ứng dụng của Power Query trong Excel
Power Query trong tự động hóa dữ liệu
Bằng việc sử dụng công cụ này trên Excel sẽ giúp các bạn tự động hóa dữ liệu một cách nhanh chóng. Tôi có thể đưa ra một ví dụ cho các bạn hình dung như sau:.
Ví dụ: Nhân viên A hàng ngày phải tải 10 file dữ liệu từ hệ thống. Công việc của nhân viên này là copy 10 file này bỏ vào 10 sheet khác nhau của 1 file Excel khác, sau đó viết các công thức để ra doanh số bán hàng trong ngày hôm trước.
Công việc thực hiện lặp đi lặp lại hàng ngày. Nếu như nhân viên A không biết sử dụng công cụ Power Query, bạn phải thực hiện thao tác này với thời gian trung bình 20 phút mỗi ngày.
Tuy nhiên, việc kết nối dữ liệu và refresh dữ liệu bằng Power Query thì thao tác này chỉ chạy chưa tới 1 phút.
Ngoài ví dụ trên, Power Query còn rất nhiều ứng dụng khác trong tự động hóa dữ liệu. Vui lòng tham khảo khóa học nền tảng của chúng tôi.
Ứng dụng trong làm sạch dữ liệu
Khi nhận được file dữ liệu từ người khác cung cấp, không phải lúc nào chúng ta cũng có một dữ liệu chuẩn để làm việc. Do vậy việc chuẩn hóa dữ liệu là một điều bắt buộc và tiên quyết trước khi xử lý.
Hình dưới đây sẽ mô tả cho bạn đọc biết một bảng dữ liệu như thế nào là cấu trúc đúng và chưa đúng.
Do vậy, thông thường khi nhận được dữ liệu chưa đúng cấu trúc sẽ tốn rất nhiều thời gian để xử lý. Tuy nhiên, với việc áp dụng công cụ Power Query trong việc làm sạch dữ liệu sẽ giúp các bạn tiết kiệm được rất nhiều thời gian.
Đối với trường hợp dữ liệu như trên, các bạn chỉ cần dùng tính năng Unpivot là có thể ra được bảng đúng cấu trúc trong vòng 1 click. Đặc biệt, Power Query vẫn lưu trữ lại phần dữ liệu gốc ban đầu, dù bên trong người sử dụng có thay đổi format và công thức.
Sử dụng Power Query trong phân tích dữ liệu
Bằng việc thao tác và sử dụng ngôn ngữ M Query hoặc kéo thả vô cùng đơn giản giúp cho người dùng có thể viết các công thức Excel hoặc kết hợp các dữ liệu từ nhiều nguồn. Kết quả được load ra sẽ không bao gồm các công thức trung gian, giúp giảm thiểu dung lượng của file dữ liệu. Đồng thời thể hiện những thông tin cần thiết cho người đọc.
Bên cạnh đó, Power Query là một công cụ đắc lực cho nhân viên làm nghề phân tích dữ liệu – Business Intelligence trong việc phân tích những dữ liệu đa nguồn. Bằng việc kết hợp với Power BI sẽ giúp người dùng mô phỏng kết quả thông qua các biểu đồ, bảng biểu một cách tự động. Bạn nên đọc thêm business intelligence là gì để hiểu hơn về công cụ này.
Nên học power bi ở đâu tốt và hiệu quả
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.
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.
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).
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).
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.
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.
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). 
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). 
Dữ liệu sau khi xoay sẽ trông như thế này. 
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. 
Sau đó các bạn vào Home (B1), Close & Load (B2), Close & Load to…(B3).
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. 
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.
Bước 2: chọn tên file mà bạn muốn cập nhập hàng ngày.
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.
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.
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.
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.
Cập nhật các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel.