Performance issue in extracting data from excel file (find folder)

Gary Powers

New Member
Joined
Apr 18, 2019
Messages
1
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"
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,096,448
Messages
5,450,507
Members
405,614
Latest member
SJ789

This Week's Hot Topics

Top