Forum Discussion

halifaxious's avatar
halifaxious
Frequent Visitor
4 years ago
Solved

bilingual Excel file as data source

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)

ColourName
blueBob
greenJill

 

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

CouleurNom
blueBob
greenJill

 

Need Help
  • artemus's avatar
    artemus
    4 years ago

    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)}))

1 Reply

  • artemus's avatar
    artemus
    Microsoft Employee
    4 years ago

    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)}))