sometimes small and obviously simple tasks, drives you crazy. And this is one, which is desperately required to provide professional reports. Hope you can help me with the following:
I would like to display the last date when an Excel-File has been modified in a “Card”. Some other dates from the content of the Excel-File should be displayed in a table.
Unfortunately the ‘Modified Date’ is in “en-us”-format (mm.dd.yyyy) and the dates in the content are in date-format “german” (dd.mm.yyyy)
As PowerBI only allows to set the locale for the complete file, one of the dates always invokes an error .
Even after having unchecked the automatic “type detection” in the options, I was not able to convert the date manually.
If I could provide a locale for each query, I could define 2 queries. The first will load the “Date Accessed” with the locale “en-us” and the second will load the dates from the content with the locale “german”.
Any idea on how to solve this or any other idea is highly appreciated.
Are you saying you can’t change the locale in the query editor or the actual table?
Like so here…
Would it be possible to re-arrange thing inside the query editor with a few steps. Basically overriding the date format manually with some transformation inside the query editor?
Hi Sam, finally I figured out the problem. In the options on data-load the automatic “Type Detection” was checked.
With that the first line in the query already displayed “error” either for the modifed Date of the file, or the dates within the file.
Generally I would recommend uncheck this option, as long as the automatic Type Detection is that stupid.
If you have created your model already, the only way to change this, is to change the “Data Source settings”.
Just select the respective data-source and then “change source” and close.
With that the option upon load is turned off and you can manually change the date type using locale.