Thực hành Power query sử dụng M-code căn bản trong kế toán

[ptm0412]

Ứng dụng bài số 9 chủ đề Hàm và ngôn ngữ M trong Power query, với file dữ liệu kế toán chuẩn của năm 2008, hãy xem bài thực hành lập sổ quỹ vừa tiền mặt, vừa ngân hàng trong 1 sheet
Sử dụng 2 name ở ô H2 và E3 với H2 chọn giữa tài khoản 111 và 112, E3 chọn tháng, sau đó nhấn refresh
M-Code:

let
    MonthRep= Excel.CurrentWorkbook(){[Name="MonthQuy"]}[Content]{0}[Column1] ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKQuy"]}[Content]{0}[Column1] ,
    Source = Excel.Workbook(File.Contents("E:\Data\ThanhMy\MY BOOK\MCode-PowerQuery\Data-Exercise1.xlsx"), null, true),
    Data_DefinedName = Source{[Item="Data",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"loaict", type text}, {"sct", type text},
         {"diengiai", type text}, {"TkNo", type text}, {"TKCo", type text}, {"Stien", Int64.Type}, {"Serie", type text}, 
         {"hoadon", Int64.Type}, {"ngaygoc", type date}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text}, 
         {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([TkNo] = AcctRep or [TKCo] = AcctRep)and Date.Month([date]) = MonthRep),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "TKDU", each if [TkNo] = AcctRep then [TKCo] else [TkNo]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Thu", each if [TkNo] = AcctRep then [Stien] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Chi", each if [TKCo]=AcctRep then [Stien] else 0),
   
    #"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"loaict", "diengiai", "TkNo", "TKCo", "Stien", "Serie", "hoadon", "ngaygoc", "Msthue", "Mskh", "HD", "LoaiHD", "VATRate", "MaCP"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "sct", "date", "TenKH", "Noidung", "TKDU", "Thu", "Chi"})
in
    #"Reordered Columns"

M-code

 

[ptm0412]

Bài thực hành 8: Tổng hợp công nợ theo tài khoản kế toán
Tương tự bài 7, thực hiện qua 2 bước
- Tính tồn đầu kỳ
- Lập tổng hợp công nợ
Khác biệt là lọc từ ngày đến ngày chứ không theo tháng, và lọc thêm theo tài khoản công nợ

 

[ptm0412]

Bài thực hành 9: Nhập xuất tồn vật tư hàng hoá theo tài khoản và theo ngày bất kỳ
Tương tự bài 8 tổng hợp công nợ, nhưng tính cho 2 cột tồn số lượng và tồn thành tiền, và do tính chất tồn kho nên không có dư bên có

 

[ptm0412]

Bài thực hành 10: Sổ quỹ tiền mặt có thêm cột tồn quỹ luỹ kế
Thực ra bài này bạn @excel_lv1.5 đã hỗ trợ thực hiện trên bài viết #13, bài đó đã có cột tồn quỹ luỹ kế nhưng không giống mẫu quy định của kế toán: Chỉ có tồn quỹ nợ chứ không có tồn bên có, nếu tồn quỹ âm là lỗi kế toán chưa thu đã chi hoặc chi lố. Ngoài ra còn cần 1 ô chứa giá trị tồn quỹ đầu kỳ của tháng trước chuyển sang (ô I6).


Do đó tôi làm lại:
1. Hàm tự tạo FuncTonQuy viết giống như bạn @excel_lv1.5

let
    //Khai báo 2 tham số
    SoduQuy=(Acct as text, mth as number)=>
    let
        Val1=List.PositionOf(SDTK5[SoTK],Acct), // Vị trí của tham số Acct trong list (là cột [SoTK] trong table SDTK5
        Sodu=SDTK5[Duno]{Val1}-SDTK5[Duco]{Val1}, // Lấy giá trị cột [Duno] - giá trị [Duco] tại dòng Val1 = Số dư đầu năm
        Tbl=Table.SelectColumns(Data1,{"date","TkNo","TKCo","Stien"}), // Lấy 4 cột từ bảng Data1, cho vào bảng mới Tbl
        Tbl1=Table.SelectRows(Tbl,each (Date.Month([date])<mth) and ([TkNo] = Acct or [TKCo]=Acct)), // Lọc Tbl 3 điều kiện
        // Thêm cột Sotien = Stien nợ - Stien có:
        #"Added Custom" = Table.AddColumn(Tbl1, "Sotien", each if [TkNo] = Acct then [Stien] else -[Stien],type number),
        // Val2 = Sum( cột mới Sotien) = Tổng phát sinh nợ - có trước kỳ báo cáo + số dư đầu năm = số dư đầu kỳ báo cáo
        Val2=(if Table.RowCount(Tbl1)=0 then 0 else List.Sum(#"Added Custom"[Sotien]))+Sodu
    in
        Val2
in
    SoduQuy

2. Invoke function thành 1 giá trị để dùng trên query chính và load to 2 ô I5:I6 cho báo cáo

let
//gán giá trị 2 tham số cho hàm
    MonthRep= Excel.CurrentWorkbook(){[Name="MonthQuy"]}[Content]{0}[Column1] ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKQuy"]}[Content]{0}[Column1] ,
//dùng hàm FuncTonQuy tính cho 2 giá trị tham số, kết quả là số dư tài khoản quỹ đầu kỳ báo cáo
    Source = FuncTonQuy(AcctRep, MonthRep)
in
    Source

3. Query chính: Tương tự code của bạn @excel_lv1.5

let
    // Khai báo và gán giá trị 2 tham số của query
    MonthRep= Excel.CurrentWorkbook(){[Name="MonthQuy"]}[Content]{0}[Column1] ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKQuy"]}[Content]{0}[Column1] ,
    // Dùng SelectColumns để lấy 1 số cột cần dùng trong Data1, đỡ mất công change type và xoá,
    Source = Table.SelectColumns(Data1,{"sct", "date", "TenKH", "Noidung","TkNo","TKCo","Stien"}),
    // lọc Source 3 điều kiện (trong kỳ báo cáo)
    #"Filtered" = Table.SelectRows(Source, each ([TkNo] = AcctRep or [TKCo] = AcctRep)and Date.Month([date]) = MonthRep),
    // Thêm cột TKDU:
    #"AddedTKDU" = Table.AddColumn(#"Filtered", "TKDU", each if [TkNo] = AcctRep then [TKCo] else [TkNo]),
    // Thêm cột Thu, Chi, cột STT
    #"AddedThu" = Table.AddColumn(#"AddedTKDU", "Thu", each if [TkNo] = AcctRep then [Stien] else 0),
    #"AddedChi" = Table.AddColumn(#"AddedThu", "Chi", each if [TKCo]=AcctRep then [Stien] else 0),
    #"Added Index" = Table.AddIndexColumn(#"AddedChi", "Index", 1, 1, Int64.Type),
    // Thêm cột tồn quỹ (sau thu chi) bằng hàm FuncTonQuy
    AddTonQuy=Table.AddColumn(#"Added Index","Ton", each
        let
            //trích cột [Thu] từ  bảng sau index tạo thành 1 list, trích cột [Chi] từ  bảng sau index tạo thành 1 list
            Lst1=List.Buffer(#"Added Index"[Thu]),
            Lst2=List.Buffer(#"Added Index"[Chi]),
            /* Tính tồn quỹ = TonQuy (tính từ FuncTonQuy) + Sum list thu - Sum list chi
            Chỉ tính sum từ dòng hiện hành trở lên
            Do đã có index là STT nên dùng [Index] làm tham số cho List.FirstN nghĩa là cộng [Index] dòng đầu tiên
            Mỗi dòng có [Index] thay đổi nên Sum thay đổi theo */
            TonQuyNo= TonQuy + (if Table.RowCount(#"Added Index") = 0 then 0 else List.Sum(List.FirstN(Lst1,[Index]))-List.Sum(List.FirstN(Lst2,[Index])))
        in TonQuyNo),


    #"Removed Columns" = Table.RemoveColumns(AddTonQuy,{ "TkNo", "TKCo", "Stien"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "sct", "date", "TenKH", "Noidung", "TKDU", "Thu", "Chi","Ton"})
in
    #"Reordered Columns"

4. Các hàm mới đã dùng: Tôi sẽ bổ sung vào tài liệu hàm M
List.Buffer
List.PositionOf
Table.SelectColumns
Table.RowCount

5. Lưu ý
Khi thay đổi tháng báo cáo phải refresh all hoặc refresh 2 lần: 1 cho ô I6 và 1 cho báo cáo bên dưới

 

[ptm0412]

Bài thực hành 11: Sổ chi tiết tài khoản có 2 cột sô dư cuối luỹ kế: 1 nợ 1 có
Tương tự như bài 10, đây là bài thực hành 2 có thêm 2 cột dư nợ, dư có luỹ kế: Có thể dư 1 trong 2 bên
Đồng thời cần 2 ô số dư đầu kỳ 1 nợ 1 có ở 2 ô H4:I4
1. Hàm tự tạo
Viết như bài 10 nhưng tham số truyền vào là ngày bắt đầu của kỳ báo cáo chứ không phải tháng báo cáo

let
    SoduTK=(Acct as text, StartDate as date)=>
    let
        SoTKCT  =  List.PositionOf(SDTK5[SoTK],Acct),
        Sodu  = SDTK5[Duno]{SoTKCT}-SDTK5[Duco]{SoTKCT},
        Tbl=Table.SelectColumns(Data1,{"date","TkNo","TKCo","Stien"}),
        Tbl1=Table.SelectRows(Tbl,each ([date] < StartDate) and ([TkNo] = Acct or [TKCo]=Acct)),
        #"Added Custom" = Table.AddColumn(Tbl1, "Sotien", each if [TkNo] = Acct then [Stien] else -[Stien],type number),
        Val2= (if Table.RowCount(Tbl1) >0 then List.Sum(#"Added Custom"[Sotien]) else 0) +Sodu
    in
        Val2
in
    SoduTK

2. Invoke function
Cần invoke function thành 2 ô dư nợ và dư có, giải pháp là dùng hàm #table để tạo bảng 2 cột 1 record. Mỗi giá trị là 1 kết quả của hàm, load to H3 để có query ở H3:I4

let
    DateFrom= Date.From( Excel.CurrentWorkbook(){[Name="DFrom"]}[Content]{0}[Column1]) ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKCT"]}[Content]{0}[Column1] ,
    SoduTK = #table({"duNo","duCo"},{{(if FuncSoduTK(AcctRep, DateFrom) > 0 then FuncSoduTK(AcctRep, DateFrom) else 0),
            (if FuncSoduTK(AcctRep, DateFrom) < 0 then Number.Abs(FuncSoduTK(AcctRep, DateFrom)) else 0)}})
in
    SoduTK

3. Query chính
Hai cột dư nợ và dư có luỹ kế lấy kết quả của bảng 2 cột tạo ra từ function bên trên để tính toán (bảng SoduTK)
Truy xuất số dư nợ: SoduTK{0}[duNo]
Truy xuất số dư có: SoduTK{0}[duCo]

    // Cột dư nợ luỹ kế
    AddCkyNo=Table.AddColumn(#"Added Index","CkyNo", each
        let
            Lst1=List.Buffer(#"Added Index"[StienNo]),
            Lst2=List.Buffer(#"Added Index"[StienCo]),
            CuoiKyNo= SoduTK{0}[duNo] -SoduTK{0}[duCo] +List.Sum(List.FirstN(Lst1,[Index]))-List.Sum(List.FirstN(Lst2,[Index]))
        in if CuoiKyNo>0 then CuoiKyNo else 0),
    // Cột dư có luỹ kế
    AddCkyCo=Table.AddColumn(AddCkyNo,"CkyCo", each
        let
            Lst1=List.Buffer(#"Added Index"[StienNo]),
            Lst2=List.Buffer(#"Added Index"[StienCo]),
            CuoiKyCo= SoduTK{0}[duCo] -SoduTK{0}[duNo] +List.Sum(List.FirstN(Lst2,[Index]))-List.Sum(List.FirstN(Lst1,[Index]))
        in if CuoiKyCo>0 then CuoiKyCo else 0),

4. Lưu ý:
Khi thay đổi ngày hoặc tài khoản cần báo cáo, phải refresh all hoặc refresh 2 query, 1 tại H4 và 1 ở bên dưới

 

[ptm0412]

Bài thực hành 13: Sổ chi tiết công nợ đối tác có thêm 2 cột dư nợ/ có luỹ kế
Giống bài 12

 

[Hau151978]

Đặt vấn đề khác: M không có hàm countif, hãy tạo ra hàm countif và countifs (hàm countifs cho phép đếm nhiều điều kiện)

Em làm kiểu lách luật, tạo function giống các tham số như SelectRows, áp dụng cũng dễ:

let
    CountIfs = (Tbl as table, Criteria as function) => 
        let
            Tbl1=Table.SelectRows(Tbl, Criteria),
            Tbl2=Table.RowCount(Tbl1)
        in
            Tbl2
in
    CountIfs

Criteria thì dùng each như bình thường.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", type text}, {"c", Int64.Type}}),
    Custom1 = CountIfs(#"Changed Type",each ([a]>1) and ([b])="y")
in
    Custom1[/coode]

 

[ptm0412]

Bài thực hành 13 (bài cuối cho kế toán): Sổ chi tiết vật tư hàng hoá có cột tồn Số lượng và thành tiền luỹ kế
Tương tự 3 bài trên,
- Function FuncTonVT có thêm tuỳ chọn SL hay TT để tính tồn số lượng hay thành tiền



- Invoke funtion ra 1 bảng 2 cột 1 dòng, load to J4:K5



- Query chính thêm 2 cột tồn luỹ kế:

AddCkySL=Table.AddColumn(AddIndex,"CkySL", each
        let
            Lst1=List.Buffer(AddIndex[SLuongN]),
            Lst2=List.Buffer(AddIndex[SLuongX]),
            CuoiKySL= SoduVT{0}[Số lượng] +List.Sum(List.FirstN(Lst1,[Index]))-List.Sum(List.FirstN(Lst2,[Index]))
        in CuoiKySL),
    AddCkyTT=Table.AddColumn(AddCkySL,"CkyTT", each
        let
            Lst1=List.Buffer(AddIndex[StienN]),
            Lst2=List.Buffer(AddIndex[StienX]),
            CuoiKyTT= SoduVT{0}[Thành tiền] +List.Sum(List.FirstN(Lst1,[Index]))-List.Sum(List.FirstN(Lst2,[Index]))
        in CuoiKyTT),

Ghi chú
- Nếu đổi số tài khoản phải refresh query DMVT
- Nếu đổi ngày báo cáo phải refresh query SoduVT
- Cuối cùng là refresh ChiTietVT

 

[ptm0412]

SƠ KẾT VỀ LOẠT BÀI THỰC HÀNH M CODE TRONG POWER QUERY CHO KẾ TOÁN
- Loạt bài thực hành này khuyến khích người đọc tự viết hoặc chỉnh sửa trực tiếp power query trong cửa sổ lập trình M code (Advanced query editor), tiến lên 1 bước nhỏ ra khỏi việc thao tác nhấn nút kéo thả rồi có sao chịu vậy.

- Các bài thực hành trên đây được viết theo thứ tự từ dễ đến khó nhưng vẫn thuộc phạm vi căn bản cho người mới học M code (tôi cũng mới học). Tôi cũng đã sử dụng những hàm cơ sở để làm gọn các step trong query như: Table.SelectColumns, Table,SelectRows, kết hợp change type trong SelectColumns, kết hợp ChangeColumn name trong unpivot nhằm giảm thiểu số lượng steps.

- Việc viết hàm tự tạo và sử dụng chúng cũng ở bước sơ khởi. Tuy nhiên tôi cũng đã cố gắng (với sự giúp đỡ của bạn @excel_lv1.5) ứng dụng trong phạm vi đơn giản để phù hợp với người mới học.

- Dữ liệu mẫu là dữ liệu chuẩn của 1 file access kế toán nên từ 1 bảng dữ liệu có thể ra nhiều loại báo cáo như vậy. Do đó theo kinh nghiệm của tôi thì chuẩn hoá dữ liệu đầu vào rất quan trọng trong bất kỳ ngành nghề nào.

- Dữ liệu mẫu mới chỉ hơn 600 dòng, nếu nhiều hơn nữa thì tốc độ sẽ chậm và các query mẫu trên đây có lẽ chỉ phù hợp với tối đa 5 ngàn - 10 ngàn dòng (tuỳ máy). Trên nữa là trăm ngàn dòng, triệu dòng sẽ phải viết lại hoặc kết hợp với DAX trong Data model của Power Pivot mới được.

Hy vọng là người đọc có được ít nhất một khái niệm cơ bản về tạo query và hàm thông qua viết M code trong advanced query editor. Cao hơn nữa xin mời theo dõi tiếp chủ đề Thực hành Power query & M code (tổng hợp)