Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)}))
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)}))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.