Dynamic field names in Excel queries

Question

As explained here, Excel queries accept dynamic values (e.g. "where name = ?" and "?" can be linked to a cell of your choice).

Alas, Excel queries don't seem to accept dynamic field names (e.g. the "name" in "where name"). Is there a way to bypass that limitation? Well, another way than this.

Thanks!

Answer 1

I would use the Power Query Add-In for this. I first add an Index column, then duplicate the Query.

In the duplicate, I would select the Index and choose Unpivot Columns / Unpivot Other Columns (PQ Transform ribbon). This will convert each cell into a row, with Attribute (field name) and Value columns.

I would then filter on the Attribute and Value fields, which would give you a list of which Index values to keep.

Then I would return to the original query, and use Merge Queries (PQ Home ribbon) to join it to the duplicated query, matching on the Index columns and using the Join Kind: Inner.