Chưa có sản phẩm trong giỏ hàng.
3 Cách dùng M Query để biến đổi dữ liệu trong Power Query Editor
Thông thường, chúng ta hay thực hiện xử lý và biến đổi dữ liệu trong Power BI thông qua chức năng Power Query Editor. Nếu bạn làm việc đủ lâu với nó, bạn sẽ biết rằng thực chất các thao tác biến đổi dữ liệu của bạn đang được xử lý bởi ngôn ngữ M Query. Trong những nhu cầu biến đổi cơ bản thì chúng ta có thể thao tác thông qua giao diện của Power Query Editor. Tuy nhiên, đối với một số tình huống phức tạp hơn, bắt buộc chúng ta phải sử dụng M Query và can thiệp sâu vào quá trình biến đổi dữ liệu. Trong bài viết hôm nay, chúng ta cùng đi tìm hiểu tổng quan về M Query và một số ứng dụng của nó nhé.
Mục lục
M Query là gì?
M Query (tên đầy đủ là Power Query M) là một ngôn ngữ truy vấn mạnh mẽ được sử dụng để truy vấn và biến đổi dữ liệu trong Power Query. Ngôn ngữ này được tạo ra bởi Microsoft để giúp người dùng tạo ra các bước biến đổi phức tạp cho dữ liệu của họ. Chúng ta có thể sử dụng M Query với các nguồn dữ liệu như Analysis Services, Excel, hay Power BI workbooks. Sức mạnh chính của Power Query đó chính là mash-up data (Có thể hiểu đơn giản là kết hợp dữ liệu) từ một hoặc nhiều nguồn khác nhau và xử lý. Output sẽ đưa ra dạng data chuẩn hóa trước khi thực hiện phân tích.
Một lưu ý khá quan trọng đó là “M is a case-sensitive language”, tức trong logic xử lý của nó sẽ phân biệt các tình huống chữ hoa và chữ thường.
Quá trình data được xử lý bởi M Query
Tại sao nên sử dụng M Query thay vì chỉ thao tác với Power Query Editor?
Việc can thiệp sâu vào tầng xử lý data bằng ngôn ngữ M Query sẽ giúp chúng ta có một số ưu điểm như sau:
- Truy vấn dữ liệu động: Bạn có thể tạo các truy vấn dữ liệu động bằng cách sử dụng tham số. Điều này cho phép bạn thay đổi nguồn dữ liệu hoặc điều kiện truy vấn mà không cần phải thay đổi mã source thủ công.
- Biến đổi dữ liệu linh hoạt: M Query cung cấp nhiều công cụ và biểu thức để biến đổi dữ liệu theo yêu cầu của bạn. Bạn có thể thực hiện các bước biến đổi từ đơn giản đến phức tạp, như xử lý chuỗi, tách cột, ghép nối và nhiều biến đổi khác.
- Chuẩn hóa và làm sạch dữ liệu: M Query cung cấp nhiều function tùy chỉnh sâu hơn để giúp bạn loại bỏ dữ liệu trùng lặp, xử lý giá trị bị thiếu hoặc không hợp lệ, thay đổi định dạng kiểu dữ liệu và thực hiện các biến đổi khác để đảm bảo tính nhất quán và đáng tin cậy của dữ liệu.
- Tối ưu hiệu suất: Bằng cách sử dụng các M Query và ứng dụng các kỹ thuật tối ưu, bạn có thể cải thiện hiệu suất xử lý và tải dữ liệu. Sử dụng các bước folding cho phép bạn chuyển một số biến đổi tới nguồn dữ liệu để tận dụng khả năng xử lý của cơ sở dữ liệu.
- Kiểm soát quá trình chuẩn bị dữ liệu: M Query cho phép bạn thấy rõ các bước biến đổi dữ liệu và tùy chỉnh chúng theo ý muốn. Điều này giúp bạn kiểm soát rõ ràng quá trình chuẩn bị dữ liệu và làm việc với dữ liệu có chất lượng tốt hơn.
- Lập trình hóa và tự động hóa: M Query sử dụng ngôn ngữ lập trình có cú pháp rõ ràng, cho phép bạn lập trình hóa các bước biến đổi dữ liệu và tạo các kịch bản tự động hóa việc chuẩn bị dữ liệu.
- Tùy chỉnh tích hợp nhiều nguồn dữ liệu: M Query cho phép bạn kết nối và truy vấn nhiều nguồn dữ liệu khác nhau như cơ sở dữ liệu SQL, tệp văn bản, bảng Excel, dịch vụ web và nhiều nguồn khác. Điều này giúp bạn tổng hợp dữ liệu từ nhiều nguồn vào một tập dữ liệu duy nhất.
Làm thế nào để sử dụng M Query trong Power BI?
Trong Power BI, người dùng có thể sử dụng M Query theo 3 cách, tương ứng với đó là 3 mức độ từ dễ đến khó.
Use the Ribbon
Bản chất cách sử dụng này chúng ta không cần phải đụng gì đến code cả. Như mình đã nói ở phần đầu, các thao tác biến đổi dữ liệu thực ra là được xử lý bởi ngôn ngữ M Query. Vì vậy, khi bạn thao tác với bất kỳ công cụ nào trong giao diện Power Query Editor, thì code M Query đã được tự sinh ra rồi.
Ví dụ:
- Tại màn hình Power Query Editor, mình chọn “New Source” và chọn source là file CSV => trỏ đến đường dẫn file CSV của mình. Sau khi data được load vào Power Query Editor, thì tại bước đầu tiên của quá trình xử lý, bạn sẽ thấy một đoạn code M Query được tự động tạo ra. Đây là đoạn code M Query dùng để lấy data từ file CSV trong máy mình.
- Tương tự như vậy, nếu bạn thực hiện một thao tác xóa Column thì code M Query cũng sẽ tự động sinh ra.
Click and Adjust
Với cách dùng Use the Ribbon thì đơn giản bạn chỉ đọc hiểu logic code thôi chứ chưa can thiệp gì cả. Với “Click and Adjust”, ngoài việc hiểu logic thực hiện của M Query, bạn trực tiếp Adjust (Tùy chỉnh) code để phù hợp với nhu cầu của bản thân. Việc tùy chỉnh này không yêu cầu bạn hiểu sâu về M Query bởi các đoạn code tự sinh từ các bước bạn thao tác trên Power Query Editor tương đối dễ hiểu.
Ví dụ:
Khi load data, Power BI sẽ có một bước Change Type (biến đổi kiểu dữ liệu) mặc định. Nếu có một cột nào đó bị biến đổi sang kiểu dữ liệu không dúng ý bạn, bạn có thể trực tiếp sửa vào code M Query. Dưới đây là bộ dữ liệu và code M Query tự sinh ra.
Code này đang tự đổi cột CREATED_DATE sang dạng text, nhưng mình muốn đổi cột này sang dạng date thì ta chỉ cần đổi đoạn type sang “date”.
Nếu đã có hiểu biết sâu hơn về M Query, bạn có thể vào xem toàn bộ đoạn code M Query dùng để tạo ra 1 bảng data bất kỳ. Bạn click chuột phải vào bảng data cần xem => Chọn Advance Editor => Sẽ ra giao diện code M Query và có thể tùy ý chỉnh sửa.
Giao diện của phần code M Query sẽ trông như thế này.
Tổng quan chung về syntax của M Query sẽ có dạng như sau:
Let
Step 1 = …………………………………………….;
Step 2 = …………………………………………….;
in
- Mỗi Bước trong Power Query giống như một Biến. Các biến này lưu trữ output của bước đó. Output này có thể là một bảng, danh sách, hàm, giá trị, v.v. mà sau đó bạn có thể gọi trong bất kỳ bước nào khác của truy vấn.
- Tất cả các bước đều kết thúc bằng dấu phẩy (,) để đánh dấu là đã hoàn thành, trừ bước cuối cùng.
- let và in là hai từ khóa cho phép bạn xác định các bước trong truy vấn. Code M Query luôn bắt đầu bằng let và kết thúc bằng in. Chúng khá giống với cấu trúc VAR và RETURN trong DAX.
Custom Code
Level cuối cùng trong việc sử dụng M Query đó chính là viết tất cả code để phục vụ mục đích nào đó. Trường hợp hay sử dụng Custom Code chính là việc tạo thêm các cột thông tin mới.
Ví dụ về việc tạo thêm column mới dùng M Query:
Giả sử chúng ta có bộ dữ liệu về transaction hàng ngày của một công ty A bao gồm các thông tin: CREATED_DATE, CREATED_AT, PAID_AT, PAID_AMOUNT, TRANSACTION_TYPE, TRANSACTION_TYPE_DETAIL, PRODUCT_NAME VÀ TRANSACTION_ID.
Công ty yêu cầu deadline hoàn thành giao dịch và sau 10 tiếng kể từ khi giao dịch được tạo (CREATED_AT). Trong các chức năng mặc định của Power Query Editor không thể tạo cột deadline theo logic như trên. Vì vậy trong trường hợp này ta buộc phải dùng M Query để tạo cột thông tin deadline.
Các bước thực hiện:
Bước 1:
Vào tab “Add Column” => Chọn “Custom Column”
Bước 2:
Khi màn hình giao diện nhập code hiện ra, ta nhập theo công thức như sau và nhấn ok:
Sau khi hoàn thành, chúng ta đã có cột thông tin DEADLINE được cộng thêm 10 tiếng từ cột CREATED_AT.
Các logic code và công thức cụ thể của M Query, bạn có thể tham khảo thêm tại: Link.
Kết luận
M Query là một phần quan trọng của Power BI, giúp bạn truy vấn, biến đổi và tinh chỉnh dữ liệu trước khi nạp chúng vào mô hình dữ liệu. Bằng cách sử dụng M Query, bạn có thể chuẩn hóa dữ liệu, tạo cấu trúc phù hợp và xử lý các vấn đề dữ liệu không đồng nhất. Hiểu biết về cách sử dụng M Query sẽ giúp bạn tối ưu hóa quá trình chuẩn bị dữ liệu và nâng cao khả năng phân tích dữ liệu trong Power BI.
Ngoài ra, Power BI còn có thể convert các thao tác xử lý dữ liệu trở thành native query, mục đích cũng là để tối ưu quy trình xử lý và load dữ liệu. Các bạn có thể tham khảo tại đây: Sử dụng Query Folding trong Power BI
- Tham gia group ôn thi PL-300/DA-100 tại: https://www.facebook.com/groups/da100vn
- Chuỗi Video Hướng dẫn thực hành Lab và sử dụng các tài nguyên của Microsoft: https://www.youtube.com/c/Datapotvn/videos
- Update tài nguyên từ Microsoft, DA-100 exam questions và exam topics tại Fanpage của Datapot: https://www.facebook.com/DatapotAnalytics/
Với các bạn mới bắt đầu tìm hiểu Power BI & mong muốn ứng dụng Power BI trong công việc, hãy tham khảo khóa học PL-300: Microsoft Power BI Data Analyst từ Datapot.
Được thiết kế và giảng dạy bởi các chuyên gia giàu kinh nghiệm làm việc và đào tạo trong ngành dữ liệu, được triển khai tại các doanh nghiệp như Unilever, SHB, MB Ageas Life,.., khóa học sẽ mang đến cho bạn những kiến thức và kỹ năng thực tiễn nhất.
