Trong bài viết này, KPIM sẽ tìm hiểu về các tính năng, công cụ và ứng dụng của Power Query. Chúng ta sẽ khám phá cách công cụ này hỗ trợ việc kết nối và nhập dữ liệu từ nhiều nguồn, xử lý và biến đổi dữ liệu, ghép nối và kết hợp dữ liệu, tạo bước công việc và tự động hóa quy trình xử lý dữ liệu.
Định nghĩa về Power Query
Power Query là một công cụ quyền lực được tích hợp trong Microsoft Excel và Power BI để truy vấn, biến đổi và kết hợp dữ liệu từ nhiều nguồn khác nhau. Nó cho phép người dùng tạo ra các bước xử lý dữ liệu tự động và lặp lại được, giúp tiết kiệm thời gian và nỗ lực trong việc chuẩn bị và biến đổi dữ liệu.
Power Query cũng cung cấp các chức năng biến đổi dữ liệu mạnh mẽ như thêm cột tính toán, xóa cột không cần thiết, chuyển đổi kiểu dữ liệu, tách chuỗi, gộp bảng, và áp dụng các quy tắc xử lý dữ liệu phức tạp. Người dùng có thể xây dựng một loạt các bước biến đổi dữ liệu để làm sạch và chuẩn hóa dữ liệu trước khi nạp vào Excel hoặc Power BI để phân tích và hiển thị.
Vai trò và lợi ích của Power Query trong xử lý dữ liệu
Power Query đóng vai trò quan trọng trong xử lý dữ liệu và mang lại nhiều lợi ích cho người dùng. Dưới đây là một số vai trò và lợi ích chính của công cụ này:
- Truy xuất dữ liệu từ nhiều nguồn: Cho phép người dùng kết nối và truy vấn dữ liệu từ nhiều nguồn khác nhau như cơ sở dữ liệu, tệp Excel, trang web, nguồn dữ liệu OData và nhiều nguồn dữ liệu khác. Điều này giúp người dùng tổng hợp dữ liệu từ nhiều nguồn và tạo ra báo cáo hoặc phân tích toàn diện hơn.
- Biến đổi và làm sạch dữ liệu: Cung cấp các công cụ mạnh mẽ để biến đổi và làm sạch dữ liệu. Người dùng có thể áp dụng các bước biến đổi như lọc dữ liệu, sắp xếp, nhóm, ghép nối, gộp bảng và áp dụng các quy tắc xử lý dữ liệu phức tạp. Điều này giúp người dùng chuẩn hóa và chuẩn hóa dữ liệu trước khi nạp vào công cụ phân tích như Excel hoặc Power BI.
- Tự động hóa quy trình xử lý dữ liệu: Cho phép người dùng xây dựng các bước biến đổi dữ liệu một lần và áp dụng chúng cho các bộ dữ liệu mới mà không cần thay đổi cấu trúc của bảng. Điều này giúp tiết kiệm thời gian và đảm bảo tính nhất quán trong việc xử lý dữ liệu.
- Cập nhật dữ liệu tự động: Power Query cho phép người dùng lập kế hoạch và cập nhật tự động các bước xử lý dữ liệu. Người dùng có thể thiết lập lịch trình cập nhật để đồng bộ hóa dữ liệu mới và tự động áp dụng các bước biến đổi dữ liệu đã xây dựng. Điều này giúp đảm bảo dữ liệu luôn được cập nhật và sẵn sàng cho phân tích và báo cáo.
- Tích hợp với các công cụ phân tích: Tích hợp tốt với các công cụ phân tích như Excel và Power BI. Sau khi dữ liệu đã được biến đổi bằng Power Query , người dùng có thể sử dụng các tính năng phân tích và hiển thị dữ liệu một cách dễ dàng và linh hoạt.
Các tính năng chính của Power Query
Power Query cung cấp một loạt tính năng mạnh mẽ để xử lý và biến đổi dữ liệu. Dưới đây là mô tả về các tính năng chính của công cụ này:
Kết nối và nhập dữ liệu
- Cung cấp khả năng kết nối và nhập dữ liệu từ nhiều nguồn khác nhau như cơ sở dữ liệu, tệp tin Excel, trang web, nguồn dữ liệu OData, nguồn dữ liệu SharePoint, hệ thống CRM và nhiều nguồn dữ liệu khác.
- Người dùng có thể chọn loại kết nối phù hợp và cung cấp thông tin xác thực để truy cập vào nguồn dữ liệu.
Xử lý và biến đổi dữ liệu
- Người dùng có thể thực hiện các bước biến đổi như lọc dữ liệu, sắp xếp, nhóm, thêm cột tính toán, thay đổi kiểu dữ liệu, tách chuỗi, chuyển đổi định dạng và thực hiện nhiều hành động xử lý dữ liệu khác.
- Các thao tác xử lý dữ liệu có thể được thực hiện thông qua giao diện người dùng trực quan hoặc bằng cách viết các công thức ngôn ngữ M (M formula).
Ghép nối và kết hợp dữ liệu
- Cho phép ghép nối (join) và kết hợp (merge) dữ liệu từ các nguồn khác nhau. Người dùng có thể ghép nối các bảng dữ liệu dựa trên các cột chung hoặc kết hợp dữ liệu từ các nguồn khác nhau để tạo ra báo cáo hoặc phân tích toàn diện.
- Cung cấp các công cụ linh hoạt để chỉnh sửa cách ghép nối và kết hợp dữ liệu, bao gồm chọn loại ghép nối (inner join, left join, right join, full outer join), chỉ định cột kết nối và xử lý các trường hợp dữ liệu trùng lặp.
Tạo bước công việc và ghi lại quy trình xử lý dữ liệu
- Power Query cho phép người dùng tạo bước công việc (query) để thực hiện các bước xử lý dữ liệu. Các bước công việc được tạo ra có thể được sử dụng lại và áp dụng cho các tập dữ liệu mới.
- Người dùng có thể ghi lại quy trình xử lý dữ liệu thành mã nguồn M hoặc sử dụng chế độ xem Advanced Editor để chỉnh sửa mã nguồn M trực tiếp.
- Các bước công việc cũng có thể được tự động cập nhật để đồng bộ hóa dữ liệu mới và áp dụng các bước biến đổi dữ liệu đã xây dựng.
Giao diện và công cụ của Power Query
Power Query cung cấp một giao diện trực quan và một loạt công cụ mạnh mẽ để xử lý và biến đổi dữ liệu. Giao diện công cụ này được thiết kế để giúp người dùng dễ dàng thao tác và tùy chỉnh quy trình xử lý dữ liệu một cách linh hoạt.
Giao diện Power Query
Giao diện Power Query cung cấp một môi trường trực quan và dễ sử dụng để xử lý dữ liệu. Dưới đây là mô tả về các thành phần chính của giao diện công cụ này:
- Power Query Editor: Đây là nơi chính để xử lý và biến đổi dữ liệu. Hiển thị các bước công việc (query) được tạo và cho phép người dùng thực hiện các thao tác trên dữ liệu như xem, chỉnh sửa và áp dụng các bước biến đổi.
- Bảng dữ liệu (Table Preview): Power Query Editor hiển thị một bảng dữ liệu trước (table preview) để người dùng có thể xem trực quan dữ liệu đang được xử lý. Bảng dữ liệu này cung cấp một cái nhìn trước về dữ liệu và cho phép người dùng áp dụng các bước biến đổi dữ liệu.
- Bảng công thức (Formula Bar): Bảng công thức hiển thị công thức (M Formula) tương ứng với bước công việc (query) đang được chọn trong Power Query Editor. Người dùng có thể xem và chỉnh sửa công thức để tùy chỉnh xử lý dữ liệu.
- Cửa sổ thuộc tính (Properties Window): Cửa sổ thuộc tính hiển thị thông tin chi tiết về bước công việc (query) và các tùy chọn cấu hình liên quan. Người dùng có thể tùy chỉnh các thiết lập và cấu hình của bước công việc thông qua cửa sổ này.
- Thanh công cụ (Toolbar): Thanh công cụ cung cấp các nút lệnh và biểu tượng để thực hiện các thao tác trong Power Query Editor. Các chức năng như kết nối nguồn dữ liệu, biến đổi dữ liệu, tải dữ liệu và cập nhật dữ liệu có thể được thực hiện thông qua thanh công cụ.
- Cửa sổ xem trước (Preview Window): Cửa sổ xem trước hiển thị kết quả xem trước của các bước biến đổi dữ liệu. Người dùng có thể xem dữ liệu sau khi áp dụng các bước biến đổi và kiểm tra kết quả trước khi tải dữ liệu vào ứng dụng mục tiêu.
Công cụ và chức năng trong Power Query
Power Query cung cấp một loạt công cụ và chức năng để xử lý và biến đổi dữ liệu. Dưới đây là một số công cụ và chức năng quan trọng:
- Filter and Sort: Cho phép người dùng lọc và sắp xếp dữ liệu dựa trên các điều kiện được chỉ định.
- Group By: Cho phép nhóm dữ liệu và tính toán các giá trị thống kê như tổng, trung bình, max, min, và nhiều hơn nữa.
- Append và Merge: Cho phép người dùng ghép nối (append) nhiều bảng dữ liệu thành một bảng duy nhất hoặc kết hợp (merge) các bảng dữ liệu dựa trên cột chung.
- Pivot và Unpivot: Cho phép người dùng chuyển đổi dữ liệu từ dạng hàng sang dạng cột (pivot) hoặc từ dạng cột sang dạng hàng (unpivot) để phù hợp với yêu cầu phân tích.
- Add Columns và Remove Columns: Cung cấp khả năng thêm cột tính toán mới dựa trên các công thức và loại bỏ các cột không cần thiết trong công cụ này.
M là gì?
M là tên không chính thức của ngôn ngữ Power Query Formula và M là viết tắt của Data Mashup. M là một ngôn ngữ chức năng, và điều quan trọng là phải biết các chức năng của nó. Tuy nhiên, học cấu trúc và cú pháp là cấp độ đơn giản nhất của việc học ngôn ngữ đó. Trong bài đăng này, tôi sẽ giải thích cú pháp của M.
Trước khi học M, tôi muốn nhấn mạnh lại cho bạn biết rằng: M mạnh và làm được hơn nhiều so với các công cụ trên giao diện của Power Query.
Chính xác! Giao diện thanh công cụ của Power Query thay đổi hàng tháng và mỗi tháng có chức năng mới trên giao diện đồ họa này. Nhưng thực tế là tất cả các chức năng này đã có trong ngôn ngữ M trong nhiều năm! Nếu bạn biết ngôn ngữ, bạn có thể dễ dàng sử dụng chúng, thay vì chờ đợi tùy chọn giao diện đồ họa cho nó.
(Cũng phải thôi khi đội ngũ Power BI không chỉ phải mất công đơn giản hóa thanh công cụ và lựa chọn tính năng nào phù hợp để đưa lên)
Cú pháp của M
Cú pháp của ngôn ngữ này rất đơn giản, nó luôn có hai programming blocks:
- Bắt đầu bằng khối biểu thức LET
- Kết thúc bằng khối biểu thức IN
Cấu trúc cú pháp M
Dưới đây là cú pháp M đơn giản nhất:
let
xin_chao = "hello world"
in
xin_chao
Cú pháp của M
let và in là các reserved words (tức là các từ đã được xử dụng cho lệnh và bạn không thể sử dụng các biến có tên tương tự). Ngoài ra, điều đầu tiên và quan trọng nhất bạn cần biết: M có Phân biệt chữ hoa chữ thường. Có sự khác biệt giữa x và X.
Vậy hai programming blocks này là gì:
LET: định nghĩa của tất cả các biếnIN: đầu ra. mọi thứ bạn đặt trong khối này sẽ là kết quả của truy vấn của bạn.
Vì vậy, về cơ bản, truy vấn dưới đây có nghĩa là xác định một biến có tên là xin_chao, gán giá trị “hello world” cho nó và hiển thị nó dưới dạng kết quả. Vì vậy truy vấn sẽ trả về hello world như dưới đây.
Để tạo câu lệnh như trên, bạn cần phải mở Power BI Desktop. Đi tới Get Data, bắt đầu với New Blank Query.
Sau đó trong tab View ở giao diện Power Query, chọn Advanced Editor. Bạn cần đảm bảo rằng khi viết script, let và in phải được viết thường, không in hoa, cũng như tên biến của bạn phải giống nhau trong cả hai phần let và in.
Như bạn thấy, không cần xác định kiểu dữ liệu cho biến; nó sẽ tự động được gán khi nhiệm vụ đầu tiên xảy ra. Nếu bạn chỉ định một giá trị text thì biến sẽ tự động là một kiểu dữ liệu text nếu là số nó sẽ tự nhận dạng là số.
Sử dụng , để kết một dòng lệnh
Các dòng mã trong M vẫn tiếp tục nếu bạn không đặt ký tự cuối dòng. Giả sử tôi tạo ra một đoạn script mới như sau rồi bổ sung thêm một đoạn dưới dòng đó: + 1.
Sử dụng , để kết một dòng lệnh
Tên biến
Tên của các biến có thể là một từ, như Source, hoặc nó có thể có khoảng trống trong đó.
Trong trường hợp bạn có khoảng trống trong tên biến, thì bạn cần đặt tên bên trong dấu ngoặc kép ("), và đặt một thẻ bắt đầu bằng # ở đầu nó. Một cái gì đó tương tự như: #"Tên biến này có khoảng trống".
Tên biến có thể có ký tự đặc biệt, như bạn có thể thấy bên dưới biến có tất cả các loại ký tự trong đó và vẫn chạy tốt, chỉ cần ở trong dấu "".
Dấu ngoặc kép (") là ký tự thoát. bạn có thể sử dụng nó để xác định các biến có tên có dấu ngoặc kép khác trong đó.
Ví dụ, mình muốn tạo ra một biến có tên là Tên biến đặc biệt. Chúng ta sẽ sử dụng như dưới đây:
Dấu ngoặc kép (“) là ký tự thoát
Dấu ngoặc kép đầu tiên (được đánh dấu) ở trên là cần thiết trước dấu ngoặc kép thứ hai (là một phần của tên biến).
Coding từng bước
Power Query là một chuỗi biến đổi theo từng bước. Mọi biến đổi thường xảy ra trong một bước và nối tiếp bước tiếp theo. Trong khi coding, bạn cũng có thể nhận thấy rằng ở phía bên tay phải, bạn sẽ thấy mọi biến tạo thành một bước.
Trong ảnh chụp màn hình ở trên, bạn có thể thấy mọi biến được xác định dưới dạng một bước. Và nếu biến có khoảng trắng trong tên, nó sẽ hiển thị với khoảng trắng trong danh sách các bước được áp dụng.
Biến cuối cùng luôn được chỉ định trong phần in.
Sử dụng các loại biến khác
Có nhiều cách khác nhau để xác định mọi chữ trong Power Query. Ví dụ, nếu bạn muốn xác định một biến ngày, đây là cách thực hiện:
Để xác định tất cả các loại ký tự khác, đây là bảng tham chiếu để sử dụng:
Sử dụng các hàm tính toán M
M là một ngôn ngữ hàm, và để thực hiện hầu hết mọi thứ bạn cần gọi một hàm cho nó. Các hàm có thể được gọi dễ dàng với tên của hàm và chỉ định các tham số cho nó. Khi bạn đánh tên đầu của các loại hàm, sẽ có chỉ dẫn để bạn chọn các hàm tương ứng mà bạn cần.
Đây là tính năng vô cùng hay, giúp chúng ta không phải nhớ 100% các syntax cho các hàm mà chỉ cần nhớ loại hàm cần đánh ra trước tiên mà thôi.
Ảnh trên đây sử dụng hàm Date.WeekOfYear giúp thay thổi giá trị biến ngày thành tuần trong năm. Tên các hàm luôn bắt đầu bằng các chữ cái viết hoa: Date.WeekOfYear (). Ngoài ra, Microsoft còn cung cấp một thư viện hàm M để các bạn có thể dễ dàng tra cứu, tìm kiếm hàm cần sử dụng.
Các ký hiệu để bổ sung ý nghĩa hoặc bình luận
Giống như bất kỳ ngôn ngữ lập trình nào, bạn có thể đưa comments vào code của mình. Nó có thể ở hai dạng:
Comment dòng đơn với dấu gạch chéo kép (//)
Sử dụng // để bổ sung ý nghĩa hoặc bình luận
Comment nhiều dòng giữa dấu gạch chéo và bắt đầu (/ * nhận xét * /)
Đồng thời, bạn cũng có thể bổ sung comment bằng cách chuột phải vào step mà bạn muốn bổ sung, từ đó chọn Properties -> Bổ sung vào Description.
Sử dụng và tùy chỉnh
Bây giờ bạn đã biết một số điều cơ bản, hãy xem xét một truy vấn hiện có ở chế độ trình chỉnh sửa nâng cao và hiểu nó.
Trong ảnh chụp màn hình ở trên, bạn có thể thấy tất cả những điều cơ bản được đề cập cho đến nay:
letvàinblock- Tên biến liên kết với các bước được áp dụng trong truy vấn
- Một số tên biến có hashtag bắt đầu bằng
#và dấu ngoặc kép:#"var name" - Ký tự cuối dòng: dấu phẩy
, - Gọi các chức năng
Vẫn còn nhiều phần của truy vấn này mà bạn có thể không hiểu, đặc biệt khi sử dụng các chức năng. Bạn cần tìm hiểu các chức năng đang hoạt động để thực sự hiểu khi code.
Kết luận
Power Query là một công cụ mạnh mẽ cho việc xử lý và biến đổi dữ liệu trong các ứng dụng như Power BI và Excel. Nó cho phép người dùng kết nối và nhập dữ liệu từ nhiều nguồn khác nhau, xử lý dữ liệu, và tạo các bước công việc để biến đổi dữ liệu theo ý muốn.
Qua bài viết này, KPIM đã giới thiệu về Power Query, vai trò và lợi ích của công cụ này trong xử lý dữ liệu. Các tính năng chính và công cụ của nó, cũng như cách tích hợp công cụ này trong Power BI và Excel. Power Query là một công cụ hữu ích cho việc làm việc với dữ liệu và giúp đạt được sự hiệu quả trong công việc phân tích dữ liệu.
