Giới thiệu Power Query
Power Query là gì?
Power Query, còn được gọi là “Get & Transform” từ Excel 2016 và các phiên bản mới hơn, là một công cụ mạnh mẽ được tích hợp sẵn trong Excel. Đây là công cụ trích xuất, chuyển đổi và tải (ETL) dữ liệu, cho phép người dùng kết nối với nhiều nguồn dữ liệu khác nhau như tệp Excel, CSV, cơ sở dữ liệu, trang web. Qua đó, người dùng có thể làm sạch, chuyển đổi và định dạng lại dữ liệu theo yêu cầu.
Power Query không chỉ là một công cụ đơn giản để lấy và biến đổi dữ liệu. Nó giống như một chiếc chìa khóa vạn năng giúp bạn mở ra các cánh cửa dữ liệu từ tất cả các nguồn bạn cần. Từ một tệp Excel đơn giản cho đến việc kết nối với cơ sở dữ liệu phức tạp, Power Query giúp bạn tự động hóa các quy trình xử lý dữ liệu, tiết kiệm thời gian và giảm thiểu sai sót.
Ưu điểm của Power Query
Power Query mang lại nhiều ưu điểm vượt trội:
- Tự động hóa quy trình xử lý dữ liệu: Các bước biến đổi dữ liệu được ghi lại và có thể tự động chạy lại khi cần cập nhật dữ liệu, giúp bạn tiết kiệm thời gian và công sức.
- Tiết kiệm thời gian: Tự động hóa các tác vụ xử lý dữ liệu mang lại sự hiệu quả trong công việc, giảm thiểu thời gian phải làm việc thủ công.
- Giảm thiểu sai sót: Việc tự động hóa không chỉ giúp quản lý khối lượng lớn dữ liệu mà còn giảm thiểu sai sót do thao tác thủ công gây ra.
- Kết nối đa dạng: Power Query hỗ trợ kết nối với nhiều nguồn dữ liệu, từ tệp văn bản cho đến cơ sở dữ liệu trực tuyến, cho phép bạn tổng hợp dữ liệu từ nhiều nơi khác nhau.
Các ứng dụng của Power Query
Power Query có thể được sử dụng trong nhiều ứng dụng khác nhau trong cuộc sống công việc hàng ngày:
- Trích xuất dữ liệu từ các nguồn khác nhau như tệp Excel, CSV, cơ sở dữ liệu, trang web, v.v.
- Làm sạch và chuyển đổi dữ liệu, ví dụ như loại bỏ khoảng trắng, chia cột, thay đổi định dạng ngày tháng, v.v.
- Kết hợp dữ liệu từ nhiều nguồn khác nhau vào một bảng dữ liệu thống nhất, giảm thiểu việc phải quản lý nhiều tệp hoặc bảng dữ liệu độc lập.
- Tự động hóa quy trình cập nhật dữ liệu bằng cách lên lịch làm mới các truy vấn Power Query.
- Phân tích dữ liệu sâu hơn bằng cách kết hợp Power Query với các công cụ phân tích khác như Pivot Table và Power BI, mở ra khả năng phân tích dữ liệu đa chiều và toàn diện hơn.
Tóm lại, Power Query là công cụ không thể thiếu giúp quản lý và phân tích dữ liệu lớn trong Excel một cách hiệu quả, tiết kiệm thời gian và giảm thiểu sai sót.
Cài đặt và khởi động Power Query
Cài đặt Power Query
Power Query được tích hợp sẵn trong các phiên bản Office 2016 và Office 365, nhưng người dùng phiên bản thấp hơn cũng có thể cài đặt thêm Power Query:
- Người dùng Office 2010 và 2013 có thể tải Add-in Power Query từ trang web của Microsoft.
- Link tải Power Query: Microsoft Download Center
Khởi động Power Query
Khởi động Power Query rất đơn giản trong Excel:
- Mở Excel và vào tab Data.
- Trong nhóm Get & Transform Data, chọn Get Data hoặc From Other Sources.
Cửa sổ Trình soạn thảo Power Query sẽ hiện ra, cho phép bạn thực hiện các thao tác kết nối, biến đổi và kết hợp dữ liệu. Đây là môi trường trực quan giúp quản lý mọi khía cạnh của dữ liệu bạn cần xử lý.
Kết nối dữ liệu
Kết nối với tệp Excel
Kết nối với tệp Excel là một thao tác cơ bản và thường gặp trong Power Query:
- Trong cửa sổ Trình soạn thảo Power Query, chọn From File > From Excel.
- Chọn tệp Excel mà bạn muốn kết nối. Power Query sẽ hiển thị các bảng dữ liệu có trong tệp, cho phép bạn chọn bảng cần kết nối.
- Bạn có thể kết nối với nhiều tệp Excel khác nhau và kết hợp dữ liệu từ các nguồn này lại với nhau.
Kết nối với tệp CSV, TSV
Kết nối với tệp CSV hoặc TSV có thể thực hiện qua các bước sau:
- Trong Power Query, chọn “From File > From Text/CSV”.
- Chọn đường dẫn tệp và nhập thông tin xác thực (nếu cần).
- Chọn “Transform Data” để bắt đầu chuyển đổi dữ liệu trong Power Query Editor.
Kết nối với tệp TSV có chút khác biệt:
- Mở tệp Excel trống và vào Power Query, chọn “From Text/CSV”.
- Chọn “All Files” (thay vì mặc định là “Text Files”) để hiển thị các tệp .tsv.
Kết nối với trang web
Để kết nối với dữ liệu từ trang web:
- Chọn “Web page” hoặc “Web API” từ hộp thoại Get Data.
- Sử dụng một on-premises data gateway vì lý do bảo mật.
- Tuỳ chọn phương thức xác thực: Anonymous, Windows, Basic, hay Organizational Account.
Kết nối với cơ sở dữ liệu
Power Query hỗ trợ kết nối với nhiều loại cơ sở dữ liệu khác nhau:
- Sử dụng truy vấn cơ sở dữ liệu bản địa.
- Sử dụng Excel for the web, chức năng này tích hợp Power Query để cung cấp khả năng nhập, làm mới và xác thực nguồn dữ liệu. Ví dụ như cơ sở dữ liệu SQL, Access,…
Kết nối với các nguồn dữ liệu khác
Ngoài các kết nối phổ biến ở trên, Power Query cũng hỗ trợ kết nối với các nguồn dữ liệu khác:
- Web services, XML, JSON,…
- API và các dịch vụ trực tuyến khác.
Danh sách các nguồn dữ liệu mà Power Query hỗ trợ rất đa dạng và ngày càng được mở rộng, mang lại sự linh hoạt cho người dùng trong viêc quản lý dữ liệu.
Định hình dữ liệu
Loại bỏ cột
Trong Power Query, bạn có thể loại bỏ các cột không cần thiết bằng các bước sau:
- Sử dụng tính năng “Choose Columns” trong tab Transform.
- Chọn và giữ lại các cột mong muốn, giúp làm sạch và tối ưu hóa dữ liệu của bạn.
Thay đổi kiểu dữ liệu
Thay đổi kiểu dữ liệu của các cột trong Power Query rất quan trọng:
- Power Query tự động phát hiện và gán kiểu dữ liệu cho các cột khi nhập.
- Bạn có thể thay đổi kiểu dữ liệu bằng tính năng “Change Type” trong tab Transform.
Điều này giúp đảm bảo dữ liệu được định dạng đúng, hỗ trợ các hàm và tính toán trong Excel hiệu quả hơn.
Lọc hàng
Lọc dữ liệu dễ dàng trong Power Query:
- Sử dụng biểu tượng bộ lọc trong tiêu đề cột.
- Chọn hoặc loại bỏ các hàng dựa trên giá trị mong muốn.
Sắp xếp dữ liệu
Sắp xếp dữ liệu trong Power Query:
- Sắp xếp tăng dần (từ A đến Z) hoặc ngược lại với Sắp xếp giảm dần (từ Z đến A).
- Sắp xếp nhiều cột một cách linh hoạt, tuỳ chỉnh theo thứ tự chọn lựa cột.
Phối hợp bảng
Power Query cung cấp khả năng kết hợp dữ liệu từ nhiều nguồn khác nhau vào một bảng duy nhất:
- Kết hợp các cột từ nhiều bảng tính vào một bảng duy nhất.
- Xử lý các bảng lồng nhau một cách hiệu quả.
Tạo kiểu dữ liệu riêng
Tạo kiểu dữ liệu riêng trong Power Query:
- Nhập dữ liệu từ nguồn bạn muốn.
- Chọn cột cần thiết và sắp xếp theo thứ tự mong muốn.
- Sử dụng “Transform > Create Data Type” để mở hộp thoại “Create Data Type”.
- Đặt tên cho kiểu dữ liệu mới và chọn cột hiển thị.
- Chỉnh sửa nâng cao nếu cần và chọn “OK”.
Điều này giúp bạn tạo ra các kiểu dữ liệu lồng nhau phù hợp với nhu cầu phân tích.
Nâng cao Power Query
Sử dụng ngôn ngữ M
Ngôn ngữ M trong Power Query là công cụ mạnh mẽ giúp thực hiện các thao tác phức tạp:
- Hỗ trợ 18 kiểu dữ liệu nguyên thủy khác nhau.
- Các hàm và toán tử giúp thao tác dữ liệu linh hoạt.
- Tạo các truy vấn phức tạp bằng cách kết hợp các hàm lại với nhau.
Ví dụ: Tạo một cột nhân đôi giá trị của cột khác: = Table.AddColumn(#"Changed Type", "DoubleNumbers", each [Numbers] * 2, Int64.Type)
Lấy dữ liệu từ web nhiều trang, nhiều ngày
Lấy dữ liệu từ nhiều trang web:
- Sử dụng “From Web” để kết nối và lấy dữ liệu.
- Sử dụng các hàm như
Web.Contents()vàWeb.BrowserContents()để lấy nội dung. - Sử dụng hàm
List.Generate()vàList.Dates()để lặp qua các trang và ngày khác nhau.
Ứng dụng Power Query trong các trường hợp đặc biệt
Power Query được ứng dụng trong nhiều trường hợp đặc biệt:
- Xử lý dữ liệu lớn: Khả năng xử lý dữ liệu với khối lượng lớn rất hiệu quả.
- Tích hợp dữ liệu từ nhiều nguồn: Hỗ trợ kết nối và kết hợp dữ liệu từ nhiều nguồn.
- Tự động hóa quy trình: Các bước xử lý dữ liệu có thể lưu lại và tái sử dụng.
Tải dữ liệu và làm mới
Tải dữ liệu vào trang tính
Tải dữ liệu vào trang tính trong Excel:
- Sử dụng các kết nối dữ liệu có sẵn.
- Tạo các truy vấn Power Query để lấy và biến đổi dữ liệu trước khi đưa vào trang tính.
Tải dữ liệu vào mô hình dữ liệu
Tải dữ liệu vào mô hình dữ liệu trong Excel:
- Power Query có thể lấy dữ liệu từ nhiều nguồn và đưa vào mô hình dữ liệu.
- Mô hình dữ liệu trong Excel giúp thực hiện các phân tích và báo cáo phức tạp.
Làm mới dữ liệu
Làm mới dữ liệu trong Excel:
- Trong Power Query Editor, chọn “Home > Close & Load > Close & Load to” để cập nhật dữ liệu.
- Hoặc tìm truy vấn trong tab “Queries & Connections” ở Excel, nhấp chuột phải và chọn “Refresh”.
Tùy chọn tải dữ liệu
Các tùy chọn tải dữ liệu:
- Tải dữ liệu vào trang tính mới hoặc vào Data Model.
- Tùy chỉnh cài đặt mặc định trong “Query Options” của Power Query.
Các lưu ý
Yêu cầu .NET Framework
Power Query yêu cầu .NET Framework để hoạt động:
- Khi bắt đầu kết nối với nguồn dữ liệu, Power Query sẽ tải và khởi chạy .NET Framework.
- Điều này có thể mất vài phút tùy thuộc vào cấu hình của máy tính.
Yêu cầu Microsoft Edge WebView2
Microsoft Edge WebView2 là một yêu cầu quan trọng:
- WebView2 Runtime được cài đặt tự động trên các thiết bị chạy Windows với Microsoft 365 Apps phiên bản 2101 trở lên.
- Nếu thiết bị không có WebView2 Runtime, các tính năng của Office dựa trên WebView2 sẽ không khả dụng.
Hỗ trợ trên các phiên bản Excel
Power Query hỗ trợ trên các phiên bản Excel:
- Excel 2016 và Office 365 tích hợp sẵn Power Query.
- Các phiên bản thấp hơn như Excel 2010 và 2013 cần tải thêm Add-in.
Tóm lại, Power Query trong Excel là công cụ mạnh mẽ giúp quản lý và phân tích dữ liệu lớn một cách hiệu quả và chính xác. Với các tính năng tự động hóa, khả năng kết nối đa dạng và định hình dữ liệu linh hoạt, Power Query là lựa chọn hàng đầu cho việc xử lý dữ liệu phức tạp trong Excel. Điều quan trọng là bạn cần nắm bắt và khai thác tối đa các tính năng của Power Query để gia tăng hiệu quả công việc và giảm thiểu sai sót trong quá trình xử lý dữ liệu.
