Hàm và Ngôn ngữ M trong Power query (căn bản)

[ptm0412]

GIỚI THIỆU
Power query là một công cụ xử lý trích xuất dữ liệu mạnh xuất hiện trong Excel 2016, Excel 2019, Excel 365 và cả Power BI. Công cụ này thực sự mạnh và dễ sử dụng trên giao diện Query Editor, với các bước chuyển đổi, chỉnh sửa dữ liệu trực quan và dễ dàng thao tác.
Khi thao tác các step trong Power query editor, Excel tự biên dịch thành các câu lệnh theo ngôn ngữ M.
Tài liệu này giới thiệu ở mức độ căn bản ngôn ngữ M đã được dùng trong Power query, dịch từ tài liệu tiếng Anh của Microsoft, bổ sung thí dụ thực hành và hình minh hoạ

M Code là gì
M viết tắt của data Mash-up, diễn tả công việc của power query là kết nối tới nhiều loại nguồn dữ liệu khách nhau rồi trộn lẫn nhau. M code chạy đằng sau giao diện người dùng. Khi người dùng thực hiện 1 thao tác chuyển đổi, hiệu chỉnh dữ liệu, Excel diễn dịch thành 1 câu lệnh M tương ứng cho query.
M là một ngôn ngữ dạng hàm (functional language), nghĩa là nó được viết dưới dạng hàm được gọi ra để tính toán (các tham số) và trả về kết quả. M code có 1 thư viện hàm cực kỳ lớn và người dùng lại còn có thể tự định nghĩa.

Viết M code ở đâu
Thanh công thức
Trong tab View của cửa sổ Power query editor, đánh dấu vào Formula bar như hình



Thanh công thức sẽ hiển thị M-code cho 1 bước hiệu chỉnh bên phải (query setting pane), chọn bước nào hiển thị code cho bước đó



Có thể chỉnh sửa trực tiếp trên thanh công thức cho mỗi bước, thí dụ như đổi kiểu dữ liệu trong bước đổi kiểu tự động kề dưới Source:



Hoặc sửa tên trường tạo ra trong bước Unpivot



Hộp thoại Advanced Editor
Là hộp thoại hiển thị toàn bộ các bước tạo và hiệu chỉnh dữ liệu, xuất hiện khi nhấn nút Advanced editor trong tab Home hoặc tab View.
Trên hộp thoại có tên của query đang muốn chỉnh sửa, toàn bộ M-code, và có thông báo kiểm tra cú pháp, cảnh báo lỗi cú pháp nếu có.



Do tính chất là soạn thảo/ chỉnh sửa nâng cao nên gõ trong hộp thoại này không được gợi ý cú pháp mà chỉ cảnh báo nếu có lỗi.

(còn tiếp)

 

[ptm0412]

Tiếp theo
Thư viện hàm M
Thư viện chuẩn
Microsoft có cả 1 thư viện khổng lồ các hàm M. Có thể tải file pdf tại trang web Power Query M Reference của Microsoft.

​

Đây là trọn bộ tất cả hàm M của Power query, mà tôi đã dịch một phần trong chủ đề giới thiệu Power Pivot.
Trong power query editor, trên thanh công thức nhấn nút fx để tạo mới step, gõ = #shared sẽ được danh sách các hàm như sau:

​

Khi click chọn 1 hàm sẽ xuất hiện bên dưới cú pháp gợi ý và cả thí dụ mẫu. Thí dụ hàm Duration.From

​

Nếu nhấn vào chữ function bên phải , sẽ xuất hiện hộp thoại để điền tham số

​

Sau khi điền tham số (15.25) và OK, step invoke function được tạo với kết quả là 15.06:00:00 (15 ngày và 6 giờ)

​

Step được tạo là Invoked FunctionDuration From

​

Các hàm khác có cách tạo tương tự, nếu làm quen có thể gõ trực tiếp trên thanh công thức

Đặc thù hàm M và M-code: Cần phân biệt chữ hoa/ thường
Không như hàm Excel, hàm M và cú pháp câu lệnh M-code yêu cầu phải phân biệt chữ hoa, chữ thường ngay trong cú pháp hàm và phải tuân thủ, nếu không sẽ bị lỗi:

• Tên hàm: Date.Day(), Duration.From, Duration.ToText, #duration(), #date(), …
• Từ khoá: if … then … elseif … else, Invoked FunctionDuration, …
• Khai báo: type text, type number, Int64.Type, …

 

[ptm0412]

Tiếp theo
Biểu thức và giá trị trong Power query
Power query xây dựng hoàn toàn trên biểu thức và giá trị.
Biểu thức là cái gì đó có thể tính toán cho ra 1 giá trị kết quả, thí dụ biểu thức 1+1 sẽ tính ra giá trị kết quả là 2
Một giá trị đơn như trên chỉ là 1 phần nhỏ của dữ liệu. Các giá trị có thể mang các kiểu dữ liệu: số nguyên, số thập phân, … Lưu ý viết đúng chữ hoa, chữ thường

Kiểu​	M type​	Kiểu​	M type​
Số nguyên	Int64-Type	Dạng True/ False	logical
Số thập phân	number	Ngày tháng	date
Tiền tệ	Currency.Type	Ngày giờ	datetime
Dạng phần trăm	Percentage.Type	Giờ phút giây	time
Dạng chuỗi	text	Ngày giờ & múi giờ	datetimezone
		Khoảng thời gian	duration

Các giá trị có thể có cấu trúc phức tạp như list (danh sách), record (bản ghi), table (bảng dữ liệu). Ngoài ra còn có sự kết hợp của 3 cấu trúc trên như list trong list, table trong list, table trong table, … Những cấu trúc kết hợp này thuộc dạng nâng cao.

Giá trị đơn giản
Là các giá trị có cấu trúc đơn giản:

1. 123.45 là 1 giá trị số.
2. “Hello World!” là 1 giá trị chuỗi.
3. true là giá trị logical.
4. null là giá trị rỗng.

Giá trị nội hàm đơn (single intrinsic value)
Sử dụng các hàm nội tại
#time(hours, minutes, seconds)
#date(years, months, days)
#datetime(years, months, days, hours, minutes, seconds)
#datetimezone( years, months, days, hours, minutes, seconds, offset-hours, offset-minutes)
#duration(days, hours, minutes, seconds)

Thí dụ muốn diễn tả ngày 31 tháng 12 năm 2020, cần sử dụng hàm #date(2020, 12, 31)
Muốn so sánh khoảng thời gian giữa 2 ngày bắt đầu và kết thúc với 30 ngày, không so sánh trực tiếp [DateTo] – [DateFrom] với 30 mà phải so sánh với #duration(30, 0, 0, 0)

Giá trị có cấu trúc
List
Định nghĩa
Là một cấu trúc danh sách có thứ tự sắp xếp. Có thể định nghĩa 1 list trong một cặp dấu ngoặc nhọn {}. Thí dụ {1, 2, 3} là 1 list chứa 3 giá trị đơn 1, 2, 3 theo đúng thứ tự. Lưu ý là List có tính chất sắp xếp thứ tự nên List {1, 2, 3} sẽ không giống list {2, 3, 1}
Thứ tự sắp xếp của các thành phần trong list bắt đầu từ 0.

​

Một list có thể chứa 2 hoặc nhiều list khác, mỗi list nằm trong 1 cặp {} và cách nhau bởi dấu phẩy, thí dụ {{“ptm”, “OverAC”, “HLMT”}, {1, 2, 3}}.

​

Có thể lấy 1 list thành phần trong List cha bằng câu lệnh = Source{0} hoặc =Source{1}. Lưu ý là chỉ số thứ tự của thành phần bên trong list bắt đầu bằng 0

​

Tạo List nhiều thành phần có thứ tự, hoặc list rỗng
Có thể tạo 1 list bằng định dạng {x..y} để viết tắt. Thí dụ ={2..5} sẽ tạo list 4 thành phần 2, 3, 4, 5. Định dạng này áp dụng cho cả ký tự chuỗi thí dụ ={“a”..”f”} cho ra list có 6 thành phần a, b, c, d, e, f.

Cũng có thể tạo 1 list rỗng (không có thành phần nào bằng câu lệnh ={}

​

Truy xuất giá trị thứ n của list
Giả sử ta có 1 list Source2 có 5 thành phần, muốn lấy giá trị thứ 4 của list thì ta viết =Source2{3}, do 3 là thứ tự bắt đầu từ 0 của 4, trong thí dụ list {“a”..”f”} sẽ có kết quả “d”

​

Nếu là List trong list như ở trên {{“ptm”, “OverAC”, “HLMT”}, {1, 2, 3}},

tham chiếu = Source{0}{1} sẽ cho giá trị “OverAC”

​

Ứng dụng
Có thể tạo ra những list để sử dụng bằng cách merge với query chính như list năm, list tháng, list kho hàng, list nhân viên bán hàng …

 

[ptm0412]

Tiếp theo
Record
Định nghĩa
Record là 1 dạng cấu trúc có sắp xếp của trường dữ liệu, thứ tự sắp xếp luôn bắt đầu bằng 0. Mỗi trường dữ liệu có tên trường (duy nhất) và 1 giá trị kèm theo. Giá trị có thể là giá trị null
Record được khai báo trong cặp ngoặc vuông […] và có cú pháp:
= [FieldName1 = Value1, FieldName2 = Value2]

Thí dụ = [Nick = “ptm0412”, Name = “Phạm Thành Mỹ”, Age = 59, Post = 9955]

​

Record trong record


Có thể khai báo 1 record chính trong đó có chứa các record phụ bằng cách lồng các cặp dấu […], mỗi record phụ có tên và có dấu bằng, Thí dụ:
= [ Member1 =[Nick = "ptm0412", Name = "Phạm Thành Mỹ", Age = 59, Post = 9955],Member2=[Nick="OverAC",Name="Đỗ Nguyên Bình", Age=40,Post= 2628] ]

[B]Truy xuất giá trị trong record[/B]
Có thể truy xuất 1 trường của 1 record bằng cú pháp:

=RecordName[FieldName]

Thí dụ với Record name là Custom4 = [Nick = “ptm0412”, Name = “Phạm Thành Mỹ”, Age = 59, Post = 9955]

Thì truy xuất Age bằng cách viết:

= Custom4[Age]
Kết quả là 59
[ATTACH type="full" alt="1604671747973.png"]248818[/ATTACH]


Truy xuất giá trị 1 trường của record con
Trong thí dụ record trong record 3.4.2.2, để lấy trường Post của record thứ 2 có tên là Member2 bên trong record Custom3 ta viết:

= Custom3[Member2][Post]

​

Table
Định nghĩa
Table là tập hợp các dòng, mỗi dòng là 1 List, các dòng được sắp thứ tự bắt đầu bằng 0. Table chỉ có thể được tạo từ hàm nội tại, thí dụ như hàm #table()
Cú pháp:
#table({“Header1”, “Header2”, …},{{Value11,Value12},{Value21,Value22},{..},…}})
Thí dụ tạo 1 bảng 3 dòng 3 cột:

= #table({"Nick","Name","Post"},{{"OverAC","Đỗ Nguyên Bình",2628 },{"ptm0412","Phạm Thành Mỹ",9955},{"HLMT","Lê Phát Đởm",7685}})

​

Truy xuất giá trị
Nếu Custom7 là tên của table vừa tạo, muốn lấy số lượng post (cột 3) của dòng 2 (thứ tự dòng = 1) thì viết:
=Custom7{1}[Post]
Kết quả là 9955

Còn tiếp​

 

[ptm0412]

Tiếp theo
Biểu thức
Định nghĩa
Biểu thức là tất cả những gì có thể tính toán trả về 1 giá trị. Bản thân 1 giá trị đơn cũng là 1 biểu thức (đơn thức), thí dụ đơn thức 1 có giá trị 1. Biểu thức có thể bao gồm các giá trị và cũng có thể bao gồm các hàm phức hợp.

Thí dụ:

• Biểu thức 1 + 1 trả về giá trị 2
• Biểu thức 3 > 2 trả về giá trị true
• Biểu thức “Diễn đàn “ & “GPE” trả về giá trị “Diễn đàn GPE”
• Biểu thức Text.Upper(“ptm”) trả về giá trị “PTM”

Các toán tử trong biểu thức
Cùng với các toán tử thông thường, M-code còn có 1 danh sách các hàm đặc biệt được coi là những toán tử. Những toán tử này kết hợp hai giá trị và trả về 1 giá trị đơn theo cách mà hàm đó hoạt động.

Toán tử số học
Gồm các toán tử cộng trừ nhân chia như Excel (+, -, *, /). Các toán tử này không chỉ tính toán cho dữ liệu kiểu số mà còn có thể tính toán cho kiểu ngày tháng hoặc kiểu ngày giờ. Thí dụ như ngày cộng với 1 khoảng thời gian

#date(2020,10,1) + #duration(3,0,0,0) sẽ tương đương ngày 04/10/2020

Toán tử luỹ thừa:
Dùng hàm Number.Power(number, power) as number
Có thể dùng hàm này để tính căn số bậc 2, 3, n
Thí dụ

• Number.Power(2, 10) = 1024
• Number.Power(4, 0.5) = 2 (căn bậc 2)
• Number.Power(8, 1/3) = 2 (căn bậc 3)

Toán tử so sánh
Gồm các toán tử <, >, <=, >=, =, =, <>
So sánh các giá trị sẽ trả về giá trị true/ false
Cũng có thể so sánh 2 list với nhau với toán tử = như sau:
Biểu thức {1, 2, 3, 4} = {2, 3, 4} sẽ trả về giá trị false

Toán tử nối và hợp nhất
Là toán tử &, dùng để nối chuỗi, hợp nhất 2 hoặc nhiều list, nhiều record, hoặc nhiều table

Biểu thức “Diễn đàn “ & “GPE” trả về kết quả “Diễn đàn GPE”
Biểu thức {1, 2, 3} & {3, 4, 5} trả về list hợp nhất có 6 phần tử {1, 2, 3, 3, 4, 5}
Biểu thức nối records [ a = 1 ] & [ b = 2 ] trả về record hợp nhất có 2 field, tương đương [a= 1, b= 2]
Thí dụ nối 2 records

​

Toán tử logic
Tương tự như VBA Excel bao gồm 3 toán tử and, or, not (viết thường), kết quả trả về dạng boolean (true/ false)

Ghi chú (Commenting) Code
Như các ngôn ngữ lập trình khác, M-code cho phép ghi chú trong code. Có 2 loại comment

Ghi chú 1 dòng
Ghi chú trên cùng dòng code hoặc riêng 1 dòng, sử dụng 2 ký tự //
Thí dụ

M code here
M code here //This code line use for case 1
M code here //This code line use for case 2
//Case else
M code here

Ghi chú nhiều dòng
Nếu ghi chú dài phải xuống dòng, thì đoạn ghi chú bỏ trong cặp ký tự /* và */

M code goes here /*This is a comment
on multiple lines*/
M code goes here

Phát biểu let … in
Phát biểu let (viết thường) cho phép tính toán 1 số các giá trị hoặc biến, sau đó gán vào 1 biểu thức hoặc 1 biến đứng sau từ khoá in

let
a = 1,
b = 2,
c = a + b
in
c

Biểu thức trên đây bao gồm 3 biểu thức đứng sau từ khoá let, mỗi biểu thức cách nhau bởi 1 dấu phẩy ngoại trừ biểu thức cuối đứng trước từ khoá in. Toàn bộ biểu thức let … in này trả về giá trị 3.

Các biểu thức bên trong let … in không nhất thiết phải sắp đúng thứ tự tính toán, biểu thức sau cũng cho về giá trị 3 mặc dù thứ tự khác bên trên, miễn là sau in là c chứ không phải a hay b. Đó là do M-code tự động tính toán dựa vào sự phụ thuộc tính toán của các biểu thức thành phần.

let
c = a + b,
b = 2,
a = 1
in
c

Cũng có thể viết gọn lại như sau:

let
a = 1,
b = 2
in
a + b

 

[ptm0412]

Tiếp theo
Phát biểu each – if then else – try otherwise
Phát biểu each
Phát biểu each (viết thường) là 1 cách ngắn gọn để tạo cột bao gồm các giá trị định sẵn hoặc tính toán.
Thí dụ tạo 1 cột mới có giá trị bằng cột cũ nhân 2:

let
    Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}),
    #"Added Custom" = Table.AddColumn(Source, "Double", each 2*[Numbers])
in
    #"Added Custom"

Phát biểu if then else
M-code không có cấu trúc select case hoặc vòng lặp mà chỉ có phát biểu if

if [logical expression to test]
then [do this when true]
else [do this when false]

Thí dụ
= Table.AddColumn(#"Changed Type", "Custom", each if [rev] < 3 then "Fail" else "Pass")

​

Nếu 2 hoặc nhiều điều kiện thì dùng thêm else if, thí dụ:

​

Phát biểu try otherwise
Là phát biểu nhằm loại trừ lỗi. Trong tính toán có trường hợp dữ liệu sinh ra lỗi như cộng trừ với text, chia cho zero, …
Thí dụ ta có 2 cột Bonus1 và Bonus2 không hoàn toàn là số, và cột tính tổng TotalBonus sẽ bị lỗi ở các dòng chứa text

​

Câu lệnh M-code sử dụng try … otherwise là:

= Table.AddColumn(#"Added Conditional Column3", "Total bonus", each try [Bonus1]+[Bonus2] otherwise "fail")

Còn tiếp​

 

[ptm0412]

Tiếp theo​

Đặt tên (Name) và sử dụng Name
Đặt 1 name
Có thể đặt 1 name với cú pháp sau trong Advanced Editor: Bắt đầu bởi # rồi tới tên của Name, rồi tới giá trị của name. Tất cả đặt trong 1 phát biểu let … in
Name có thể đặt với khoảng trắng, ký tự đặc biệt và phải bỏ trong cặp dấu “”

let
    #"MyName" = any
in
    #"MyName”

Thí dụ:

​

Kết quả là 1 query với duy nhất 1 step, và 1 giá trị đơn

​

Cũng có thể gán giá trị cho Name là 1 List, 1 record, 1 table tuỳ theo nhu cầu sử dụng.

​

Sử dụng name để lọc
Thí dụ mẫu
Ta có bảng dữ liệu sau:

​

Và mong muốn lọc dữ liệu theo AccNo = 1000011 hoặc 1000012
Tạo 1 name là Acct1 = 1000011 và 1 name là Acct2 = 1000012:

​

Tạo query đơn giản từ bảng Data, lọc cột Acct = 1000011 và 1000012

​

Vào Advanced editor

​

Thấy đoạn M-code như sau:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"AccNo", Int64.Type}, {"AccName", type text}, {"Input", Int64.Type}, {"Output", Int64.Type}, {"Branch", type text}, {"Department", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([AccNo] = 1000011 or [AccNo] = 1000012))
in
#"Filtered Rows"

Sửa lại bằng 2 names đã đặt

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"AccNo", Int64.Type}, {"AccName", type text}, {"Input", Int64.Type}, {"Output", Int64.Type}, {"Branch", type text}, {"Department", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([AccNo] = Acct1 or [AccNo] = Act2))
in
#"Filtered Rows"

Kết quả:

Còn tiếp​

 

[ptm0412]

Tiếp theo
Đặt biến lấy từ Name trên sheet Excel
Đặt name trực tiếp như trên để lọc dữ liệu chỉ nên áp dụng khi điều kiện lọc là cố định. Khi điều kiện lọc là thay đổi theo yêu cầu báo cáo, hoặc cần dùng name động để làm biến cho query thì cần phải dễ thao tác chứ không phải mở query editor lên sửa. Cách tốt nhất là có 1 query lấy từ sheet của excel, khi cần thì thay đổi trên sheet cho dễ.
Cách đặt biến trong query
Giả sử ta có 2 ô chứa giá trị thay đổi J2, J3 và muốn tham gia vào query, 2 ô này đã đặt name range là FName và ShName

​

Ngay dưới let của query editor, khai báo 2 name fname và dataname của query tương ứng 2 name range với cú pháp:

let
fname = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1] ,
dataname = Excel.CurrentWorkbook(){[Name="ShName"]}[Content]{0}[Column1] ,
Source = …

Trong đó:

• {[Name="FName"]} là Name range Fname
• [Content] là từ khoá lấy giá trị của name, lúc này giá trị của name là 1 table của query dù cho chỉ có 1 dòng và 1 cột, không có tiêu đề
• {0} là lấy phần tử dòng thứ 1
• [Column1] là lấy cột thứ nhất do table không có tiêu đề. Nếu có tiêu đề thì xem lại mục 3.3.3.2 bên trên.

​

Ứng dụng
Thí dụ ứng dụng 1
Dùng trong trường hợp lấy dữ liệu từ file excel khác và vô tình hoặc cố tình sửa tên file, di dời, sửa tên table, tên sheet dữ liệu nguồn, hoặc dời thư mục dữ liệu nguồn.
Tạo name
Điền tên đầy đủ và tên bảng hoặc tên sheet vào 2 ô, đặt 2 name

​

Tạo query from workbook
Tạo query from workbook và mở advanced editor

Ta có các câu lệnh:

let
Source = Excel.Workbook(File.Contents("E:\Data\ThanhMy\MY BOOK\Pivot2019-PowerPivot\Final\Combine\CN-CT.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Code", type text}, {"Qty", Int64.Type}, {"Amount", Int64.Type}})
in
#"Changed Type"

Thêm biến (name) và sửa thông số ban đầu thành biến

let
fname= Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1] ,
dataname = Excel.CurrentWorkbook(){[Name="ShName"]}[Content]{0}[Column1] ,
Source = Excel.Workbook(File.Contents(fname), null, true),
Data_Sheet = Source{[Item=dataname,Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Code", type text}, {"Qty", Int64.Type}, {"Amount", Int64.Type}})
in
#"Changed Type"

Kết quả

• Kết quả tạo query

​

• Sửa tên file nguồn và/ hoặc di dời sang thư mục khác

​

Khi refresh, không bị lỗi

• Chọn file khác cùng cấu trúc (chi nhánh HN)

​

Khi refresh lấy được dữ liệu mới

Còn tiếp​

 

[ptm0412]

Tiếp theo (Đặt biến lấy từ Name trên sheet Excel)
Thí dụ ứng dụng 2
Dữ liệu và yêu cầu
Giả sử với dữ liệu 50.000 dòng như sau:

​

Cần báo cáo động với tuỳ chọn từ ngày đến ngày tuỳ ý, số tiền từ x đến y tuỳ ý, và tuỳ chọn lọc theo tiền vào hoặc tiền ra như sau:

​

Yêu cầu: người dùng chọn ngày bắt đầu, ngày kết thúc tuỳ ý, chọn số tiền từ và đến tuỳ ý, chọn 1 trong 2 mục Input, Output trong combobox Loại giao dịch

Đặt name cho các tham số

​

AmountFrom	=Param1!$C$3
AmountTo	=Param1!$F$3
DateFrom	=Param1!$C$2
DateTo	=Param1!$F$2
IsInput	=Param1!$G$4
TransType	=Data!$J$1:$J$2

Tạo query cho dữ liệu chính

​

Lọc trường Date (between)

• Chọn between:

​

• Chọn ngày bắt đầu và kết thúc, giá trị bất kỳ

​

Được 1 step với câu lệnh trên formula bar:

= Table.SelectRows(#"Changed Type", each [Date] >= #date(2020, 1, 1)
    and [Date] <= #date(2020, 1, 31))

Lọc trường Input (between)
Tương tự lọc ngày tháng, lọc số tiền cột Input với 2 số tuỳ ý

​

Kết quả

= Table.SelectRows(#"Filtered Rows", each [Input] >= 10000000 and [Input] <= 20000000)

Tạo và gắn biến

• Vào advanced editor

​

Thấy các dòng lệnh sau:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"AccNo", Int64.Type}, {"AccName", type text}, {"Input", Int64.Type}, {"Output", Int64.Type}, {"Branch", type text}, {"Department", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(2020, 1, 1) and [Date] <= #date(2020, 1, 31)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Input] >= 10000000 and [Input] <= 20000000) in #"Filtered Rows1"

• Thêm vào 5 khai báo parameters và sửa 4 thông số tuỳ ý lúc nãy thành parameter.

​

Sửa tiếp điều kiện lọc Input hay Output:
Sửa câu lệnh với điều kiện If then else thành như sau:

​

​

Kết quả
Lọc Input

​

Lọc output

​

Còn tiếp

 

[ptm0412]

Tiếp theo
Hàm tự tạo
Khái niệm & cú pháp
Hàm là một đoạn code kết nối, tính toán các tham số đầu vào để trả về 1 giá trị đầu ra. Ngoài các hàm trong thư viện, người dùng có thể tự tạo hàm bằng M-code. Cú pháp 1 hàm đơn giản:
Khai báo 1 hàm với các tham số, tiếp theo là dấu mũi tên =>, rồi phương pháp tính từ đối số
Kết quả là truyền tham số thực vào hàm đển hàm tính toán. Các khai báo nằm trong cặp phát biểu let … in. Thí dụ hàm nhân 2 đối số, truyền tham số 2 và 3, kết quả là 6

let
   Product = (x,y) => x * y,
   Result = Product(2,3)
in
Result

Hàm với tham số tuỳ chọn (optional)
Có 2 loại tham số hàm:

• Tham số bắt buộc: Bắt buộc phải truyền tham số này vào hàm, nếu không thì bị lỗi
• Tham số tuỳ chọn: Tham số có thể truyền giá trị vào hàm và cũng có thể không. Trường hợp bỏ trống tham số tuỳ chọn không truyền vào hàm, tham số này sẽ nhận giá trị null.

Trường hợp tham số null thì phải dùng phát biểu if then else để xử lý

let
   Product = (x, optional y) => if y is null then x else x * y,
   Result = Product(2)
in
Result

Kết quả 2 là tham số x, do tham số y bỏ trống

Hàm đệ quy
Hàm đệ quy là hàm gọi lại chính nó. Trong M-code khi gọi lại chính nó phải dùng ký tự khoá @ đứng trước tên hàm
Thí dụ 1
Hàm tính giá trị thứ n bằng tổng 2 giá trị (n -1) và (n-2) (hàm Fibonacci)
Nếu n = 1 hoặc n = 2, hàm Fibonacci có giá trị 1
Nếu n = 3, hàm Fibonacci có giá trị 1 + 1 = 2
Nếu n = 4, hàm Fibonacci có giá trị 2 + 1 = 3
…
Nếu n = 7, hàm Fibonacci có giá trị 5 + 8 = 13

let
Fibonacci = (n) =>
   if n = 1  then 1
   else
        if n = 2 then 1
        else @Fibonacci(n-1) + @Fibonacci(n-2),
Result = Fibonacci(7)
in
Result

Thí dụ 2: Hàm tính giai thừa (Factorial)
Giai thừa của số n là giá trị của 1 x 2 x 3 x … x n, hoặc giai thừa của (n - 1) nhân n

let
Factorial = (n) =>
   if n = 0 or n = 1 then 1
   else
   @Factorial(n-1) * n,
Result = Factorial(7)
in
Result

Kết quả của hàm là 5.040

HẾT​

Tổng kết
Power query là 1 công cụ rất mạnh mẽ trong Excel và trong Power BI, dùng để lấy và xử lý dữ liệu từ rất nhiều nguồn khác nhau. Chúng ta có thể sử dụng Power query bằng cách sử dụng menu (ribbon) trên giao diện Excel cũng như giao diện Query editor một cách trực quan từng bước. Tuy nhiên so sánh với lập trình VBA của Excel thì làm như vậy mới chỉ dừng ở mức record macro.

Tiến lên một bước nữa với ngôn ngữ M, ta có thể chỉnh sửa M-Code theo ý muốn, thậm chí viết ra hẳn những query riêng biệt cho mình không qua thao tác có sẵn của giao diện.

Mọi ngôn ngữ lập trình đều cần phải có những hiểu biết căn bản của ngôn ngữ đó, và tài liệu này phần nào đáp ứng những kiến thức căn bản nhất của ngôn ngữ M.

Do đây chỉ là tài liệu lý thuyết nên khi đọc sẽ cảm thấy khô khan vì thiếu nhiều những thí dụ minh hoạ, tuy nhiên người đọc sẽ cần phải nắm vững lý thuyết và ứng dụng vào thực hành rất nhiều mới có thể sử dụng thành thạo ngôn ngữ này.

Cụ thể như mục viết hàm và nhất là hàm đệ quy, phải có được bài toán cụ thể, phù hợp thì mới ứng dụng được, còn không thì mới chỉ là lý thuyết suông.
Mong rằng tài liệu ngắn ngủi này mang lại chút kiến thức nhỏ bé cho các bạn nào cần dùng.

Xin cám ơn. Tôi sẽ đính kèm file eBook sau khi hoàn thiện
Đã đính kèm file pdf