Forum Discussion
Language of month's name extracted from a date in a dynamic table from Power Pivot Data Model
Hi,
I have a problem with an Excel file that has been created in Windows 10 Pro with en-US regional format. The problem is that when I open it on a computer with Windows 10 Pro with es-MX regional format, the language of the month remains English. This happens only when I use the Power Pivot Data Model (using an Excel table as a source works correctly, as well as with a formula).
I made an example to explain the situation:
- I created an example table in a sheet, uploaded it to Power Query, and published it to the Power Pivot data model. All this with the regional format setting to "English (United States)" in Windows.
- I have created a dynamic table using the Data Model as a source, in Lines I put the name of the month (automatically generated) and in Values the sum of the number field.
- I have also created the same pivot table using the Excel table (tbsample) instead of the data model.
- I have created some example dates and extracted the name of the short month with the function TEXT([date], "MMM")
Result:
- By configuring the regional format "English (United States)" in Windows, all months are seen in English:
https://app.box.com/s/p07cl37lyjg4m0fopourqj36cuwadjzq - By configuring the regional format "Spanish (Mexico)" in Windows, the "Pivot Table from DataModel" remains in English, the others change correctly to spanish:
https://app.box.com/s/rvbs8jt5g2gxf7picvhhr8sdtz0lj4ar
I hope someone can help me, I already asked on the es community with this post and on the Microsoft 365 Support as well without success.
Thank you,
Hi Marco_RC
I think I can replicate what you explained, though I'm not sure where the issue comes from... I had similar challenges in the paste and instead of wastihg time investigating the reason I built my own Calendar/Date table with Power Query (I can provide the query)
Fixing your particular workbook (get months in Spanish) should be doable. However, depending on how the data loads to the Data Model the approach might be different. If you could share your workbook that would be ideal...
An improssible quick-fix would have been:
but the 3rd argument (locale_name) of the DAX FORMAT function is currently supported in PowerBI only
Hi Lorenzo ,
Thank you very much for the answer.
We used a similar approach through Power Query to resolve the problem. However, I'm sharing with you the sample workbook and I would like to know the details of your proposed solution, please.
Also we saw the format parameter on the MONTH dax function, and it is quite absurd that it is not supported in Excel, it would help a lot.
My concern is that, on a Pivot Table, when the user add a date type field, Excel automatically generate the conversion of the month and it will show the wrong value, so it is really misleading.
Hey Marco_RC
I was hoping to get the file created with Windows 10 US Pro not your test file. The latter doesn't help trying to fix the first 🙂
Agreed on the FORMAT function... 😞
Side note: a couple of days ago I experienced an issue with NATURALINNERJOIN that doesn't work in Excel DM but works no problem in PowerBI with the same input tables. According to my research the issue is "known" for a long time. A workaround exists but it's awful & complex from my perspective
(Late my time) I'll provide "my" generic solution tomorrow (need to do a bit of clean-up + doc.). In any case users must be educated not to used any date based field(s) from their table(s) but to the PQ Calendar otherwise they'll always face the issue - or I missed something... (always possible)
Regards
