Tag: Power Query

参考：

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

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

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

SKU	箱数	装箱率
A	10	2
B	8	2

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

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

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

实现方式为添加List列。

  = {1..[箱数]}

然后 Expand to new rows

然后添加索引列即可。

具体代码：

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"