- Tham gia ngày
- 4 Tháng mười một 2007
- Bài viết
- 10,335
- Được thích
- 30,585
- Điểm
- 9,718
- Tuổi
- 59
- Nơi ở
- Gò Vấp
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
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
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"
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
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
// 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),
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ễ:Đặ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)
let
CountIfs = (Tbl as table, Criteria as function) =>
let
Tbl1=Table.SelectRows(Tbl, Criteria),
Tbl2=Table.RowCount(Tbl1)
in
Tbl2
in
CountIfs
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]
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),