2.3 POWER QUERY - Power query là gì ?
Power Query là công cụ giúp tạo ra 1 quy trình tự động cho việc tổ chức, xử lý, tái cấu
trúc lại bảng dữ liệu.
Với Power Query (được gọi là & dữ liệu biến đổi trong các phiên bản Excel trước đó),
bạn có thể nhập hoặc kết nối với dữ liệu ngoài, sau đó định hình dữ liệu đó như loại bỏ
một cột, thay đổi kiểu dữ liệu hoặc phối bảng, theo những cách đáp ứng nhu cầu của
bạn. Sau đó, bạn có thể tải truy vấn của mình vào Excel để tạo biểu đồ và báo cáo.
Định kỳ, bạn có thể làm mới dữ liệu để làm cho nó được Cập Nhật.
Từ phiên bản Office 2010, 2013 thì phải cài đặt Power Query dưới dạng Add-in ( tiện
ích mở rộng ). Nhưng từ phiên bản Office 2016, 2019, 365 Power Query đã được tích
hợp sẵn trong Excel : Nhóm Get & Transform Data trong thẻ Data
- Nguyên tắc hoạt động :
 Extract: kết nối và trích xuất dữ liệu từ nhiều nguồn khác nhau như: web
page, file Exce, file CSV, text file, SQL server database, Microsoft Azure
SQL Database, MySQL database, Sharepoint list,…
 Transform: các bước chuyển hóa dữ liệu nhằm đáp ứng nhu cầu phân tích
và lập báo cáo. Quy trình chuyển hóa dữ liệu này có thể bao gồm một
hoặc nhiều bước và các bước này được ghi lại theo đúng trình tự, cho
phép người dùng dễ dàng chỉnh sửa thêm hoặc xóa đi nhưng thao tác không cần thiết.
 Load: dữ liệu sau khi được chuyển hóa thành dữ liệu chuẩn sẽ được tải về
dưới dạng table trong Excel hoặc Load to PowerPivot.
Quy trình các bước thực hiện trong Power Query đều được ghi lại và chỉ cần
thực hiện 1 lần, sau đó khi có thêm dữ liệu mới người dùng chỉ cần refresh để
thực hiện tất cả các thao tác.
- Kết nối dữ liệu :
Sử dụng Power Query để kết nối tới các nguồn dữ liệu từ các tệp tin đơn lẻ đến các cơ
sở dữ liệu,… Power Query thuận tiện trong việc làm việc với dữ liệu từ nhiều nguồn
khác nhau từ trong hệ thống đến ngoài hệ thống mà không cần đồng nhất tệp tin.
Để có thể kết nối đến các nguồn dữ liệu ta cần thực hiện các bước sau :
Data > From File > From Workbook > Chọn File dữ liệu
Sau khi thực hiện các bước trên, cửa sổ Navigator sẽ hiện lên và chúng ta sẽ chọn ra
sheet dữ liệu cần thực hiện. Các file dữ liệu Excel ở đây sẽ định dạng theo kiểu bảng
dữ liệu. Để chọn nhiều ta sẽ click vào ô Select multiple items. Chúng ta sẽ lựa chọn 2 option:
 Load : Load toàn bộ dữ liệu ra Excel dưới dạng bảng dữ liệu
 Transform Data : Sử dụng dữ liệu vừa kết nối và xử liệu trên giao diện Power Query Editor
Sau khi kết nối tới Power Query Editor giao diện sẽ hiện ra như bảng sau :
- Cách chuẩn hóa dữ liệu bằng Power Query
Bước 1: Tải dữ liệu vào power query, chọn Data và sau đó chọn From Table / Range.
( Dữ liệu đã được định dạng table )
Bước 2: Tại cửa sổ Power Query, chọn tab Add Column, sau đó click chọn Index
Column, sau bước này cột dữ liệu số sẽ được thêm vào.
Bước 3: Sau đó, chúng ta sẽ chuyển đổi cột dữ liệu số này thành số hiệu duy nhất cho
mỗi record. Để thực hiện điều này ta phải chọn Transform tab, Standard, và chọn Integer-Divide.
Sau đó, điền giá trị vào cột Value. Đây sẽ 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
Sau bước này, cột giá trị mới sẽ hiển thị số hiệu định danh mỗi record như bảng dưới.
Bước 4: Chon cột column đầu tiên, sau đó vào transform tab và click chọn Pivot Column
Tại bảng Pivot Column, chọn Column2 cho Value Column và sau đó tại Advanced
options chọn Don’t Aggregate. Sau các bước trên sẽ cho ra kết quả như bảng sau :
Sau đó, vào Home > Close & Load > Close & Load to…Sau bước này file sẽ hiện ra
bảng Import Data sẽ cho phép bạn trích xuất dữ liệu vừa chuyển đổi dưới dạng mà bạn mong muốn.
- Xây dựng model cho dữ liệu Bước 1 :
 Đối với Excel 2016 và 365, vào Data trên thanh công cụ, click vào Lấy dữ liệu
& Chuyển đổi dữ liệu, sau đó lấy dữ liệu để nhập dữ liệu từ bất kỳ số lượng
nguồn dữ liệu bên ngoài nào, chẳng hạn như tệp văn bản, sổ làm việc Excel,
website, Microsoft Access, SQL Server hoặc một cơ sở dữ liệu quan hệ khác có
chứa nhiều bảng liên quan.
 Đối với Excel 2010 và 2013, vào Power Query > Lấy Dữ liệu Ngoài , rồi chọn
nguồn dữ liệu của bạn.
Bước 2: Chọn bảng trong Excel. Bật tính năng chọn nhiều bảng nếu muốn chọn nhiều
bảng trong cùng một nguồn dữ liệu. Sau đó, Excel sẽ tự động tạo mô hình dữ liệu.
- Xây dựng các hàm tính toán
 Bước 1: Vào Power Query, chọn Data trên thanh công cụ, click vào Get Data
chọn Lấy dữ liệu khác rồi chọn Blank Query
 Bước 2: Nhập hàm =text.Proper (“text,value”) rồi nhấn Enter
 Bước 3: Kết quả sẽ hiện lên trên ngăn kết quả của Power Query
 Bước 4: Chọn Đóng & Tải để xem kết quả trong trang tinh Excel.
- Tạo báo cáo với Power Query