Power query & M code (tổng hợp và nâng cao)

[Hau151978]

Nhân tiện có bài điền số từ 1 đến 10 triệu, mặc dù m code có thể xử lý table 1E7 dòng nhưng không ghi ra sheet được, vì vậy em sẽ tạo bảng 1E6 dòng x 10 cột. Do chỉ có 10 cột nên có thể dễ dàng tạo bảng bằng Table.FromColumns nhưng ở đây em sử dụng List.Accumulate để minh họa tương tự vòng lặp For:

let
    Source = Table.FromList({1..1000000},Splitter.SplitByNothing(),{"ABC"}),
    Loop = List.Accumulate({1..9}, Source, (state,current) => Table.AddColumn(state, "NewCol" & Number.ToText(current), each Record.Field(_, Table.ColumnNames(state){current-1})+1000000))
in
    Loop

Giải thích:
- Lệnh 1 tạo table Source từ list {1..1000000} có 1 column là ABC.
- Lệnh List.Accumulate có cấu trúc:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Lệnh này sẽ thực hiện n lần (n là số phần tử của tham số list); seed là tham số khởi tạo ban đầu; accumulator là 1 function nhận 2 tham số thường đặt tên là state và current, ta có thể lấy tên khác cũng được. Bắt đầu vòng lặp, state = seed, current = list{0}. Qua mỗi lần lặp, kết quả của accumulator sẽ được gán cho state, current nhận giá trị kế tiếp trong list, kết quả cuối cùng chính là kết quả của accumulator lần cuối. Ví dụ lệnh sau = List.Accumulate({"B","C","D"}, "A", (state,current)=> state & current) sẽ ra kết quả ABCD.
Như vậy sau khi đã có table Source rồi ta gán seed=Source và lặp 9 lần, mỗi vòng lặp ta sẽ Add column = 1E6 + cột cuối của bảng trước. Lệnh Table.ColumnNames trả về list các column theo thứ tự, Table.ColumnNames(state){current-1} trả về cột có thứ tự current-1, ở vòng lặp đầu tiên thì state có 1 cột, current=1 nên Table.ColumnNames(state){current-1} sẽ trả về cột duy nhất của bảng Source do phần tử list bắt đầu đánh số từ 0.

 

[excel_lv1.5]

Nhân tiện có bài điền số từ 1 đến 10 triệu, mặc dù m code có thể xử lý table 1E7 dòng nhưng không ghi ra sheet được, vì vậy em sẽ tạo bảng 1E6 dòng x 10 cột. Do chỉ có 10 cột nên có thể dễ dàng tạo bảng bằng Table.FromColumns nhưng ở đây em sử dụng List.Accumulate để minh họa tương tự vòng lặp For:

let
    Source = Table.FromList({1..1000000},Splitter.SplitByNothing(),{"ABC"}),
    Loop = List.Accumulate({1..9}, Source, (state,current) => Table.AddColumn(state, "NewCol" & Number.ToText(current), each Record.Field(_, Table.ColumnNames(state){current-1})+1000000))
in
    Loop

Giải thích:
- Lệnh 1 tạo table Source từ list {1..1000000} có 1 column là ABC.
- Lệnh List.Accumulate có cấu trúc:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Lệnh này sẽ thực hiện n lần (n là số phần tử của tham số list); seed là tham số khởi tạo ban đầu; accumulator là 1 function nhận 2 tham số thường đặt tên là state và current, ta có thể lấy tên khác cũng được. Bắt đầu vòng lặp, state = seed, current = list{0}. Qua mỗi lần lặp, kết quả của accumulator sẽ được gán cho state, current nhận giá trị kế tiếp trong list, kết quả cuối cùng chính là kết quả của accumulator lần cuối. Ví dụ lệnh sau = List.Accumulate({"B","C","D"}, "A", (state,current)=> state & current) sẽ ra kết quả ABCD.
Như vậy sau khi đã có table Source rồi ta gán seed=Source và lặp 9 lần, mỗi vòng lặp ta sẽ Add column = 1E6 + cột cuối của bảng trước. Lệnh Table.ColumnNames trả về list các column theo thứ tự, Table.ColumnNames(state){current-1} trả về cột có thứ tự current-1, ở vòng lặp đầu tiên thì state có 1 cột, current=1 nên Table.ColumnNames(state){current-1} sẽ trả về cột duy nhất của bảng Source do phần tử list bắt đầu đánh số từ 0.

List.Accumulate có thể xem như là for next và đệ quy, một cách viết khác

let
    Tbl1=Table.FromColumns(List.Accumulate({1..9},{{1..1000000}},(s,c)=>s&{{c*1000000+1..(c+1)*1000000}}))
in
    Tbl1

Sẵn ví dụ bạn nói về loop ngoài List.Accumulate theo tôi biết còn 2 cách sau:
- Sử dụng List.Transform cho for next (thường kết hợp thêm List.Positions để xác định vị trí)

let
    for_next2=Table.FromColumns(List.Transform({0..9},(i)=>{i*1000000+1..(i+1)*1000000}))
in
    for_next2

Ví dụ cộng 2 List:

let
    Lst1={1..10},
    Lst2={11..20},
    Lst3=List.Transform(List.Positions(Lst1),(i)=>Lst1{i}+Lst2{i})
in
    Lst3

- Sử dụng List.Generate như do while, for next và đệ quy :

let
    do_while=Table.FromColumns(List.Generate(()=>{1..1000000},(lst)=>List.Last(lst)<=10000000,(lst)=>{List.Last(lst)+1..List.Last(lst)+1000000}))
in
    do_while

 

[ptm0412]

- Sử dụng List.Generate như do while, for next và đệ quy :

let
    do_while=Table.FromColumns(List.Generate(()=>{1..1000000},(lst)=>List.Last(lst)<=10000000,(lst)=>{List.Last(lst)+1..List.Last(lst)+1000000}))
in
    do_while

Nhờ @excel_lv1.5 giải thích giùm vụ đệ quy trong hàm trên, hoặc cho thí dụ đơn giản hơn 1 chút, xin cám ơn

 

[excel_lv1.5]

Nhờ @excel_lv1.5 giải thích giùm vụ đệ quy trong hàm trên, hoặc cho thí dụ đơn giản hơn 1 chút, xin cám ơn

List.Generate và List.Accumulate giống nhau nhưng List.Accumulate trả về kết quả cuối cùng (any) có thể tùy biến kết trả về là List, còn List.Generate trả về kết quả của từng vòng lặp (any) và đưa vào List
Tôi đưa một ví dụ đơn giản hơn , tạo một List 2 lũy thừa với điều kiện 2 lũy thừa <3000, bài này dùng List.Transform, List.Accumulate, List.Generate đều được , nhưng List.Transform, List.Accumulate phải xác định được giá trị cuối cùng, như 2 lũy thừa <3000 thì giá trị cuối cùng là 2^11

let
    Lst = List.Transform({0..11},(i)=>Number.Power(2,i)),
    Lst2=List.Accumulate({1..11},{1},(s,c)=> s&{Number.Power(2,c)})
in
    Lst2

Còn sử dụng List.Generate thì không cần xác định giá trị cuối

let
    Lst=List.Generate(()=>1,(n)=>n<=3000,(n)=>n*2),
    Lst2=List.Generate(()=>1,each _<=3000,each _*2)
in
    Lst2

Hàm List.Accumulate bạn Hau151978 đã giải thích, tôi giải thích hàm List.Generate
Cấu trúc
List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list
Có thể hiểu là
List.Generate(()=>giá trị ban đầu (any), điều kiện dừng, kết quả trả về của từng vòng lặp) kết quả trả về là List

Giải thích hàm Lst=List.Generate(()=>1,(n)=>n<=3000,(n)=>n*2):
- Giá trị ban đầu là 1,điều kiện dừng kết quả phải nhỏ hơn 3000, kết quả trả về n*2 (đây là đệ quy n là kết quả của vòng lặp trước)
Vòng lặp 1: n*2=1*2= 2 (n là giá trị ban đầu =1) <3000=>
Vòng lặp 2: n*2=2*2= 4 (n là kết quả của vòng lặp trước là 2) <3000=>
Vòng lặp 3: n*2=4*2= 8 (n là kết quả của vòng lặp trước là 4) <3000=>
Vòng lặp 4: n*2=8*2= 16 (n là kết quả của vòng lặp trước là 8) <3000=>
.......
Vòng lặp 11: n*2=1024*2= 2048 (n là kết quả của vòng lặp trước là 1024) <3000=>
Vòng lặp 11: n*2=2048 *2= 4096 (n là kết quả của vòng lặp trước là 1024) >3000=>dừng
Ở trên tôi viết 2 cách đều trả kết quả như nhau, List.Generate(()=>1,each _<=3000,each _*2) , hàm này như trong learning path của Microsoft, dấu "_" có thể hiểu như là n ở hàm 1 (kết quả về của từng vòng lặp) nếu sử dụng "_" bắt buộc phải khai báo thêm each, trong trường hợp sử dụng loop trong loop thì sẽ không phân biệt được dấu "_" của loop nào, vì vậy nên đặt tên như cấu trúc của hàm 1

 

[VetMini]

Nhờ @excel_lv1.5 giải thích giùm vụ đệ quy trong hàm trên, hoặc cho thí dụ đơn giản hơn 1 chút, xin cám ơn

Bạn thấy cái dấu => không?
Trong lý thuyết lập trình, dấu ấy tượng trưng cho lambda expression.
MS dùng nó trong Linq. Vì M code sử dụng nhiều thứ bắt nguồn từ Linq cho nên cũng sử dụng lambda tương tự.
Nếu bạn sử dụng Power Query nhiều thì nên tìm đọc tài kiệu về Linq và lambda.

 

[Hau151978]

Ở bài trước, bác Excel_lv1500 đã nêu ví dụ và giải thích về List.Generate. Bài này, mình đưa thêm ví dụ sử dụng tham số selector để dễ dàng triển khai vòng lặp Do Loop. Ví dụ để tính tổng các phần tử của mảng arr() là số chẵn, trong VBA, ta có thể sử dụng cấu trúc:

i=1
n=ubound(arr)
sum=0
Do
If arr(i) mod 2 = 0 then sum=sum+arr(i)
if i<n then i=i+1 else exit do
Loop

Đối với m code, ta có thể sử dụng List.Generate như sau

//Cộng dồn vào biến sum
let
    arr = {1,2,4,6,5,7,8,9,10,10},
    lst=List.Generate(()=>[i=0,sum=0],each [i]<=List.Count(arr),
                each [i=[i]+1,sum=[sum]+ (if Number.IsEven(arr{[i]}) then arr{[i]} else 0)],each [sum]),
    kq=List.Last(lst)
in
    kq

Hoặc:

let
    arr = {1,2,4,6,5,7,8,9,10,10},
    lst=List.Generate(()=>[i=0,k=0],each [i]<=List.Count(arr),
                each [i=[i]+1,k=if Number.IsEven(arr{[i]}) then arr{[i]} else 0],each [k]),
    kq=List.Sum(lst)
in
    kq

Tại mỗi vòng lặp, code tạo ra record gồm 2 field i và sum (hay i và k ở cách 2); parameter selector là hàm với đầu vào là record này, đầu ra chỉ lấy sum (hay k) để đưa vào list. List tạo ra sẽ có nhiều hơn arr 1 phần tử do ban đầu i=0, sum=0, nếu muốn list tạo ra có số phần tử giống arr thì cần xét điều kiện arr{0} chẵn ngay từ parameter initial nhưng lệnh sẽ dài hơn.

 

[Hau151978]

Ở ví dụ trên, ta có thể sử dụng List.Accumulate vì số lần lặp là cố định. Tuy nhiên vì List.Accumulate không có điều kiện dừng vòng lặp giữa chừng (như Exit For trong VBA) nên nếu cần tính tổng các số chẵn đầu tiên của arr, gặp số lẻ sẽ thoát vòng lặp thì dùng List.Generate sẽ nhanh hơn:

let
     arr={2,4,6},
     lst= if not Number.IsEven(arr{0}) then {0} else
            List.Generate(()=>[i=0,sum=arr{0}],each [i]<List.Count(arr) and Number.IsEven(arr{[i]}), 
                each [i=[i]+1,sum=[sum]+ arr{[i]+1}],each [sum]),
    kq= List.Last(lst)
in
    kq

 

[DuongLeHung]

Khơi khó hiểu, nhưng mà hay quá