Import Entire Worksheet Rather Than UsedRange

Hi Anonymous,

Try this one. I've optimised the performance by truncating unnecessary checks after the first header match is found, also limiting the number of rows to check for the header (in this case top 100 rows, which seems reasonably high). If you sure this number can be less than 100, change it to what you think is most appropriate. The smaller the number the quicker the code.

let
    Source = Excel.Workbook(File.Contents("C:\...\Downloads\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    fTrimTable = (tbl as table, header as text) =>
        let 
            t = Table.Buffer(tbl),
            columns = List.Buffer(Table.ColumnNames(t)),
            rowsToCheck = 100,
            Column = List.Select(columns, each List.PositionOf(List.FirstN(Table.Column(t, _),rowsToCheck), header)>0){0},
            Row = List.PositionOf(Table.Column(t, Column), header),
            ScrollRows = Table.RemoveFirstN (t, Row),
            ScrollColumns = Table.SelectColumns(ScrollRows, List.RemoveFirstN(columns, List.PositionOf(columns, Column))),
            #"Promoted Headers" = Table.PromoteHeaders(ScrollColumns, [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    
    Trimmed = fTrimTable(Sheet1_Sheet, "Header100")
in
   Trimmed

I've tested it on a file with 600 columns and 2000 rows (about 12Mb), on my fairly old laptop it takes about 10-15 sec to refresh, much better then the older version.

For records: the problem with the old code was that (a) it was loading the entire Excel file each time for each column checked for the position of the header, and (b) checking position of the header in the list of thousands of itemsis not quick, when it is not on hte list. Both not a big problem if the colunm is found quickly (ideal case it is in the A1 cell), but very quickly become one as the number of columns to check overgrow 3-5, in my testing for searhing of "Header100" - the 100th column - it took forever and I had to break the cyle. Optimised version does not have any of the above problems.

Kind regards,

John