Convert Date format: from Text American format to date

[Anonymous]

Hi Team, quick question for you: I have a dataset in .txt format. When I upload the query everything comes nice and tidy out of those fields that should be coded as Date. So they are kept as text or whole number: How do I get from this 10719 to this 01/07/19. I would like to work this out at query level as i have several columns that should be coded as date. thanks thanks

[Anonymous]

Hi @Anonymous;

in Power Query add Custom Column and use below formula:

=Text.Start([Date],Text.Length([Date])-4)&"/"& Text.Start(Text.End([Date],4),2)&"/"&Text.End(Text.End([Date],2),4)

where [Date] is the column you whant to change. After that change type of new column in Date Time.

Best Regards

Chiara

[Anonymous]

hi Chiara, it worked almost perfectly, just sometimes it comes up with this message: DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 12/21/18 but the cell where data is has 122118. Do you know why? thanks Alberto

[Anonymous · Accepted solution]

Hi Alberto,

this problem happens because the windows regional setting is not set to english (US).

You can change that or you can split column by delimiter "/" and rename the columns as below

Then you can create a custom column putting the day before the month:

DateTime=[Day]&"/"&[Month]&"/"&[Year]

I hope you can solve it now!

Ciao

Chiara

[v-piga-msft]

Hi @Anonymous,

By my tests with the suggestion of Chiara should be helpful and I didn't get any error.

In addition, you could try another way in query editor.

You could split your column like below.

Then create a custom column.

=[Month]&"/"&[Day]&"/"&[Year]

Here is the result.

More details, please refer to the APPLIED STEPS in Query Editor in my test pbix file which has been attached.

Best  Regards,

Cherry

[Anonymous]

Hi Cherry, Chiara's solution works in most case but sometimes it's faulty. I can try to attach my pbi file if you want to have a look at it. https://drive.google.com/file/d/18g1yJqy99xCeAv8FalAiOy8qZTyZFTUl/view?usp=sharing let me know

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample and your desired output.

Best  Regards,

Cherry

[v-piga-msft]

Hi @Anonymous,

Based on your data sample, I cannot achieve your data source in query editor.

Please check if your date column is text format, if not, please change it to data type to be text type and then use the formula from Chiara or my another way in my first reply.

Best  Regards,

Cherry