bilingual Excel file as data source

[halifaxious]

I work in an bilingual organization. Many of our spreadsheets have a VBA function that translates the table headers on request. These tables need to be data sources for various queries. But a query fails when the source headers are different (ie translated) from what they were when the query was built.

What is the best way to query an Excel table with dynamic column headers?

Here is some toy data:

TableX (with English headers)

Colour	Name
blue	Bob
green	Jill

TableX (with French headers after VBA swaps in the translations)

Couleur	Nom
blue	Bob
green	Jill

[artemus · Accepted solution]

Add the following step custom after your source step (replace ... with your other columns):

Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), {"Colour", "Name", ...}}))

To translate back, at the end of your query add (replace previousStepName with your last step):

Table.RenameColumns(previousStepName, List.Zip({Table.ColumnNames(previousStepName), Table.ColumnNames(Source)}))