图形界面操作就行,不需写函数
1、将后四列向上填充
2、删除辅助列和CharacteristicSeq列
3、透视"CharacteristicName"列,值选 "CharacteristicValue"
4、删除"SampleNo"列
5、调整列顺序
最终代码如下:
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"辅助列", type any}, {"ProcessCheckName", type text}, {"MachineName", type text}, {"SampleSize", Int64.Type}, {"ActualSampleSize", Int64.Type}, {"CreatedOn", type datetime}, {"CharacteristicName", type text}, {"CharacteristicValue", type number}, {"SampleNo", Int64.Type}, {"CharacteristicSeq", Int64.Type}, {"USL", type number}, {"LSL", type number}, {"UCL", type number}, {"LCL", type number}}),
填充 = Table.FillUp(更改的类型,{"USL", "LSL", "UCL", "LCL"}),
删除的列 = Table.RemoveColumns(填充,{"辅助列", "CharacteristicSeq"}),
已透视列 = Table.Pivot(删除的列, List.Distinct(删除的列[CharacteristicName]), "CharacteristicName", "CharacteristicValue"),
删除的列1 = Table.RemoveColumns(已透视列,{"SampleNo"}),
重排序的列 = Table.ReorderColumns(删除的列1,{"ProcessCheckName", "MachineName", "SampleSize", "ActualSampleSize", "CreatedOn", "槽号", "液体重量 S1", "纯重量 P1", "容器重量 S2", "总重量 P2", "测试量", "LCL", "UCL", "LSL", "USL"})
in
重排序的列 |