按产品编号，拆分发生额问题

ljxxw

请教各位老师：



不同产品的发生明细，想用某个固定数拆分列式如下：
详见附件


用函数公式、或者Power Query实现都行。

静听溪流

=MIN(C2,E2-SUMIF(A$1:A1,A2,G$1:G1))

ThxAlot

这种涉及相邻行（更准确的说是递归）计算的问题，还是公式更为简洁易懂。

PQ写出来就比较难看了

1. let
   
2.     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LDoAgDETv0jVJP3yEpXoMwv2vYauCGDaTaV8yr1bYwQEjBRQSsU6kWQiaq3BolYjkjdrfW0ieaEISpVzsWHFGSoazHmGm5+ONw3vvc/ioiaWLy0LTu1z68o+qd+tejgO3Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Date = _t, Qty = _t, Split = _t]),
   
3.     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Date", type date}, {"Qty", Int64.Type}, {"Split", Int64.Type}}),
   
4.    
   
5.     #"Split by Code" = Table.Group(
   
6.             #"Changed Type",
   
7.             "Code",
   
8.             {"Grp", each
   
9.                 let
   
10.                     split = [Qty],
    
11.                     total = [Split]{0},
    
12.                     arr = List.Accumulate({0..Table.RowCount(_)-1}, {}, (s,c) => let remain = total-(List.Sum(s)??0)
    
13.                 in
    
14.                     if remain >= split{c} then s&{split{c}} else s&{remain}) in Table.FromColumns(Table.ToColumns(_) & {arr}, Table.ColumnNames(Source) & {"Arrangement"})
    
15.             }
    
16.     ),
    
17.     #"Expanded Grp" = Table.ExpandTableColumn(#"Split by Code", "Grp", {"Date", "Qty", "Split", "Arrangement"})
    
18. in
    
19.     #"Expanded Grp"

复制代码

ljxxw

静听溪流 发表于 2023-5-3 14:49
=MIN(C2,E2-SUMIF(A$1:A1,A2,G$1:G1))

谢谢 静听溪流 老师  答复！

G列是没有的，是我演示的效果，=MIN(C2,E2-SUMIF(A$1:A1,A2,G$1:G1))里不用G列，公式该怎么写呢？

哦对的，不好意思，是我理解错了

ljxxw

ThxAlot 发表于 2023-5-3 16:34
这种涉及相邻行（更准确的说是递归）计算的问题，还是公式更为简洁易懂。

PQ写出来就比较难看了

非常谢谢  ThxAlot  老师答复！！
日期都正常，为何PQ里有6处提示：
DataFormat.Error: 无法分析提供给 Date 值的输入。
详细信息:  25/03/2020

ThxAlot  老师在吗？

14820103

ljxxw 发表于 2023-5-3 23:20
非常谢谢  ThxAlot  老师答复！！
日期都正常，为何PQ里有6处提示：
DataFormat.Error: 无法分析提供给 ...

修改为： {"Date", type text}就不会报错了。

bjzzxhzl

仅用最终截图的前三列。我遇事不决Acc，不过对于初学者来说，光看这花括号都容易看花了，仅供参考：




代码如下：

1. = #table(Table.ColumnNames(源)&{"拆分后"},List.Accumulate(Table.ToRows(源),{{},0},(x,y)=>if y{0}<>List.Last(x{0}){0}? then {x{0}&{y&{List.Min(List.LastN(y,2))}},y{3}-y{2}} else if x{1}-y{2}>=0 then {x{0}&{y&{y{2}}},x{1}-y{2}} else {x{0}&{y&{List.Max({x{1},0})}},x{1}-y{2}}){0})

复制代码

ljxxw

14820103 发表于 2023-5-7 14:39
修改为： {"Date", type text}就不会报错了。

修改为：{"Date", type text}会变成文本格式，不再是日期了，
可以加一条语句：= Table.TransformColumns(Source,{"Date",each Date.FromText(_,[Format="d/MM/yyyy",Culture="cn-zh"])})

14820103

ljxxw 发表于 2023-5-8 21:43
修改为：{"Date", type text}会变成文本格式，不再是日期了，
可以加一条语句：= Table.TransformColum ...

请教，为啥再更改类型已经是日期还会出错。还需后续再做一次定义。

390717298

来个容易理解的，哈哈！