best solution for translating pivot headers?

[halifaxious]

I have a requirement to present my data in French and in English formats. I've created an isFrench boolean parameter to hold the desired state. My pivots take their data from a couple Power Query tables.  So, my question: which of the following methods would be fastest and most stable for the end user? 

note: I'm doing this with Power Query, Power Pivot and Excel.

Method 1:

1. output 2 versions of the data table from Power Query, one with French and the other with English column names
2. create 2 pivot tables, one based on the data table with French headers, the other on the English data table
3. toggle visibility of pivot tables based on value of isFrench.

downside: extra data and pivot tables mean increased refresh times

Method 2:

1. have a Fr and En version of each Measure
2. create 2 named sets for each pivot table, one for Fr and one for En.
3. toggle which Set to display based on the value of isFrench --not sure how I'd do this. Probably VBA. Suggestions welcome.

downside: not sure if row headers are renameable in a Set

Method 3:

1. generate one data table from Power Query
2. generate one version of each pivot table
3. on change in isFrench, run VBA script to change the display names of the pivot fields.

downside: VBA to maintain

I have a feeling that Method 3 is the most efficient. But I'd like to avoid VBA if I can. Are the other 2 options terrible? Is one better than the other. Is there a 4th option that is even better?

[v-cgao-msft]

Hi @halifaxious ,

You can refer to Greg's reply under this thread.

Solved: Multilingual reports in Power BI :Headers translat...

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[halifaxious]

Interesting information. But it won't work for me because, as I mentioned, I'm doing this with Power Query, Power Pivot and Excel. Since any solution is going to involve DAX, I thought this would be a good place to post. If it's not, where would be better?