let
payments = Excel.CurrentWorkbook(){[ Name = "Поступило" ]}[Content],
paymentsGroup = Table.Group ( payments, { "ИНН" }, { { "Поступило", each List.Sum ( [Поступило] ), type number } } ),
paymentsRec = Record.FromList ( paymentsGroup[Поступило], List.Transform ( paymentsGroup[ИНН], Text.From ) ),
fact = Excel.CurrentWorkbook(){[ Name = "Факт" ]}[Content],
typed = Table.TransformColumnTypes (
fact,
{
{ "Дата", type date },
{ "№ СФ", Int64.Type },
{ "ИНН", Int64.Type },
{ "Сумма СФ", type number },
{ "Оплачено", type number },
{ "Долг", type number }
}
),
sort = Table.Sort ( typed, { { "ИНН", Order.Ascending }, { "Дата", Order.Ascending }, { "№ СФ", Order.Ascending } } ),
group = Table.Group (
sort,
{ "ИНН" },
{
{
"recs",
( t ) =>
[
recs = List.Buffer ( Table.ToRecords ( t ) ),
iMax = List.Count ( recs ) - 1,
generate = List.Generate (
() => [
i = 0,
p = Record.FieldOrDefault ( paymentsRec, Text.From ( recs{0}[ИНН] ), 0 ),
s = if i = iMax then p else List.Min ( { recs{i}[Долг], p } ),
result = Record.TransformFields (
recs{i},
{ { "Оплачено", ( x ) => List.Sum ( { x, s } ) }, { "Долг", ( x ) => List.Sum ( { x, - s } ) } }
)
],
each [i] <= iMax,
each [
i = [i] + 1,
p = [p] - [s],
s = if i = iMax then p else List.Min ( { recs{i}[Долг], p } ),
result = Record.TransformFields (
recs{i},
{ { "Оплачено", ( x ) => List.Sum ( { x, s } ) }, { "Долг", ( x ) => List.Sum ( { x, - s } ) } }
)
],
each [result]
)
][generate],
type {record}
}
},
GroupKind.Local
),
toTable = Table.FromRecords ( List.Combine ( group[recs] ), Value.Type ( typed ) )
in
toTable |
