[Power Query Tutorial] Cách dùng R Script và Python Script

Tích hợp R Script và Python Script trong Power Query cho phép mở rộng khả năng biến đổi, xử lý thống kê và áp dụng các mô hình học máy trực tiếp vào quy trình làm sạch dữ liệu của Power BI hoặc Excel. Thay vì bị giới hạn trong các chức năng có sẵn, việc chạy script R và Python giúp giải quyết các bài toán dữ liệu phức tạp chỉ với vài dòng lệnh.

Bài viết này cung cấp hướng dẫn đầy đủ từ khái niệm, thiết lập môi trường, các bước thực hành có ảnh minh họa chi tiết, giải thích câu lệnh M code tương ứng cho đến các lưu ý thực chiến và giải đáp thắc mắc thường gặp.

Chức năng R Script và Python Script là gì?

Chức năng R Script và Python Script trong Power Query là các bước biến đổi dữ liệu cho phép nhúng mã nguồn R hoặc Python vào quy trình ETL.

Khi chạy script, Power Query sẽ tự động chuyển bảng dữ liệu của bước liền trước thành một cấu trúc dữ liệu dạng bảng với tên biến mặc định là dataset. Người dùng có thể thao tác xử lý trên dataset này bằng các thư viện ngoài, sau đó xuất ra một Data Frame mới để Power Query nhận diện và chuyển đổi ngược lại thành bảng dữ liệu.

R Script

R Script trong Power Query khai thác trọn vẹn thế mạnh của ngôn ngữ R trong việc tính toán số học và phân tích thống kê.

• Thế mạnh: Thích hợp cho các bài toán phân tích chuỗi thời gian (time-series forecasting), phân tích tương quan, kiểm định giả thuyết thống kê, xử lý ma trận và làm sạch dữ liệu theo phân phối xác suất.
• Cơ chế hoạt động: Bảng dữ liệu đầu vào được truyền vào R dưới dạng data.frame có tên dataset. Kết quả đầu ra trả về cho Power Query cũng phải ở định dạng data.frame.

Python Script

Python Script mang hệ sinh thái xử lý dữ liệu hàng đầu của Python vào Power Query, đặc biệt thông qua thư viện pandas.

• Thế mạnh: Xử lý chuỗi văn bản nâng cao (NLP, biểu thức chính quy Regex), trích xuất và biến đổi dữ liệu phi cấu trúc/JSON, gọi API ngoài hoặc tích hợp các thuật toán phân cụm, dự báo của Scikit-Learn.
• Cơ chế hoạt động: Dữ liệu từ Power Query được nạp vào Python dưới dạng pandas.DataFrame có tên dataset. Bất kỳ đối tượng pandas.DataFrame nào được tạo ra trong script đều có thể được trích xuất thành bảng kết quả trong Power Query.

Cài đặt ngôn ngữ R và Python trong Power Query

Cài đặt môi trường

• Môi trường R: Tải và cài đặt phiên bản R chính thức từ hệ thống CRAN (khuyến nghị phiên bản R 4.x trở lên, bản 64-bit).
• Môi trường Python: Cài đặt Python chính thức (phiên bản từ 3.8 đến 3.10) hoặc bản phân phối Anaconda. Sau khi cài đặt, bạn mở Command Prompt (Terminal) và cài đặt hai thư viện bắt buộc bằng câu lệnh: pip install pandas matplotlib

Cấu hình đường dẫn trong Power BI Desktop

• Mở Power BI Desktop, vào thẻ File > chọn Options and settings > chọn Options.
• Cấu hình R: Chọn mục R scripting. Kiểm tra phần Detected R home directories xem hệ thống đã nhận diện đúng thư mục cài đặt R chưa. Nếu chưa, hãy chọn đường dẫn thủ công.
• Cấu hình Python: Chọn mục Python scripting. Kiểm tra mục Detected Python home directories đã trỏ đúng thư mục Python hay chưa.
• Nhấn OK để lưu cấu hình.

Minh họa thực tế cách sử dụng chức năng R Script và Python Script

Giao diện Power Query

Trong cửa sổ Power Query Editor, chuyển sang tab Transform trên thanh Ribbon. Ở góc ngoài cùng bên phải là nhóm công cụ Scripts chứa hai nút: Run R script và Run Python script.

Giao diện Power Query Editor hiển thị bảng dữ liệu ban đầu gồm hai cột số nguyên Column1 và Column2. Trên thanh công cụ tab Transform, mục Scripts ngoài cùng bên phải hiển thị hai biểu tượng Run R script và Run Python script.

Chức năng Run R Script

Nhấn nút Run R script trên thanh Transform. Cửa sổ nhập mã mở ra với dòng ghi chú mặc định # ‘dataset’ holds the input data for this script.

Hộp thoại Run R script hiển thị khung soạn thảo code và thông báo đường dẫn cài đặt R đang được sử dụng trên máy tính.

Nhập đoạn mã R để nhận dữ liệu từ dataset, sao chép sang bảng out và tạo thêm cột tổng Sum_Value:

# 'dataset' holds the input data for this script

out <- as.data.frame(dataset)

out$Sum_Value <- out$Column1 + out$Column2

out

Cửa sổ Run R script chứa đoạn mã biến đổi tạo ra bảng dữ liệu đầu ra mang tên out với cột tính tổng Sum_Value.

Nhấn OK. Power Query sẽ chạy script thông qua R runtime và trả về bảng kết quả đã có cột Sum_Value.

Bảng dữ liệu sau khi biến đổi bằng R Script hiển thị thêm cột Sum_Value với giá trị là tổng của Column1 và Column2 theo từng hàng.

Chức năng Run Python Script

Trên tab Transform, chọn Run Python script. Hộp thoại soạn thảo Python xuất hiện.

Cửa sổ soạn thảo Run Python script hiển thị ghi chú input dataset cùng đường dẫn Python 3.10 trên máy tính.

Nhập đoạn mã Python sử dụng thư viện pandas để xử lý tính toán:

# 'dataset' holds the input data for this script

import pandas as pd

out = pd.DataFrame(dataset)

out["Sum_Value"] = out["Column1"] + out["Column2"]

out

Khung mã nguồn Run Python script đã nhập code import pandas, tạo DataFrame out và tính cột Sum_Value.

Nhấn OK. Power Query thực thi đoạn mã và trả về danh sách các biến dạng bảng được tạo trong môi trường Python (gồm bảng gốc dataset và bảng kết quả out).

Màn hình hiển thị hai đối tượng Table tương ứng với hai biến dataset và out trong cột Value.

Để trích xuất dữ liệu, nhấn vào nút Expand (mũi tên hai chiều) ở góc phải tiêu đề cột Value. Đánh dấu chọn các cột cần lấy gồm Column1, Column2, Sum_Value và bỏ tích chọn Use original column name as prefix.

Hộp thoại Expand cột Value cho phép tick chọn các cột Column1, Column2, Sum_Value để hiển thị chi tiết ra bảng.

Bảng dữ liệu hoàn chỉnh sau khi mở rộng hiển thị đầy đủ các cột và giá trị tính toán.

Kết quả hiển thị dữ liệu đầy đủ sau khi mở rộng bảng từ bước chạy Python script.

M code của chức năng R Script và Python Script trong Power Query

Khi bạn sử dụng giao diện đồ họa, Power Query sẽ tự động chuyển hóa các thao tác thành công thức ngôn ngữ M. Bạn có thể xem và tùy biến trực tiếp trong Advanced Editor.

Cú pháp M Code cho R Script: R.Execute

Hàm R.Execute nhận mã nguồn R dạng chuỗi văn bản và tham số bảng dữ liệu nguồn:

= R.Execute("# 'dataset' holds the input data for this script#(lf)out <- as.data.frame(dataset)#(lf)out$Sum_Value <- out$Column1 + out$Column2#(lf)out", [dataset = #"Changed Type"])

• Ký tự #(lf) là ký hiệu xuống dòng (line feed) trong ngôn ngữ M.
• Tham số [dataset = #”Changed Type”] định nghĩa bảng dữ liệu từ bước trước sẽ được nạp vào biến dataset của R.

Cú pháp M Code cho Python Script: Python.Execute

Tương tự, hàm Python.Execute thực thi đoạn mã Python và nhận tham số bảng dữ liệu:

= Python.Execute("# 'dataset' holds the input data for this script#(lf)import pandas as pd#(lf)out = pd.DataFrame(dataset)#(lf)out[""Sum_Value""] = out[""Column1""] + out[""Column2""]#(lf)out", [dataset = #"Changed Type"])

• Dấu ngoặc kép trong code Python được escape bằng cặp ngoặc kép `””` khi nằm trong chuỗi M code.
• Sau bước `Python.Execute`, Power Query tự động thêm bước `Table.ExpandTableColumn` để mở rộng bảng dữ liệu.

Lưu ý khi sử dụng tính năng R/Python Script trong Power Query

Tốc độ thực thi

Mỗi bước script chạy, Power Query phải khởi tạo tiến trình R/Python riêng, ghi dữ liệu ra bộ nhớ đệm, tính toán và đọc ngược lại. Do đó tốc độ xử lý sẽ chậm hơn đáng kể so với các hàm biến đổi native của Power Query M. Do vậy, chỉ nên dùng script cho các phép tính toán phức tạp mà Power Query hoặc DAX không hỗ trợ trực tiếp.

Làm mới dữ liệu tự động trên Power BI Service

Để refresh tự động báo cáo có chứa R/Python script trên Power BI Service, bắt buộc phải thiết lập On-premises Data Gateway (Personal mode hoặc Enterprise mode có cấu hình runtime R/Python trên máy chủ Gateway).

Chuẩn hóa định dạng đầu ra

Dữ liệu trả về bắt buộc phải là đối tượng bảng (`data.frame` trong R hoặc `pandas.DataFrame` trong Python). Các kiểu dữ liệu đơn lẻ (scalar, list, array nhiều chiều) cần được chuyển đổi thành DataFrame trước khi kết thúc script.

[FAQ] Các câu hỏi thường gặp liên quan đến R Script và Python Script

Tôi có thể import các thư viện ngoài như Scikit-Learn, XGBoost hay Tidyverse không?

Có. Bạn chỉ cần cài đặt thư viện đó vào môi trường R/Python trên máy tính (dùng lệnh `pip install <tên-thư-viện>` hoặc `install.packages(“<tên-thư-viện>”)`), sau đó có thể gọi lệnh `import` hoặc `library()` bình thường trong script.

Tại sao tôi gặp lỗi “Python/R is not installed or configured”?

Lỗi này xảy ra khi Power BI không tìm thấy thư mục cài đặt ngôn ngữ. Hãy vào File > Options and settings > Options > R scripting / Python scripting để kiểm tra và trỏ lại đúng đường dẫn. Đồng thời đảm bảo phiên bản cài đặt R/Python và Power BI Desktop đều là bản 64-bit.

Có thể dùng R Python script để lấy dữ liệu từ nguồn ngoài không?

Có. Bạn vào mục Get Data > chọn Other > chọn R script hoặc Python script. Tại đây, bạn có thể viết script để tải dữ liệu từ API, đọc web scraping hoặc đọc các tệp dữ liệu đặc thù và tải trực tiếp vào Power Query.

Nên chọn R Script hay Python Script khi xử lý dữ liệu trong Power Query?

Chọn Python Script khi cần làm sạch dữ liệu văn bản, xử lý Regex, phân tích chuỗi, kết nối API hoặc ứng dụng Machine Learning cơ bản với Scikit-Learn.

Chọn R Script khi cần phân tích thống kê chuyên sâu, hồi quy đa biến phức tạp, phân tích chuỗi thời gian hoặc làm việc với các kiểm định toán học nâng cao.

Tổng kết

Việc tích hợp R Script và Python script trong Power Query mang lại sự linh hoạt cho quy trình xử lý dữ liệu ETL trong Power BI và Excel. Bằng cách kết hợp trực quan hóa và giao diện thân thiện của Power Query với hệ sinh thái phân tích mạnh mẽ của Python và R, các nhà phân tích dữ liệu có thể nhanh chóng làm sạch chuỗi văn bản nâng cao, xử lý chuỗi thời gian hay ứng dụng mô hình học máy.

Thành thạo việc nhúng R Script và Python Script trong Power Query sẽ giúp bạn giải quyết triệt để các bài toán xử lý data nâng cao và đưa mô hình Machine Learning trực tiếp vào quy trình ETL.

• Để tự học phân tích dữ liệu bạn có thể tham khảo Thư viện tự học Phân tích Dữ liệu miễn phí
• Nếu bạn muốn sở hữu lộ trình đào tạo bài bản từ tiền xử lý dữ liệu thô, tư duy Data Model đến xây dựng Dashboard chuyên nghiệp cùng giảng viên, hãy đăng ký ngay Khóa học Power BI Business Intelligence Essentials tại Starttrain.