我在施阳老师的博客看到这篇关于多维转一维的文章:多维转一维
但在自己尝试保留源文件文件名时出现了很多重复行,请问各位如何才能正确的保留文件名?
let
fx=(bin)=>
let
表= Excel.Workbook(bin){0}[Data],
转为列表 = Table.ToColumns(表),
标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5,0,1,2,4,5,0,3,0},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9,19,19,19,19,19,26,26,27}}),each 转为列表{_{0}}{_{1}}),
数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5,0,1,2,4,5,1,4,2},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10,20,20,20,20,20,26,26,27}}),each {转为列表{_{0}}{_{1}}}),
结果 = Table.FromColumns(数据,标题),
#"Added Custom" = Table.AddColumn(结果, "Custom", each Table.Range(表,10,List.PositionOf(表[Column1],"名称")-11)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6"})
in
#"Expanded Custom",
Source = Folder.Files("C:\Users\Jeremy Fu\Desktop\PowerQuery\多维转一维\多维转一维\多文件汇总\源数据"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.Combine(List.Transform(#"Removed Other Columns"[Content],fx))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])), Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])))
in
#"Expanded Custom"