Need to reference individual unnamed Excel cells to make custom columns

[Anonymous]

Hello,

I am trying to pull in a couple hundred Excel worksheets with a given schema. The bulk data starts on row 4 and I have a way to filter for that. What I am trying to figure out is how to create columns from values in individual cells from row 2:

I know there are ways to get this value with named ranges, but I cannot go back and name those ranges on hundreds of files. The most brute force way I can think to get those values is to make queries hitting those cells and then joining them to the big tables but then I'd be hitting the files multiple times. Trying to figure out how to get those individual unnamed cells and the big table in one query.

[Vijay_A_Verma · Accepted solution]

1. Create the query. You can mark it as not to be loaded by right clicking it and uncheck Enable load. 

2. Now create a reference query against the above query. In this table, you will need to do all transformations like filtering etc.

3. In this reference query, you can refer to individual cells of first query. Let's say first query is named Query1 and column name is Column1....Now to refer to second cell of this column

= Query1[Column1]{1}

(Since PQ starts with 0 based index, hence for 2nd element, you need to specify 1 not 2)

There are many ways to do this. For example, you can also use

= Query1{1}[Column1]