let
源 = Folder.Files("E:\download\IDM\求助"),
自定义 = Table.AddColumn(源, "自定义", each
[a = Excel.Workbook([Content])[Data]{0},
b = Table.Transpose(Table.Skip(a, 6)),
c = Table.FirstN(b, Table.RowCount(b)-2),
d = Table.FillDown(c, {"Column1"}),
e = Table.CombineColumns(d, {"Column1", "Column2"}, each Text.Combine(_, "-"), "分类"),
f = Table.Transpose(e),
g = Table.PromoteHeaders(Table.FirstN(f, List.PositionOf(f[Column1], "合计")))
][g]),
删除列 = Table.SelectColumns(自定义,{"Name", "自定义"}),
替换的值 = Table.ReplaceValue(删除列,"数据.xls","份",Replacer.ReplaceText,{"Name"}),
展开 = Table.ExpandTableColumn(替换的值, "自定义", Table.ColumnNames(替换的值[自定义]{0}))
in
展开 |