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.
Hi
I have a column which has [Text Date Text] in one cell. I used power query to split text and date into different columns. Now I have the Date in one column but in Text format. I click on ABC of Power Query and change the format to Date but I got many error message inside the column. Please see screenshots and I also attaching the link to the file. How can I fix that? Coverting the format of date from text to date in power query. Thank you very much.
The file is here
https://drive.google.com/file/d/1QSYjZ_9rPKZrKjSWMXnJfrVZPlu1jmlE/view?usp=sharing
Solved! Go to Solution.
Yeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Item.1", type text},
{"Item.2", type date},
{"Item.3", type text},
{"Sales", type number}
},
"en-IN"
)
If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)
Hi,
I can't open your source. so, simulated this, and didn't see the issue.
I hope this answer helps you.
I don't see any issues with a similar dataset.
Can't open your file, not in the correct format
DataSource.Error: Could not find a part of the path 'C:\Users\153707\Desktop\PowerBI\weeks\7\More PQ Examples.xlsx'.
Details:
C:\Users\153707\Desktop\PowerBI\weeks\7\More PQ Examples.xlsx
Please check
Regards,
Ritesh
Hi,
I cannot see the information in Power Query Editor, since the source file is not accessible.
However, I assume that PBI desktop is setup as to show USA date structure, but the data shows different date structure.
I suggest try the below.
In Power Query Editor, select the column -> right click -> Change type -> Using locale -> Data type: Date -> Locale: English(Netherlands) or English(UK)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Yeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Item.1", type text},
{"Item.2", type date},
{"Item.3", type text},
{"Sales", type number}
},
"en-IN"
)
If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)
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.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |