近来在汇总全年数据时出现问题如下：
一个工作簿中的一个工作表在1-5月时是这样A-X列
那知道从6月起要在中间插入一列“其他”，所以变成了这样A-Y列
我在用PowerQuery汇总时用了以下的方法进行处理。在此记录下
let
源 = Folder.Files(目录[复制目录在此]{0}),
Content = Table.AddColumn(源, "Con", each Excel.Workbook([Content])),
展开的Con= Table.ExpandTableColumn(Content, "Con", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
筛选的行 = Table.SelectRows(展开的Con, each ([Name.1] = "在职明细" )),
Skip = Table.AddColumn(筛选的行, "skip4", each Table.Range([Data],4,List.PositionOf([Data][Column1],"合计")-4)),
Select行 = Table.AddColumn(Skip, "Select行", each
if Number.From(Text.Middle([Name],5,2))<6 then
Table.SelectColumns([skip4],{"Column1","Column2","Column3","Column4","Column5","Column24"})
else
Table.RenameColumns(
Table.SelectColumns([skip4],{"Column1","Column2","Column3","Column4","Column5","Column25"}),
{"Column25", "Column24"}
)
),
删除的其他列 = Table.SelectColumns(Select行,{"Name", "Select行"}),
替换的值 = Table.ReplaceValue(删除的其他列,"区代发工资.xls","",Replacer.ReplaceText,{"Name"}),
展开skip4 = Table.ExpandTableColumn(替换的值, "Select行", {"Column1", "Column2", "Column3", "Column4","Column5","Column24"}, {"序号", "单位", "姓名", "身份证","帐号", "备注"}),
去空行 = Table.SelectRows(展开skip4, each [单位] <> null),
重命名的列 = Table.RenameColumns(去空行,{{"Name", "年月"}}),
逆序的行 = Table.ReverseRows(重命名的列),
自定义1 = Table.Distinct(逆序的行,{"姓名","身份证"})
in
自定义1
