Lập trình trong Power Query và các ví dụ

[befaint]

Lời tựa:
Mấy năm gần đây, Power Query đã dần được sử dụng nhiều hơn mặc dù Microsoft (MS) đã hỗ trợ từ Excel 2010.


Ở phạm vi diễn đàn, trên các kênh/ trang mạng hướng dẫn khác cũng có khá nhiều tài liệu, các ví dụ về lập trình trình trong Power Query.
Power Query có hỗ trợ môi trường lập trình (không biết từ ngữ phù hợp chưa) cho phép viết code, thực thi code, sử dụng M code "Power Query M formula".
Tức là cũng cho phép tạo các 'custom funtions', và gọi các functions đó, truyền các tham số vào các functions đó. Đại khái cũng nhưng tạo Sub/ Functions trong VBA.
Vậy nên, bữa nay có chút thời gian rảnh mình làm ví dụ minh họa. Ai có nhã hứng thì góp ví dụ nhé.

Lưu ý:
1/ Mỗi nền tảng, mỗi ngôn ngữ lập trình có đặc điểm, có ưu nhược điểm riêng nên trong đây chúng ta không nên chê cái này chậm/ kém, khen cái kia nhanh/ tốt.
Cái nào ta sử dụng được, thấy phù hợp với công việc cụ thể, hay đơn giản mình thích thì mình dùng thôi.
2/ Chủ đề này không thuộc chuyên mục Office365 nên mod/admin đừng di chuyển đi nhé.
---------

Ví dụ:
Đề bài (nhân tiện từ một chủ đề gần đây): Tổng hợp bảng tỷ giá ngoại tệ theo khoảng thời gian cho trước, nếu bảng đã có dữ liệu ngày nào thì bỏ qua ngày đó.
Mình thử chạy cho 1 tháng hết cỡ ~ 05 giây. Chắc do mạng nhanh.

 

[Hoàng Trọng Nghĩa]

Lời tựa:
Mấy năm gần đây, Power Query đã dần được sử dụng nhiều hơn mặc dù Microsoft (MS) đã hỗ trợ từ Excel 2010.
View attachment 264650

Ở phạm vi diễn đàn, trên các kênh/ trang mạng hướng dẫn khác cũng có khá nhiều tài liệu, các ví dụ về lập trình trình trong Power Query.
Power Query có hỗ trợ môi trường lập trình (không biết từ ngữ phù hợp chưa) cho phép viết code, thực thi code, sử dụng M code "Power Query M formula".
Tức là cũng cho phép tạo các 'custom funtions', và gọi các functions đó, truyền các tham số vào các functions đó. Đại khái cũng nhưng tạo Sub/ Functions trong VBA.
Vậy nên, bữa nay có chút thời gian rảnh mình làm ví dụ minh họa. Ai có nhã hứng thì góp ví dụ nhé.

Lưu ý:
1/ Mỗi nền tảng, mỗi ngôn ngữ lập trình có đặc điểm, có ưu nhược điểm riêng nên trong đây chúng ta không nên chê cái này chậm/ kém, khen cái kia nhanh/ tốt.
Cái nào ta sử dụng được, thấy phù hợp với công việc cụ thể, hay đơn giản mình thích thì mình dùng thôi.
2/ Chủ đề này không thuộc chuyên mục Office365 nên mod/admin đừng di chuyển đi nhé.
---------

Ví dụ:
Đề bài (nhân tiện từ một chủ đề gần đây): Tổng hợp bảng tỷ giá ngoại tệ theo khoảng thời gian cho trước, nếu bảng đã có dữ liệu ngày nào thì bỏ qua ngày đó.
Mình thử chạy cho 1 tháng hết cỡ ~ 05 giây. Chắc do mạng nhanh.

View attachment 264652

Chắc do mình không biết dùng cái này nên nó bị lỗi chẳng?

 

[Cá ngừ F1]

Chắc do mình không biết dùng cái này nên nó bị lỗi chẳng?

View attachment 264658

Em cũng đang bị lỗi giống a Nghĩa.

Dạng bài này đang được chia sẻ trong Topic này: https://www.giaiphapexcel.com/diend...n-hàng-về-file-excel-bằng-power-query.157180/

Xin phép a cho di chuyển bài vô đây nhé, với một ví dụ khác có thể khởi tạo Topic mới.

 

[befaint]

(Mình đang dùng điện thoại)
Các bạn chỉnh trong privacy của Power Query nhé.

PowerQuery Privacy settings not retained

Hi! I'm new to PowerQuery but not so much to Excel and struggling with a weird Privacy settings issue I have a few workbooks completed with PowerQuery. One is simply fetching a table from another workbook with some row filtering and the other is importing a folder full of XML-s. Everything is...

community.powerbi.com

 

[excel_lv1.5]

(Mình đang dùng điện thoại)
Các bạn chỉnh trong privacy của Power Query nhé.

PowerQuery Privacy settings not retained

Hi! I'm new to PowerQuery but not so much to Excel and struggling with a weird Privacy settings issue I have a few workbooks completed with PowerQuery. One is simply fetching a table from another workbook with some row filtering and the other is importing a folder full of XML-s. Everything is...

community.powerbi.com

Tốt nhất là nên chỉnh query lại bạn ạ, query bạn đang sử dụng source trung gian nên mới cảnh báo, có thể ignore khi dùng phiên bản desktop nhưng nếu dùng power BI đã public lên web sẽ bị lỗi khi resfresh

 

[Cá ngừ F1]

(Mình đang dùng điện thoại)
Các bạn chỉnh trong privacy của Power Query nhé.

PowerQuery Privacy settings not retained

Hi! I'm new to PowerQuery but not so much to Excel and struggling with a weird Privacy settings issue I have a few workbooks completed with PowerQuery. One is simply fetching a table from another workbook with some row filtering and the other is importing a folder full of XML-s. Everything is...

community.powerbi.com

Không lên được bảng Privacy để tích chọn đó anh.

 

[ptm0412]

Các bạn chỉnh trong privacy của Power Query nhé.

- Không mở được privacy của query
- Câu lệnh
listDates = List.Generate(()=> dateFrom, each _<=dateTo, each _+1, each Date.From(_))

Có thể viết đơn giản là

listDates = {dateFrom..dateTo}

2/ Chủ đề này không thuộc chuyên mục Office365 nên mod/admin đừng di chuyển đi nhé.

Hiện tại có chuyên mục mới là Power Query. Nói thêm là Power query phiên bản trong Excel 2010 thiếu 1 số hàm M về xử lý chuỗi (tôi phát hiện chưa hết). Những phiên bản sau nâng cấp dần lên và thư viện M cũng mở rộng thì phải (cũng có thể là MS muốn hạn chế như thế đối với phiên bản Excel thấp). Trong khi MS 365 nâng cấp thường xuyên nếu có bản quyền, và thư viện M có thể cũng được bổ sung theo.

 

[excel_lv1.5]

Không lên được bảng Privacy để tích chọn đó anh.

Trong File->option and setting->Query option->Privacy->Check Allway ignore... tắt cái này thì vẫn lỗi nếu dùng power BI khi upload web, vì cảnh báo này là một dạng rủi ro của source không xác định

- Không mở được privacy của query
- Câu lệnh
listDates = List.Generate(()=> dateFrom, each _<=dateTo, each _+1, each Date.From(_))

Có thể viết đơn giản là

listDates = {dateFrom..dateTo}

Viết vầy thì dateFrom, dateTo phải là number vì date không sử dụng được, thêm bước chuyển đổi nữa mới thành Date được List.Transform({dateFrom..dateTo},Date.From)

 

[ptm0412]

Viết vầy thì dateFrom, dateTo phải là number vì date không sử dụng được, thêm bước chuyển đổi nữa mới thành Date được

List.Transform({dateFrom..dateTo},Date.From)

Tại tôi thấy khai báo ban đầu trong file đã là Number:

dateFrom = Number.From(Excel.CurrentWorkbook(){[Name="dateFrom"]}[Content]{0}[Column1]),
    dateTo   = Number.From(Excel.CurrentWorkbook(){[Name="dateTo"]}[Content]{0}[Column1]),

À, tôi nhầm. Đọc sót Date.From(_)

 

[befaint]

Tốt nhất là nên chỉnh query lại bạn ạ, query bạn đang sử dụng source trung gian nên mới cảnh báo, có thể ignore khi dùng phiên bản desktop nhưng nếu dùng power BI đã public lên web sẽ bị lỗi khi resfresh

Power BI thì ngon quá, có tooltip khi gõ code.
Cái vụ privacy chỉ là cảnh báo query này 'ngó' được query khác.

Không mở được privacy của query

Ở cửa sổ Power Query anh vào File menu, tới mục dưới cùng thì phải, rồi tìm tới privacy của thisworkbook...

Hiện tại có chuyên mục mới là Power Query.

Nó là mục con của Office365 anh ơi. Chẹp.

 

[ptm0412]

Power BI thì ngon quá, có tooltip khi gõ code.

365 cũng có tool tip rồi

Ở cửa sổ Power Query anh vào File menu, tới mục dưới cùng thì phải, rồi tìm tới privacy của thisworkbook...

Privacy không có this workbook, Permission mới có

Nó là mục con của Office365 anh ơi. Chẹp.

Như đã giải thích, Power query 365 đầy đủ hơn. Mà thôi, mới chích ngừa Vero Cell ...

 

[excel_lv1.5]

Power BI thì ngon quá, có tooltip khi gõ code.
Cái vụ privacy chỉ là cảnh báo query này 'ngó' được query khác.

Excel cũng có tooltip mà
Không chỉnh query lại nó sẽ bị lỗi khi refresh nếu public và bắt buộc phải combine cái source đó đấy nếu muốn cài scheduled

 

[befaint]

Excel cũng có tooltip mà

Mình dùng Excel2019 không thấy gì. @@

 

[excel_lv1.5]

Mình dùng Excel2019 không thấy gì. @@

Ah, tôi dùng 365 , chắc chỉ hỗ trợ trên 365

 

[Cá ngừ F1]

(Mình đang dùng điện thoại)
Các bạn chỉnh trong privacy của Power Query nhé.

PowerQuery Privacy settings not retained

Hi! I'm new to PowerQuery but not so much to Excel and struggling with a weird Privacy settings issue I have a few workbooks completed with PowerQuery. One is simply fetching a table from another workbook with some row filtering and the other is importing a folder full of XML-s. Everything is...

community.powerbi.com

em chỉnh lại Privacy đã chạy được.
Tuy nhiên, trong PQ có: 4 cái Fx, 2 cái Query, a có thể giải thích thêm về công dụng của các Fx?

 

[ptm0412]

em chỉnh lại Privacy đã chạy được.
Tuy nhiên, trong PQ có: 4 cái Fx, 2 cái Query, a có thể giải thích thêm về công dụng của các Fx?

Các Fx cũng đọc để hiểu trong Advanced Editor luôn, cú pháp các câu lệnh cũng giống như query, nhưng mở đầu là các tham số trong ngoặc rồi tới => rồi let. Tham số trong các câu lệnh thì xài tham số của hàm.
Trong tài liệu hàm M căn bản cũng có cú pháp Fx.

Cùng lắm không hiểu thì invoke ra 1 query mới và xem kết quả

 

[Cá ngừ F1]

Các Fx cũng đọc để hiểu trong Advanced Editor luôn, cú pháp các câu lệnh cũng giống như query, nhưng mở đầu là các tham số trong ngoặc rồi tới => rồi let. Tham số trong các câu lệnh thì xài tham số của hàm.
Trong tài liệu hàm M căn bản cũng có cú pháp Fx.

Cùng lắm không hiểu thì invoke ra 1 query mới và xem kết quả

Mấy cái ký hiệu kiểu “=>” này khó nhằn ghê

 

[befaint]

Tuy nhiên, trong PQ có: 4 cái Fx, 2 cái Query, a có thể giải thích thêm về công dụng của các Fx?

Khi thực hiện làm dự án/ chương trình thì cần phân tích và tách nhỏ các phần công việc riêng ra. Rồi lập function cho từng công việc đó.
Bước này rất quan trọng, nhằm dễ xử lý và quản lý, không bị thiếu sót. Sau này chỉnh sửa, nâng cấp, mở rộng cũng thuận tiện hơn.
Đại khái chia ra để trị, lập sơ đồ khối ấy.
---
Tên function, tên biến thì cố gắng đặt tên theo nhiệm vụ của nó luôn, đỡ phần nào ghi chú thích. Nên bạn đọc tên function là biết công dụng của nó.
Trong Power Query, khi đổi tên function/ query thì những chỗ tham chiếu/ dùng tới nó tự động cập nhật luôn, cái này khá hay.

Về cấu trúc lập custom function thì kiểu vầy.

( [variable [as type]] ) [as type] =>
block code

Màu vàng: Cấu trúc tạo function, bắt buộc.

[variable [as type]]: Ngoặc vuông không bắt buộc. Tức là function có thể có, hoặc không có tham số.
variable: tên biến (tham số của function)
as: từ khóa khai báo kiểu dữ liệu của biến
type: Kiểu dữ liệu của biến
Nếu không khai báo type thì chúng nhận mặc định kiểu any.

[as type]: Kiểu dữ liệu trả về của function, nếu không khai báo thì nó nhận kiểu any.

block code: Khối code chính của function. Có thể đơn giản là một đoạn lệnh, hoặc một query hoàn chỉnh (let ... in), hoặc là hằng giá trị cụ thể.


Ví dụ:

//function1
//ghi chú từng dòng

()=>"Xin chào các bạn!"

/*
function2
Tính tổng 2 số
Ghi chú một đoạn nhiều dòng
*/

(a as number, b as number) as number =>
a + b

/*
function22
Tính tổng 2 số, dùng let ... in
Ghi chú một đoạn nhiều dòng
*/

(a as number, b as number) as number =>
let
    tong = a + b
in
    tong