一、原始代码运行5分07秒
let
源 = Folder.Files(fnGetParameter("原始数据")),
删除的其他列 = Table.SelectColumns(源,{"Content"}),
已添加自定义 = Table.AddColumn(删除的其他列, "Custom", each Excel.Workbook([Content])),
#"展开的“Custom”" = Table.ExpandTableColumn(已添加自定义, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
筛选的行 = Table.SelectRows(#"展开的“Custom”", each ([Name] = "Sheet1")),
#"展开的“Data”" = Table.ExpandTableColumn(筛选的行, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10"}),
筛选的行1 = Table.SelectRows(#"展开的“Data”", each ([Data.Column1] <> null and [Data.Column1] <> "学习详情成绩报表")),
提升的标题 = Table.PromoteHeaders(筛选的行1)
in
提升的标题
二、第一次优化后运行13秒
第二次优化只是删除了包含binary的那一列，如图：
未优化前.png
代码如下：
let
源 = Folder.Files(fnGetParameter("原始数据")),
删除的其他列 = Table.SelectColumns(源,{"Content"}),
已添加自定义 = Table.AddColumn(删除的其他列, "Custom", each Excel.Workbook([Content])),
#"展开的“Custom”" = Table.ExpandTableColumn(已添加自定义, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
筛选的行 = Table.SelectRows(#"展开的“Custom”", each ([Name] = "Sheet1")),
#"展开的“Data”" = Table.ExpandTableColumn(筛选的行, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10"}),
筛选的行1 = Table.SelectRows(#"展开的“Data”", each ([Data.Column1] <> null and [Data.Column1] <> "学习详情成绩报表")),
提升的标题 = Table.PromoteHeaders(筛选的行1),
删除的其他列1 = Table.SelectColumns(提升的标题,{"账号", "姓名", "组织机构", "状态"})
in
删除的其他列1
只在最后一步增加了一个删除binary那一列的步骤。
三、进一步优化后运行只需7秒
在第一次优化的基础上，考虑到删除不必要的列将会进一步节省时间，于是新增了两次删除，代码如下：
let
源 = Folder.Files(fnGetParameter("原始数据")),
删除的其他列 = Table.SelectColumns(源,{"Content"}),
已添加自定义 = Table.AddColumn(删除的其他列, "Custom", each Excel.Workbook([Content])),
删除的其他列2 = Table.SelectColumns(已添加自定义,{"Custom"}),
#"展开的“Custom”" = Table.ExpandTableColumn(删除的其他列2, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
筛选的行 = Table.SelectRows(#"展开的“Custom”", each ([Name] = "Sheet1")),
删除的其他列3 = Table.SelectColumns(筛选的行,{"Data"}),
#"展开的“Data”" = Table.ExpandTableColumn(删除的其他列3, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10"}),
筛选的行1 = Table.SelectRows(#"展开的“Data”", each ([Data.Column1] <> null and [Data.Column1] <> "学习详情成绩报表")),
提升的标题 = Table.PromoteHeaders(筛选的行1),
删除的其他列1 = Table.SelectColumns(提升的标题,{"账号", "姓名", "组织机构", "状态"})
in
删除的其他列1
上面的“删除的其他列2”和“删除的其他列3”是新增的步骤。
四、总结
其实两次优化，都只是一个删除不必要的列的动作。第一次删除binary列，运行时间缩短到13秒，只占原来的四分之一，原因在于binary保存的是原始数据的二进制，非常占内存，所以我同事直接提示内存不够。而且这一列在后续数据处理中，用不着，所以直接删除就大幅提高运行效率了。
第二次优化，提前删除了包含binary在内的其他不需要的列，进一步将运行时间缩短到原来的一半。
第二次优化，最后要保留的列和第一次优化是一样的，只是多了几个删除不必要的列的动作。
所以，不必要的列，尤其是binary这类用完之后不再用的列，越早删除越好。
