Issue converting date from JSON source

[Anonymous]

Hi all, 

As per the below screenshot, when I import a JSON file into Power BI, there is an issue with the formatting of the date. 

When I try and change the column to Date/Time using the 'Date Type' option in the column tools, I get the following erorr - 'We can't automatically convert the column to Date/Time type'. 

Please can anyone advise on the best way to achieve the date format of dd/mm/yyyy hh/mm. Any help is greatly appreciated!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can add a custom column with below code in Power Query editor and use it to replace the original column. 

Date.FromText(Text.BeforeDelimiter([Last Start Date]," "))&Time.FromText(Text.AfterDelimiter([Last Start Date]," "))

Or if you want to transform the original column directly, it would be:

= Table.TransformColumns(#"Changed Type", {{"Last Start Date", each Date.FromText(Text.BeforeDelimiter(_," "))&Time.FromText(Text.AfterDelimiter(_," ")), type datetime}})

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hi, thank you for this! The top method works although ideally I would need to transform the original column. I can see the code pasted but how do I transform the column? 

[v-jingzhang]

@Anonymous 

See the formula bar above the table? You can click on "fx" to add a step and paste the code there. My previous step is "Changed Type", so I have it as the first variable in Table.TransformColumns() function. Replace it with your previous step name and don't forget the "#". You will see its previous step in the formula bar after clicking "fx".

BR,

Jing

[mahoneypat]

Here's one way to do it with a custom column.  Or you could adapt this formula into a custom transform column step.

= DateTime.From(Date.FromText(Text.BeforeDelimiter([DateTime], " "))) + Duration.FromText(Text.AfterDelimiter([DateTime], " "))

Pat

[amitchandak]

@Anonymous , In power query try a new column

= Date.FromText([Date])

//Change data type to date

https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

In column format choose the format you want(Column tools)