一个关于PQ转换不规则表头的问题，详见贴内，在线等！

显示全部楼层 · 2021-2-18 19:06:42

问题说明：
1.左边红色的是原始表格表头，右边蓝色是想要实现的表头，最终保留蓝色第一行表头
2.涉及到的操作，商品编码和尺码向下填充，D5:I5区域智能填充空白，然后与对应的D6:I6区域内容实现合并，合并后内容作为新的表头。
3，示例表格只是一部分，实际仓库有几十个，商品编码也有几百条。

请问能否用PQ实现这个需求？如果可以，要怎么做呢？
在线等，谢谢大家！

10824111417433.rar (15.78 KB, 下载次数: 0)

adam · 2021-2-18 19:12:42

这种透视表出来的表头已经极其规则了，转换起来也很容易。我认为第二行表头应该就没什么必要了。

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZExDsIwDEXvkrmVHKe0yQgzB0Cqehe6wNihgBjhACDmwnGISm+Bo7QsDLWlOPlyXr6+lbJU/rjzbd2/Tv2lVonyj24Szed+JUFr+X62Ua1GVSWlip3hsB32TXjQtf52ZnXCa+0ADABm1EYbNqoFlaYy44n/qGOjBvionkdz0AUzwIhyAkwoP4DBOVRrC5iG682azyIHdpCnoS1AJbaiDIYBo6PpkBX4h0psOYEJNqlmG0eWaVwIjAuZsWC8CDP/I8IZD7aC+ez8fNUX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Added Custom" = Table.AddColumn(#"Filled Down1", "Custom", each [Column1] & ([Column2]??"")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom"} & List.RemoveItems(Table.ColumnNames(#"Removed Columns"), {"Custom"})),
#"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
#"Filled Down" = Table.FillDown(#"Transposed Table1",{"Column1", "Column2"})
in
#"Filled Down"