let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
拆分 = Table.SplitColumn(源, "系统时间", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"dh", "m", "s"}),
转换 = Table.TransformColumns(拆分, {"m", each Number.ToText(Number.Round(Number.From(_)/5,0)*5)}),
合并 = Table.CombineColumns(转换,{"dh", "m", "s"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"系统时间"),
分组 = Table.Group(合并, {"设备编号", "系统时间"}, {{"计数", each
[a = Table.RemoveColumns(_, {"设备编号", "系统时间"}),
b = Table.UnpivotOtherColumns(a, {"通道号"}, "属性", "值"),
c = Table.RemoveColumns(b, "属性"),
d = Table.Sort(c, {"通道号", "值"}),
e = Table.PromoteHeaders(Table.Transpose(d)) ][e]
}}),
展开 = Table.ExpandTableColumn(分组, "计数", {"0", "0_1", "1", "1_2"}, {"压力", "压力数据状态", "温度", "温度数据状态"})
in
展开 |