I imported a .CSV file and I want to know how I can change the date format into my desired format. Also, I have a column that has its date in DD/MM/YYYY format, but Power BI is reading it as Text.
I tried converting it into the required format, from Modelling->Data Type in the Report Window, but it gives the error, “We can’t automatically convert the column to Data Type.” And then when I tried to convert it from Edit Queries -> Transform, output is Error in each cell.
You have several options in the query editor to change date formats.
Recommend to always use the Power Query editor and check the steps on the right side. If you see a “changed type” step you may find that by default certain columns have been formatted, which is not always what you want. In fact I always remove this step and analyze the data before I manually change types. The query editor will only show and analyze the first 1000 rows in your data, if there is a different format in row 1200, you will get an error, hence my preference to change things manually. Once you have removed the “changed stype” step, check what the format is and make the changes (I assume that the locale setting is correct, if you are not aware what this is ignore for now). You can also add a column by example and type the desired format for your column. Hope this helps.
Also be aware this may be controlled by the Local Setting. Your .CSV file likely came from a place where they use DD/MM/YYYY. To have Power BI recognize those as dates: Go to File => Options and settings => Options => Locale => change to your country/region.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
