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

befaint · 24/8/21

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 · 24/8/21

befaint đã viết:
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 · 24/8/21

Hoàng Trọng Nghĩa đã viết:
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 · 24/8/21

(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 · 24/8/21

befaint đã viết:
(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 · 24/8/21

befaint đã viết:
(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 · 24/8/21

befaint đã viết:
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}

befaint đã viết:
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 · 24/8/21

Cá ngừ F1 đã viết:
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

ptm0412 đã viết:
- 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 · 24/8/21

excel_lv1.5 đã viết:
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:

PHP:

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 · 24/8/21

excel_lv1.5 đã viết:
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.

ptm0412 đã viết:
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...

ptm0412 đã viết:
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 · 24/8/21

befaint đã viết:
Power BI thì ngon quá, có tooltip khi gõ code.

365 cũng có tool tip rồi

befaint đã viết:
Ở 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ó

bi phèn đã viết:
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 · 24/8/21

befaint đã viết:
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 · 24/8/21

excel_lv1.5 đã viết:
Excel cũng có tooltip mà

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

excel_lv1.5 · 24/8/21

befaint đã viết:
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 · 25/8/21

befaint đã viết:
(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 · 25/8/21

Cá ngừ F1 đã viết:
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 · 25/8/21

ptm0412 đã viết:
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 · 25/8/21

Cá ngừ F1 đã viết:
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ụ:

PHP:

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

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

PHP:

/*
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

PHP:

/*
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

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

|||||||||||||

File đính kèm

Chuyên gia GPE

( ͡° ͜ʖ ͡°)

|||||||||||||

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Bad Excel Member

Thành viên tích cực

Bad Excel Member

|||||||||||||

Bad Excel Member

Thành viên tích cực

|||||||||||||

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Bad Excel Member

( ͡° ͜ʖ ͡°)

|||||||||||||

Facebook

Group