Power BI Query Language: Cách Sử Dụng Hiệu Quả Với M

Power Query là một công cụ mạnh mẽ được tích hợp trong Microsoft Excel và Power BI, giúp người dùng nhập, làm sạch và biến đổi dữ liệu từ nhiều nguồn khác nhau. Công cụ này cho phép bạn kết nối với các tệp Excel, cơ sở dữ liệu, hoặc các nguồn dữ liệu trực tuyến, đồng thời tự động hóa quá trình xử lý dữ liệu lặp đi lặp lại.

Power Query đặc biệt hữu ích trong việc quản lý dữ liệu lớn, giúp tối ưu hóa thời gian xử lý và nâng cao hiệu suất làm việc. Bằng cách sử dụng giao diện trực quan, người dùng có thể dễ dàng thực hiện các thao tác biến đổi dữ liệu như lọc, sắp xếp, gộp, và tạo các cột tùy chỉnh mà không cần biết mã code.

Một khi dữ liệu đã được nhập và làm sạch qua Power Query, nó có thể được sử dụng trực tiếp trong các báo cáo hoặc mô hình dữ liệu của Power BI và Excel, giúp cải thiện quy trình làm việc tổng thể.

Ngôn ngữ M trong Power Query là một ngôn ngữ hàm (functional language) dùng để thao tác, biến đổi và tinh chỉnh dữ liệu. Khi thực hiện các bước trong Power Query Editor, M sẽ tự động biên dịch các thao tác này thành các câu lệnh.

• Let và In: Cú pháp cơ bản của M bắt đầu bằng let và kết thúc bằng in. Điều này giúp xác định các bước xử lý dữ liệu và cho phép tạo ra các biến lưu trữ đầu ra.
• Biến và bước: Mỗi bước trong M Query hoạt động như một biến, có thể lưu trữ các giá trị khác nhau, từ bảng dữ liệu đến danh sách và hàm.
• Thư viện hàm phong phú: M có một thư viện hàm lớn và người dùng có thể tự định nghĩa các hàm riêng để phục vụ cho các tình huống phức tạp.

Ngôn ngữ M cho phép thực hiện các thao tác dữ liệu mà giao diện người dùng Power Query không hỗ trợ, giúp bạn tạo ra các cấu trúc dữ liệu phức tạp một cách dễ dàng và linh hoạt.

Ngôn ngữ M trong Power Query có cú pháp đơn giản, giúp người dùng dễ dàng đọc và viết các lệnh xử lý dữ liệu. Dưới đây là các cấu trúc cú pháp cơ bản của M:

• Câu lệnh Let: Câu lệnh let được dùng để khai báo một tập hợp các biến và các bước xử lý. Ví dụ: let VariableName = Expression.
• Biến: Mỗi biến được tạo ra trong lệnh let có thể đại diện cho một giá trị, danh sách, bảng hoặc các kết quả của một biểu thức.
• In: Câu lệnh in xác định giá trị hoặc biểu thức cuối cùng được trả về từ câu lệnh let. Cú pháp của một lệnh let-in thường có dạng:
  let
Biến1 = Giá trị1,
Biến2 = Giá trị2
in
Biến cuối cùng
• Hàm: Ngôn ngữ M có thể tạo và gọi các hàm với cú pháp cơ bản là: (Tham số) => Biểu thức. Ví dụ, hàm cộng hai số: (x, y) => x + y.

Cú pháp của ngôn ngữ M dễ dàng cho việc chỉnh sửa và tùy biến các truy vấn trong Power Query, cho phép xử lý dữ liệu một cách linh hoạt.

Power Query cung cấp nhiều công cụ để thao tác và xử lý dữ liệu một cách dễ dàng. Dưới đây là một số thao tác cơ bản mà người dùng có thể thực hiện trong Power Query:

• Nhập dữ liệu: Power Query hỗ trợ nhập dữ liệu từ nhiều nguồn như Excel, SQL Server, CSV, và các dịch vụ đám mây. Người dùng có thể chọn nguồn dữ liệu thông qua giao diện Power Query.
• Lọc dữ liệu: Khi dữ liệu được nhập vào Power Query, bạn có thể lọc các hàng dựa trên các điều kiện cụ thể như giá trị lớn hơn hoặc nhỏ hơn, bằng hoặc khác với một giá trị cụ thể.
• Thay đổi kiểu dữ liệu: Người dùng có thể thay đổi kiểu dữ liệu của các cột trong Power Query, từ chuỗi ký tự sang số, ngày tháng, hoặc định dạng khác để phục vụ mục đích phân tích.
• Gộp và mở rộng cột: Bạn có thể gộp các cột lại với nhau, chẳng hạn như tạo một cột mới từ hai cột cũ bằng cách kết hợp giá trị của chúng. Ngoài ra, bạn cũng có thể mở rộng cột chứa danh sách hoặc bảng con thành các cột riêng biệt.
• Nhóm dữ liệu: Power Query cho phép nhóm các hàng dựa trên một hoặc nhiều cột, sau đó áp dụng các phép tính tổng hợp như tổng, trung bình, đếm số lần xuất hiện, v.v.
• Ghép nối bảng: Bạn có thể ghép nối hai bảng lại với nhau dựa trên một cột khóa chung (merge), từ đó tạo ra một bảng hoàn chỉnh với dữ liệu được liên kết.
• Xóa bỏ các hàng hoặc cột: Người dùng có thể xóa các hàng hoặc cột không cần thiết trong bảng để làm sạch dữ liệu và tối ưu hóa hiệu suất phân tích.
• Tạo cột tùy chỉnh: Ngoài các cột có sẵn, người dùng có thể tạo ra các cột tùy chỉnh mới bằng cách sử dụng các công thức và hàm trong Power Query.

Những thao tác trên giúp việc chuẩn bị và xử lý dữ liệu trong Power Query trở nên trực quan và mạnh mẽ, giúp người dùng có thể tối ưu hóa quy trình làm việc của mình.

Ngôn ngữ M trong Power Query cung cấp nhiều hàm mạnh mẽ để hỗ trợ việc thao tác và xử lý dữ liệu. Dưới đây là một số hàm phổ biến và thường được sử dụng:

• Table.SelectRows: Hàm này được sử dụng để lọc các hàng trong một bảng dựa trên điều kiện cụ thể.
• Text.Combine: Hàm này cho phép kết hợp nhiều chuỗi văn bản thành một chuỗi duy nhất. Ví dụ: Text.Combine({"Hello", "World"}, " ") sẽ trả về "Hello World".
• List.Sum: Hàm này dùng để tính tổng của một danh sách các giá trị số. Ví dụ: List.Sum({1, 2, 3, 4}) sẽ trả về 10.
• DateTime.From: Hàm này chuyển đổi một chuỗi ký tự hoặc giá trị số thành kiểu dữ liệu ngày giờ.
• Table.AddColumn: Sử dụng hàm này để thêm một cột mới vào bảng hiện có. Ví dụ: Table.AddColumn(Source, "NewColumn", each [OldColumn] * 2) sẽ thêm cột "NewColumn" với giá trị gấp đôi "OldColumn".
• Record.Field: Dùng hàm này để lấy giá trị của một trường trong bản ghi. Ví dụ: Record.Field([Record], "FieldName") sẽ trả về giá trị của trường "FieldName".
• Text.Contains: Hàm này giúp kiểm tra xem một chuỗi có chứa chuỗi con cụ thể hay không. Ví dụ: Text.Contains("Hello World", "World") sẽ trả về true.
• Number.Round: Hàm này làm tròn giá trị số đến số thập phân gần nhất. Ví dụ: Number.Round(3.14159, 2) sẽ trả về 3.14.

Các hàm trên giúp bạn dễ dàng thực hiện các thao tác xử lý dữ liệu trong Power Query, từ việc lọc, xử lý chuỗi văn bản, tính toán số học đến thao tác với bản ghi và bảng.

Power Query Editor là một công cụ mạnh mẽ trong Power BI, giúp bạn kết nối, chỉnh sửa và định hình dữ liệu từ nhiều nguồn khác nhau một cách trực quan. Dưới đây là các phần chính trong giao diện và cách sử dụng của nó:

• Tab Home: Đây là tab chứa các thao tác phổ biến, như kết nối nguồn dữ liệu mới, tải lại dữ liệu, áp dụng thay đổi, và quản lý các bước truy vấn.
• Tab Transform: Tại đây, bạn có thể thực hiện các bước chuyển đổi dữ liệu như thêm/xóa cột, thay đổi kiểu dữ liệu, tách hoặc hợp nhất cột.
• Tab Add Column: Cho phép thêm các cột tùy chỉnh, định dạng dữ liệu trong các cột, và thực hiện các phép tính khác nhau trên các cột.
• Tab View: Tab này kiểm soát việc hiển thị các ngăn như Query Settings, Data View, và Advanced Editor để chỉnh sửa trực tiếp M Code.

Giao diện Power Query Editor cũng bao gồm:

• Queries Pane (cột bên trái): Hiển thị các truy vấn đang hoạt động và các truy vấn có thể được chọn để chỉnh sửa.
• Data View (ngăn trung tâm): Là nơi hiển thị dữ liệu của truy vấn để thực hiện các bước chuyển đổi và chỉnh sửa.
• Query Settings (cột bên phải): Hiển thị các bước chuyển đổi đã được áp dụng, bạn có thể quản lý, chỉnh sửa, hoặc xóa các bước này mà không thay đổi dữ liệu gốc.

Nhờ giao diện thân thiện và dễ sử dụng, Power Query Editor cho phép người dùng thực hiện mọi thao tác từ kết nối dữ liệu, chỉnh sửa đến chuyển đổi dữ liệu một cách linh hoạt mà không làm thay đổi nguồn dữ liệu gốc.

Trong phần này, chúng ta sẽ cùng thực hành một số ví dụ cơ bản để hiểu cách viết và sử dụng ngôn ngữ M trong Power Query. Bằng cách thực hiện các thao tác biến đổi dữ liệu như thêm cột, sắp xếp, và nhóm dữ liệu, bạn sẽ nắm rõ hơn về các cú pháp và tính năng của ngôn ngữ M.

7.1 Ví dụ: Biến đổi dữ liệu trong Power Query

Giả sử bạn có một bảng dữ liệu giao dịch hàng ngày với các cột: CREATED_DATE, CREATED_AT, PAID_AT, PAID_AMOUNT, và bạn muốn thêm một cột mới để tính toán deadline cho từng giao dịch. Hãy làm theo các bước sau để thực hiện:

1. Bước 1: Mở Power Query Editor và chọn bảng dữ liệu bạn muốn biến đổi.
2. Bước 2: Truy cập tab "Add Column" và chọn "Custom Column".
3. Bước 3: Nhập công thức dưới đây để thêm một cột Deadline với giá trị được tính bằng cách cộng thêm 10 tiếng vào cột CREATED_AT:

   Deadline = DateTime.AddHours([CREATED_AT], 10)

4. Bước 4: Nhấn "OK" và kiểm tra kết quả hiển thị của cột Deadline mới.

7.2 Thực hành nhóm dữ liệu và sắp xếp bằng Table.Group và Table.Sort

Trong ví dụ tiếp theo, chúng ta sẽ nhóm dữ liệu theo cột TRANSACTION_TYPE và tính tổng PAID_AMOUNT cho mỗi nhóm, sau đó sắp xếp các nhóm theo thứ tự giảm dần:

1. Bước 1: Mở Advanced Editor trong Power Query Editor để bắt đầu viết query với ngôn ngữ M.
2. Bước 2: Sử dụng cú pháp ngôn ngữ M sau để nhóm dữ liệu theo TRANSACTION_TYPE và tính tổng:

let
    Source = Table.FromRecords({
        [TRANSACTION_TYPE = "Bán lẻ", PAID_AMOUNT = 200],
        [TRANSACTION_TYPE = "Bán buôn", PAID_AMOUNT = 500],
        [TRANSACTION_TYPE = "Bán lẻ", PAID_AMOUNT = 300]
    }),
    Grouped = Table.Group(Source, {"TRANSACTION_TYPE"}, 
                          {{"Total_Paid", each List.Sum([PAID_AMOUNT]), type number}})
in
    Grouped

3. Bước 3: Sau khi tạo bảng nhóm, sử dụng Table.Sort để sắp xếp các giá trị theo cột Total_Paid từ cao đến thấp:

   let
       SortedTable = Table.Sort(Grouped, {{"Total_Paid", Order.Descending}})
   in
       SortedTable

4. Bước 4: Nhấn "Done" và xem kết quả sau khi nhóm và sắp xếp dữ liệu.

7.3 Ví dụ khác: Loại bỏ các bản ghi trùng lặp

Để loại bỏ các bản ghi trùng lặp trong bảng, bạn có thể sử dụng hàm Table.Distinct. Cú pháp như sau:

let
    DistinctTable = Table.Distinct(Source)
in
    DistinctTable

Hàm Table.Distinct sẽ giúp bạn loại bỏ tất cả các bản ghi trùng lặp, chỉ giữ lại một bản duy nhất cho mỗi giá trị trùng lặp trong bảng nguồn.

Để tối ưu hóa các truy vấn trong Power Query, bạn cần áp dụng một số phương pháp và chiến lược giúp cải thiện hiệu suất xử lý dữ liệu. Việc tối ưu hóa này không chỉ giúp tăng tốc độ thực thi mà còn đảm bảo kết quả đầu ra chính xác và dễ dàng bảo trì. Dưới đây là một số bước chi tiết để bạn tham khảo:

1. Giảm số lượng cột và hàng cần xử lý:

   Khi làm việc với các bảng dữ liệu lớn, hãy loại bỏ các cột không cần thiết ngay từ đầu để giảm tải cho hệ thống. Bạn có thể sử dụng công cụ Remove Columns trong Power Query để loại bỏ các cột không liên quan và chỉ giữ lại những trường dữ liệu cần thiết.

2. Sử dụng chức năng lọc dữ liệu hợp lý:

   Khi cần thao tác với một tập con của dữ liệu, hãy áp dụng bộ lọc ngay từ các bước đầu để hạn chế lượng dữ liệu cần xử lý. Sử dụng chức năng Filter để lọc các giá trị theo điều kiện cụ thể giúp giảm số lượng hàng trong các phép tính kế tiếp.

3. Áp dụng chiến lược nhóm (Group By):

   Nếu bạn cần tính toán các giá trị tổng hợp như tổng, trung bình, hoặc đếm, hãy sử dụng chức năng Group By thay vì thực hiện các phép tính này thủ công. Việc này giúp giảm thời gian xử lý và đảm bảo dữ liệu được tổ chức một cách hiệu quả.

4. Sử dụng chức năng Merge và Append một cách hiệu quả:

   Khi kết hợp nhiều bảng dữ liệu, hãy đảm bảo rằng bạn chỉ kết hợp các bảng khi cần thiết. Tránh việc sử dụng các phép toán kết hợp phức tạp khi dữ liệu có thể được xử lý một cách độc lập trước đó.

5. Tối ưu hóa các biểu thức M code:

   M là ngôn ngữ chính được sử dụng trong Power Query để thực hiện các thao tác biến đổi dữ liệu. Việc viết các biểu thức M một cách tối ưu và hạn chế việc lặp lại các phép toán phức tạp sẽ giúp giảm đáng kể thời gian thực thi. Ví dụ:

   let
       Nguon = Excel.Workbook(File.Contents("DuongDan.xlsx"), null, true),
       ChonSheet = Nguon{[Item="Sheet1",Kind="Sheet"]}[Data],
       ChuyenDoi = Table.TransformColumnTypes(ChonSheet,{{"MaNV", Int64.Type}, {"TenNV", type text}})
   in
       ChuyenDoi
           

   Trong đoạn code trên, mỗi bước biến đổi đều được đặt tên rõ ràng và tối ưu hóa để tránh các phép tính không cần thiết.

6. Sử dụng Advanced Editor để kiểm tra và tinh chỉnh các biểu thức:

   Advanced Editor là nơi bạn có thể xem toàn bộ mã M của các truy vấn trong Power Query. Sử dụng công cụ này để tối ưu hóa từng bước một và loại bỏ các bước biến đổi không cần thiết.

7. Sắp xếp lại thứ tự các bước truy vấn hợp lý:

   Thứ tự thực hiện các bước truy vấn có thể ảnh hưởng lớn đến hiệu suất của Power Query. Hãy đảm bảo rằng các bước nặng nề như Merge hoặc Group By được thực hiện sau các bước lọc và loại bỏ dữ liệu không cần thiết để giảm lượng dữ liệu cần xử lý.

8. Sử dụng Buffering để tăng tốc độ xử lý:

   Buffering là một kỹ thuật nâng cao giúp lưu trữ tạm thời một bảng dữ liệu trung gian. Điều này đặc biệt hữu ích khi bạn cần sử dụng cùng một bảng dữ liệu nhiều lần trong các biểu thức. Sử dụng cú pháp như sau:

   let
       Data = Table.Buffer(YourTable)
   in
       Data
           

   Kỹ thuật này giúp giảm thời gian thực hiện các phép toán phức tạp.

Bằng cách áp dụng những kỹ thuật trên, bạn có thể tối ưu hóa các truy vấn trong Power Query một cách hiệu quả, đảm bảo dữ liệu được xử lý nhanh chóng và chính xác.

Power Query là một công cụ mạnh mẽ trong Power BI cho phép người dùng xử lý, làm sạch và tối ưu hóa dữ liệu một cách hiệu quả. Việc nắm vững ngôn ngữ truy vấn và các tính năng của Power Query không chỉ giúp cải thiện tốc độ xử lý dữ liệu mà còn hỗ trợ nâng cao chất lượng báo cáo. Đặc biệt, khi sử dụng đúng các kỹ thuật tối ưu hóa, bạn có thể giảm thiểu thời gian tải dữ liệu và tăng tính tương tác của báo cáo.

Để tối ưu hóa query trong Power Query, bạn nên tập trung vào một số điểm chính như sau:

• Sử dụng các bước lọc dữ liệu hợp lý ngay từ đầu để loại bỏ các dòng không cần thiết trước khi thực hiện các phép tính phức tạp.
• Áp dụng các bước biến đổi dữ liệu theo thứ tự tối ưu, chẳng hạn như sắp xếp và nhóm dữ liệu (Group By) trước khi thực hiện các bước khác.
• Sử dụng ngôn ngữ M một cách hiệu quả để tạo ra các công thức tùy chỉnh, giúp giảm thiểu sự phụ thuộc vào các phép tính phức tạp trong quá trình tải dữ liệu.
• Tận dụng các tính năng như “Reference” thay vì “Duplicate” để tránh tạo bản sao không cần thiết của dữ liệu, từ đó giảm dung lượng bộ nhớ.

Kết luận, hiểu rõ cách thức tối ưu hóa và xây dựng query trong Power Query không chỉ giúp bạn nâng cao kỹ năng phân tích dữ liệu mà còn mang lại giá trị lớn trong việc tối ưu hóa quy trình làm việc. Với một nền tảng vững chắc về ngôn ngữ M và các kỹ thuật tối ưu hóa, bạn sẽ có khả năng xây dựng các giải pháp dữ liệu mạnh mẽ và linh hoạt cho các nhu cầu phức tạp.