1.25 PowerBI数据准备-先进先出，为订单匹配库存批次

加入 PowerBI自己学 知识星球：下载源文件，边学边练；遇到问题，还可以提问交流。

有一张订单表，里面有一批订单，还有一张库存表，里面有不同批次的产品库存。需要按照先进先出原则将生产日期相对旧的批号库存分配给订单日期相对早的订单，在订单表中标记匹配的库存批次和数量。

订单表

库存表

匹配库存后的订单表

解决方案

将订单表和库存表分别排序，订单按照产品、订单日期、订单编号、数量排序，库存按照产品和生产日期排序，然后分别利用排序列，算出每一行的累计数量所在的最小值和最大值区间，再在订单表中插入一列，去取库存表中的库存累计区间包含订单累计区间的批次（如果一条订单匹配了不同批次，订单将会拆分为多行）。如果订单中只有部分条目被匹配，整条订单属于未完全匹配库存，需要剔除。

操作步骤  

STEP 1 以订单表为例。分产品按订单日期、订单编号、数量进行升序排列。在查询设置的最后一个步骤上点击鼠标右键，选择插入步骤，输入如下代码。然后，点击列标题右侧的展开按钮。

Table.Group(#"Expanded {0}", {"产品"}, {"表", each Table.AddRankColumn(_,"序号",{{"日期",Order.Ascending},{"订单编号",Order.Ascending},{"数量",Order.Ascending}},[RankKind=RankKind.Ordinal])})

这里用到了分组函数Table.Group和增加排序列函数Table.AddRankColumn。

STEP 2 在订单表中，添加最大值列和最小值列，其中最大值为累计求和，最小值为上一行的最大值+1。点击菜单栏添加列下的自定义列，输入如下代码。

最大值：

List.Sum(Table.SelectRows(#"Expanded {0}", (x)=> [产品]=x[产品] and [序号]>=x[序号])[数量])

最小值：

if [序号]=1 then 1 else #"Custom1"{[产品=[产品],序号=[序号]-1]}[最大值]+1

STEP 3 库存也按上述操作做好相应准备后，在订单表中插入一列取库存表中的批次。选中订单表，点击菜单栏添加列下的自定义列，输入代码，然后，点击列标题的展开按钮，并筛选去掉null值。

匹配批次：

Table.SelectRows(库存表, (x)=> [产品]=x[产品] and ((x[最小值]>=[最小值] and x[最小值]<=[最大值]) or (x[最大值]>=[最小值] and x[最大值]<=[最大值])))[批次]

STEP 4 重复上一步的操作，将匹配批次对应的库存数量最大值和最小值取出来，然后添加自定义列，取两个最大值中较小的和两个最小值中较大的相减加1用作匹配数量。

库存最大值：

Table.SelectRows(库存表, (x)=> x[产品]=[产品] and ((x[最小值]>=[最小值] and x[最小值]<=[最大值]) or (x[最大值]>=[最小值] and x[最大值]<=[最大值])) and x[批次]=[匹配批次])[最大值]

库存最小值：

Table.SelectRows(库存表, (x)=> x[产品]=[产品] and ((x[最小值]>=[最小值] and x[最小值]<=[最大值]) or (x[最大值]>=[最小值] and x[最大值]<=[最大值])) and x[批次]=[匹配批次])[最小值]

匹配数量：

if [匹配批次] = null then null else List.Min({[库存最大值],[最大值]})-List.Max({[库存最小值],[最小值]})+1

这里用到了List.Max和List.Min函数，取列表的最大值和最小值，List.Max({[列1],[列2]})。

STEP 5 点击菜单栏添加列下的自定义列，输入如下代码，判断订单是否完全匹配了库存。

if List.Max(Table.SelectRows(#"Added Custom2", (x)=> [订单编号]=x[订单编号])[库存最大值])<List.Max(Table.SelectRows(#"Added Custom2", (x)=> [订单编号]=x[订单编号])[最大值]) then "N" else "Y"

这里用到了表筛选函数Table.SelectRows，按条件筛选上一查询步骤生成的表。

STEP 6 筛选保留订单是否完全匹配列为“Y”的内容，并删除无用的列。