Power Query và 5 phút hướng dẫn sử dụng Power Query trong Power BI

Power Query là bộ công cụ dùng để phân tích và trực quan hóa dữ liệu, cung cấp thông tin chi tiết, hữu ích tới người dùng.

Power Query là gì?

Power Query cung cấp các chức năng thu thập & định hình dữ liệu nhanh chóng; dễ dàng. Có 4 giai đoạn để sử dụng Power Query:

• Kết nối: Nhập dữ liệu & tạo kết nối đến dữ liệu trong đám mây; trên một dịch vụ/ cục bộ.
• Chuyển đổi: Định hình dữ liệu để đáp ứng nhu cầu của bạn; trong khi nguồn ban đầu không thay đổi.
• Kết hợp: Định hình thêm dữ liệu bằng cách tích hợp dữ liệu từ nhiều nguồn; để có được dạng xem duy nhất vào dữ liệu.
• Tải: Hoàn tất truy vấn của bạn & lưu nó vào một trang tính/ Mô hình Dữ liệu.

Power Query trong Excel

Công cụ dữ liệu mạnh nhất của Excel & Power BI chính là Power Query. Nó giúp bạn tự động hóa quá trình nhập; chuyển đổi & xóa dữ liệu. Từ đó; tiết kiệm thời gian cho công việc của bạn. Trước đây; nó chỉ là một công cụ Add-in trong Office 2010 & Office 2013. Tới Office 2016; Power Query được tích hợp trong Excel dưới tên Get & Transform.

Cách cài đặt Power Query Excel

Bước 1: Kiểm tra version của Excel bạn đang dùng

Phiên bản Excel 2010: Chọn File > Help

Phiên bản Excel 2013 & 2016: Chọn File > Account > About Excel

Bước 2: Cài đặt Power Query

Phiên bản Excel 2010 & 2013

1. Đóng tất cả các File Excel bạn đang sử dụng

2. Vào link sau: https://bitly.com.vn/hjsnt1 . Chọn Tiếng Việt < Download 

3. Chọn phiên bản 32-bit hoặc 64-bit tương ứng với version Excel bạn đang dùng > Next

4. Download xong > Cài đặt

5. Chọn Next để tiến hành cài đặt

6. Sau khi hoàn thành; giao diện Excel của bạn sẽ có thêm tab Power Query

Phiên bản Excel 2016

Power Query đã được tích hợp sẵn trong phiên bản Excel 2016 với cái tên: Get & Transform (nằm trong tab Data)

Kết nối

Bạn có thể sử dụng Power Query để nhập vào một nguồn dữ liệu duy nhất. Chẳng hạn như một sổ làm việc Excel; các dịch vụ nằm rải rác trên nền tảng điện toán đám mây; hoặc nhiều CSDL; nguồn cấp dữ liệu. Sau khi nhập, bạn có thể làm mới dữ liệu bằng cách bổ sung; thay đổi & xóa từ nguồn dữ liệu bên ngoài. 

Chuyển đổi

Chuyển đổi dữ liệu có nghĩa là: sửa đổi nó theo một cách nào đó để đáp ứng các yêu cầu phân tích dữ liệu của bạn. Ví dụ: bạn có thể loại bỏ cột; thay đổi kiểu dữ liệu/ lọc hàng.

Power Query sử dụng một cửa sổ chuyên dụng gọi là: Trình soạn thảo Power Query để hỗ trợ & hiển thị các chuyển đổi dữ liệu. 

Kết hợp

Bạn có thể kết hợp nhiều truy vấn trong sổ làm Excel của mình bằng cách sắp thêm hoặc phối các truy vấn đó. Phép toán Nối & Phối được thực hiện trên bất kỳ truy vấn nào có hình dạng bảng; độc lập với nguồn dữ liệu.

• Chắp thêm: Thao tác nối tạo truy vấn mới có chứa tất cả các hàng từ truy vấn đầu tiên;  theo sau là tất cả hàng từ truy vấn thứ 2. Bạn có thể thực hiện 2 loại thao tác chắp thêm:
• Chắp thêm trung gian: Tạo truy vấn mới cho từng thao tác thêm.
• Chắp thêm nội tuyến: Nối dữ liệu vào truy vấn hiện có cho tới một kết quả cuối cùng.

• Phối: Một thao tác phối sẽ tạo ra một truy vấn mới từ 2 truy vấn hiện có. Truy vấn này chứa tất cả các cột từ bảng chính; với một cột được dùng làm nối kết dẫn đến bảng liên quan.

Tải

Có 2 cách chính để tải truy vấn vào sổ làm việc:

• Từ Trình soạn thảo Power Query: tab Nhà > Đóng > Đóng & Tải.
• Từ ngăn Excel truy vấn Sổ làm việc Mới >Truy vấn & Kết nối > Click chuột phải vào 1 truy vấn > Tải Đến.

Bạn cũng có thể sửa đổi tùy chọn tải như sau: Sử dụng hộp thoại Tùy chọn Truy vấn ( Tệp > Tùy chọn và thiết đặt  > Tùy chọn Truy vấn). Đây là cách chọn vị trí bạn muốn tải dữ liệu trong trang tính.

Power Query trong Power BI

Với Power BI Desktop; bạn có thể kết nối với thế giới dữ liệu; tạo những báo cáo hấp dẫn. Đồng thời chia sẻ báo cáo & phối hợp trong công việc.

Power BI có tổng cộng 2 màn hình chính:

• Màn hình Power Pivot: Màn hình trang chủ của Power BI Desktop. Có 3 chế độ xem: Báo cáo (Report); Dữ liệu (Data) & Mô hình dữ liệu (Data Model).

• Màn hình Power Query Editor: Nơi trích xuất dữ liệu & làm sạch dữ liệu. Sau đó mới tải dữ liệu vào màn hình Power Pivot.

Power Query Editor

Cách truy cập Power Query Editor: Trang chủ (Home) > Chỉnh sửa Truy vấn (Edit Queries)

Khi không có kết nối dữ liệu; Power Query Editor xuất hiện dưới dạng ngăn trống – sẵn sàng cho dữ liệu.

Khi một truy vấn (Query) được tạo ra; chế độ xem Power Query Editor sẽ hiển thị các thông tin của câu truy vấn đó. Nếu kết nối với nguồn dữ liệu Web sau; Power Query Editor sẽ tải thông tin về dữ liệu. Sau đó định hình dữ liệu đó. Ví dụ dưới đây; chúng ta có một câu truy vấn Table0. Quan sát giao diện Power Query sau khi kết nối dữ liệu:

• Trong ruy-băng thanh công cụ; nhiều nút hiện đang hoạt động để tương tác với dữ liệu trong truy vấn.
• Trong ngăn bên trái; các truy vấn được liệt kê & có sẵn để lựa chọn; xem; định hình.
• Trong ngăn trung tâm; dữ liệu từ truy vấn đã chọn được hiển thị; có sẵn để định hình.
• Ngăn Cài đặt Truy vấn (Query Setting) xuất hiện; liệt kê các thuộc tính của truy vấn; các bước được áp dụng.

Ruy băng truy vấn

Ruy-băng trong Power Query Editor bao gồm 4 tab: Trang chủ (Home), Chuyển đổi (Transform), Thêm cột (Add Column) & Chế độ xem (View).

Tab Trang chủ (Home) chứa các tác vụ truy vấn phổ biến

Để kết nối với dữ liệu & bắt đầu quá trình xây dựng truy vấn: Chọn Nguồn mới (New Source). Một menu xuất hiện; cung cấp các nguồn dữ liệu phổ biến nhất.

Tab Chuyển đổi (Transform)

Cung cấp quyền truy cập vào các tác vụ chuyển đổi dữ liệu phổ biến như: Thêm/ xóa cột; thay đổi kiểu dữ liệu; tách cột;…

Tab Thêm cột (Add Column)

Cung cấp các tác vụ bổ sung liên quan đến việc thêm cột; định dạng dữ liệu cột & thêm cột tùy chỉnh. Hình ảnh sau hiển thị tab Thêm cột (Add Column).

Tab Xem (View) trên ruy-băng

Được sử dụng để chuyển đổi; xem các ngăn/ cửa sổ nhất định có được hiển thị hay không. Hình ảnh sau đây hiển thị tab Xem (View).

Ngăn bên trái (Truy vấn)

Ngăn Truy vấn hiển thị tên truy vấn & số lượng truy vấn đang hoạt động. Khi bạn chọn một truy vấn từ ngăn bên trái; dữ liệu của nó được hiển thị trong ngăn trung tâm – nơi định hình và biến đổi dữ liệu. Hình ảnh sau đây hiển thị ngăn bên trái với một truy vấn.

Ngăn trung tâm (dữ liệu)

Trong ngăn Dữ liệu; dữ liệu từ truy vấn đã chọn được hiển thị. Nơi hoàn thành phần lớn công việc của dạng xem Truy vấn.

Hình sau đây cho thấy kết nối dữ liệu Web được thiết lập trước đó. Cột Sản phẩm (Product) được chọn; tiêu đề của nó được click chuột phải ( hiển thị các mục menu có sẵn ). Lưu ý: nhiều mục menu click chuột phải giống với các nút trong tab ruy-băng.

Khi chọn một mục menu click chuột phải (hoặc nút ruy-băng); truy vấn sẽ áp dụng bước cho dữ liệu. Các bước được ghi lại tuần tự trong ngăn Cài đặt Truy vấn (Query Settings).

Ngăn bên phải (cài đặt Truy vấn)

Ngăn Cài đặt Truy vấn (Query Settings): hiển thị tất cả các bước liên quan đến truy vấn. Ví dụ: Phần Các bước áp dụng (Applied Steps) của ngăn Cài đặt truy vấn (Query Settings) phản ánh từng bước trong câu truy vấn gồm 3 bước: lấy dữ liệu nguồn; di chuyển đến bảng dữ liệu cần lấy; đổi định dạng cột dữ liệu.

Khi các bước định hình bổ sung được áp dụng cho truy vấn; chúng sẽ được ghi lại trong phần Các bước được áp dụng (Applied Steps).

Điều quan trọng là dữ liệu nguồn không bị thay đổi. Thông tin bảng dữ liệu ở ngăn trung tâm cho phép quan sát kết quả chỉnh sửa dữ liệu sau các bước được áp dụng.

Trong ngăn Cài đặt Truy vấn (Query Settings), bạn có thể đổi tên; xóa;  sắp xếp thứ tự các bước. Để làm vậy, click chuột phải vào bước trong phần Các bước được áp dụng (Applied Steps) > chọn từ menu xuất hiện. Tất cả các bước truy vấn được thực hiện lần lượt theo thứ tự xuất hiện trong ngăn Các bước được áp dụng (Applied Steps).

Advanced Editor

Trình chỉnh sửa nâng cao (Advanced Editor) cho phép bạn xem phần code M mà Power Query Editor đang tạo với mỗi bước. Cho phép bạn tạo mã định hình của riêng mình. Để khởi chạy trình chỉnh sửa nâng cao: Xem (View) từ ruy-băng > Trình chỉnh sửa nâng cao (Advanced Editor). Một cửa sổ xuất hiện; hiển thị mã truy vấn hiện có.

Bạn có thể trực tiếp chỉnh sửa mã trong cửa sổ Trình chỉnh sửa nâng cao (Advanced Editor). Hoặc copy hoàn toàn đoạn code M trong Advanced Editor rồi đưa vào trong 1 truy vấn khác. Để đóng cửa sổ, chọn Xong/ Hủy.

Trên đây mình đã giới thiệu về Power Query; định nghĩa Power Query Excel; hướng dẫn sử dụng Power Query; tính năng & sự ưu việt của nó trong việc xử lý & chuẩn hóa dữ liệu.

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