cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lastnn30
Helper V
Helper V

changing date from text to date

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.SNAG-0705.jpgSNAG-0706.jpgSNAG-0707.jpg

 

The file is here

https://drive.google.com/file/d/1QSYjZ_9rPKZrKjSWMXnJfrVZPlu1jmlE/view?usp=sharing

 

1 ACCEPTED 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.)

View solution in original post

4 REPLIES 4
AtefehNakisa
Frequent Visitor

Hi,

I can't open your source. so, simulated this, and didn't see the issue. 

I hope this answer helps you.

Screenshot 2022-05-13 232157.pngScreenshot 2022-05-13 232231.pngScreenshot 2022-05-13 232256.png

ribisht17
Memorable Member
Memorable Member

@lastnn30 

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

Jihwan_Kim
Super User
Super User

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.


Go to My LinkedIn Page


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.)

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors