Hello,

I am new to using query and I am trying to extract data from a report in an excel file (report_1). The first 5 rows in the excel file are irrelevant (report details and caveats) with the information laid out with column headers in row 6.

I have created a query to extract the information from the file as below and I am looking for assistance to improve the performance of the extract as it takes minutes to run. I then want to use this as a reference for further extracts into different tables.

Any help appreciated.

let
Source = Folder.Files("C:\Users\GARYP\Documents\Path1\Path2\Folder1"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Report_1") and Text.StartsWith([Extension], ".xls")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.StartsWith([Extension], ".xlsb") then Excel.Workbook( [Content]) else null),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Report_1)),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom.Data", {"Column1", "Column2", "Column5", "Column7", "Column8", "Column12", "Column13", "Column16", "Column17", "Column24", "Column26", "Column35", "Column36", "Column37", "Column38", "Column49", "Column53", "Column54", "Column55", "Column56", "Column64"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column5", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column16", "Custom.Data.Column17", "Custom.Data.Column24", "Custom.Data.Column26", "Custom.Data.Column35", "Custom.Data.Column36", "Custom.Data.Column37", "Custom.Data.Column38", "Custom.Data.Column49", "Custom.Data.Column53", "Custom.Data.Column54", "Custom.Data.Column55", "Custom.Data.Column56", "Custom.Data.Column64"}),
#"Removed Top Rows" = Table.Skip(#"Expanded Custom.Data",5),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Custom.Name"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
in
#"Promoted Headers"