Hi Steve,

I did a smaller scale experiment, connecting to a sheet in a workbook where I entered some data in Sheet1 in the first few columns and a space in the 50th column. Connect to the workbook, select the file name in the Navigator window (i.e. not the sheet with the data) and press Transform Data.

Then PQ will just show the workbook structure. The Data column contains a Table with the data from the worksheet you want to extract information from.

Now, you can apply the following steps:

   Source = Excel.Workbook(File.Contents("C:UsersXXBook3.xlsx"), null, true),
   AddedCustom = Table.AddColumn(Source, "Custom", each Table.ColumnNames([Data])),
   ColNames = List.FirstN (AddedCustom{[Item="Sheet1",Kind="Sheet"]}[Custom],15),
   Remove = Table.SelectColumns(Source,{"Data"}),
   Expand = Table.ExpandTableColumn(Remove, "Data", ColNames)
in
   Expand

Or if you want to make a bit more compact you can join the red lines of code into one.

let
   Source = Excel.Workbook(File.Contents("C:UsersXXBook3.xlsx"), null, true),
   ColNames = List.FirstN (Table.AddColumn(Source, "Custom", each Table.ColumnNames ([Data])){[Item="Sheet1",Kind="Sheet"]}[Custom], 15),
   Remove = Table.SelectColumns(Source,{"Data"}),
   Expand = Table.ExpandTableColumn(Remove, "Data", ColNames)
in
  Expand

This will only import the first 15 columns from my Sheet1 that actually has 50 "used" columns in it.

From here you can continue with more transformations. Hopefully this will speed up matters.

If not, let me know.

Riny

Hi Riny,

I am new to using Power Query and your answer really helped (once I had realised I needed to use the Advanced Editor :-))

Very useful, many thanks

Steve