Power Query 扩充行数的方法

参考:

Insert X Rows to Expand Table – Excel University (excel-university.com)

一张出运明细表中含有装箱率、件数、箱数,现在需要用Bartender制作外箱贴,外箱贴是有序号的,需要正好符合总外箱数。

在安排编号的时候并不会手动设定每一张外箱贴编号,我需要根据外箱数及不同SKU,自动生成外箱编号。举例:A有10箱,B有8箱。

SKU箱数装箱率
A102
B82

需要自动转化出另一张表:

编号SKU装箱率
1A2
2A2
3A2
4A2
5A2
6A2
7A2
8A2
9A2
10A2
11B2
12B2
13B2
14B2
15B2
16B2
17B2
18B2

我有预感Power Query能实现扩充行数。

实现方式为添加List列。

  = {1..[箱数]}

然后 Expand to new rows

image-20211202230714581

然后添加索引列即可。

具体代码:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"EAN", type text}, {"UNIT", Int64.Type}, {"CTNS", Int64.Type}, {"PCS", Int64.Type}, {"PCStoPrint", Int64.Type}, {"CTNStoPrint", Int64.Type}, {"CTN_NUM_F", type any}, {"CTN_NUM_T", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CTINS_LIST", each {1..[CTNS]}),
    #"Expanded CTINS_LIST" = Table.ExpandListColumn(#"Added Custom", "CTINS_LIST"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded CTINS_LIST", "CTIN_ID", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"CTINS_LIST"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"CTIN_ID", "SKU", "EAN", "UNIT", "CTNS", "PCS", "PCStoPrint", "CTNStoPrint", "CTN_NUM_F", "CTN_NUM_T", "RANGE"})
in
    #"Reordered Columns"