PowerQuery技巧之先进先出分配法
- 格式：pdf
- 大小：158.20 KB
- 文档页数：2
顺序，只要在源数据中再加一个索引列，最后按索引列排序即可还原。
解法3：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 分组 = Table.Group(源,"产品",{"a",each _}), fx = (l,t)=>List.Accumulate(l,
实际上就是累计求和题型的变体，之前也介绍过很多次。 同样之前也说过，这种方法因为是逐行扫描筛选，算法的复杂度是行数的平方，所以如果数据量一大效率就会比较低，再想想有 没有别的解法？
解法2：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 分组 = Table.Group(源, {"产品"}, {"a", each Table.AddColumn(Table.AddIndexColumn(_,"索引",1,1),"分配",(x)=>[a=Table 展开 = Table.ExpandTableColumn(分组, "a", List.Skip(Table.ColumnNames(源))&{"分配"})
附件
先进先出 (1第一种方法的改进，同样分为三个过程，和前面的方法只在第一步的累计求和有区别。
此处使用分组加索引，然后 Table.FirstN ，每一行获得筛选表中前N行的记录，复杂度大大减少，效率有显著提升。 本题中[产品]列中的A和B本身就分开的，但如果是AABBAABB这种交替的排序，分组后就会打乱原来的顺序，如果要还原原来的
[a={},b=0] , (s,c)=>if t<c+s[b] then [a=s[a]&{List.Max({t-s[b],0})},b=s[b]+c] else [a=s[a]&{c},b=s[b]+c] )[a],
结果 = bine(List.Transform(分组[a], each Table.FromColumns( Table.ToColumns(_)&{fx([入库数],[库存]{0})}, Table.ColumnNames(_)&{"分配"}) )) in
结果
基于第二种方法，对中间的算法使用 List.Accumulate 进一步优化。
实测2W行数据秒出结果，效率最高，但如果数据过大可能会因为堆栈溢出引发报错。 这种方法较难，在《List.Accumulate》中有讲解。
总结：
如果数据量不大，以上三种方法均可。 如果数据量很大，用第三种方法，如果挂了，再换第二种方法。
先进先出分配法
题目：
先进先出分配法是财务与企业管理中经常遇到的老问题，根据期初库存分配出库数。
在工作表函数、VBA、DAX中都已经有了对应的解法，今天来看看在Power Query又是怎样的思路？
解法1：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 结果 = Table.AddColumn(源, "分配", each [a=Table.SelectRows(源,(x)=>x[产品]=[产品] and x[入库年月]<=[入库年月]),b=a[库存
in
结果
虽然是一步出结果，但整个过程分为三步，也就是中间构建的record的abc。 过程a是筛选每一行中日期<=当前行的所有记录，返回一张每行都不一样的筛选表：
过程b是用筛选表a中的[库存]列减去累计入库数，比如上图中就是用200-(50+60+100)。 过程c再判断如果b>0就返回当前行[入库数]的值，否则就加上[入库数]，也就是剩余的库存数，最后再对负数进行归0处理。
解法3：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 分组 = Table.Group(源,"产品",{"a",each _}), fx = (l,t)=>List.Accumulate(l,
实际上就是累计求和题型的变体，之前也介绍过很多次。 同样之前也说过，这种方法因为是逐行扫描筛选，算法的复杂度是行数的平方，所以如果数据量一大效率就会比较低，再想想有 没有别的解法？
解法2：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 分组 = Table.Group(源, {"产品"}, {"a", each Table.AddColumn(Table.AddIndexColumn(_,"索引",1,1),"分配",(x)=>[a=Table 展开 = Table.ExpandTableColumn(分组, "a", List.Skip(Table.ColumnNames(源))&{"分配"})
附件
先进先出 (1第一种方法的改进，同样分为三个过程，和前面的方法只在第一步的累计求和有区别。
此处使用分组加索引，然后 Table.FirstN ，每一行获得筛选表中前N行的记录，复杂度大大减少，效率有显著提升。 本题中[产品]列中的A和B本身就分开的，但如果是AABBAABB这种交替的排序，分组后就会打乱原来的顺序，如果要还原原来的
[a={},b=0] , (s,c)=>if t<c+s[b] then [a=s[a]&{List.Max({t-s[b],0})},b=s[b]+c] else [a=s[a]&{c},b=s[b]+c] )[a],
结果 = bine(List.Transform(分组[a], each Table.FromColumns( Table.ToColumns(_)&{fx([入库数],[库存]{0})}, Table.ColumnNames(_)&{"分配"}) )) in
结果
基于第二种方法，对中间的算法使用 List.Accumulate 进一步优化。
实测2W行数据秒出结果，效率最高，但如果数据过大可能会因为堆栈溢出引发报错。 这种方法较难，在《List.Accumulate》中有讲解。
总结：
如果数据量不大，以上三种方法均可。 如果数据量很大，用第三种方法，如果挂了，再换第二种方法。
先进先出分配法
题目：
先进先出分配法是财务与企业管理中经常遇到的老问题，根据期初库存分配出库数。
在工作表函数、VBA、DAX中都已经有了对应的解法，今天来看看在Power Query又是怎样的思路？
解法1：
let 源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content], 结果 = Table.AddColumn(源, "分配", each [a=Table.SelectRows(源,(x)=>x[产品]=[产品] and x[入库年月]<=[入库年月]),b=a[库存
in
结果
虽然是一步出结果，但整个过程分为三步，也就是中间构建的record的abc。 过程a是筛选每一行中日期<=当前行的所有记录，返回一张每行都不一样的筛选表：
过程b是用筛选表a中的[库存]列减去累计入库数，比如上图中就是用200-(50+60+100)。 过程c再判断如果b>0就返回当前行[入库数]的值，否则就加上[入库数]，也就是剩余的库存数，最后再对负数进行归0处理。
