Whole Number to Date

[Anonymous]

Hi, 

My data of order date change from Date to Whole Number when I imported into Power BI. 

Here is the picture for illustration:

Please help me!

I also tried the fomular in other posts, but it did not work!! 

Thanks!! 

[Anonymous · Accepted solution]

in Power Query after you imported the table you will see "Change Type" under applied steps. Power Query tries (and does a pretty good job) at guessing at what your data types are.  Remove that step and see if you can set your date columns to date. If that errors that means there is something in that column that is preventing it from being converted to a date.

[alexvc · Accepted solution]

Hi there,

From what I see, from your data source your date is formatted as string or integer, that's why its not beign recognized as date. What you can do is in the query editor split that column into a day, month and year columns. Then create another column with the formula =DATE(DAY,MONTH,YEAR), this will give you the correct formatted field.

In the main ribbon go to Edit Queries. Once in the Power Query Editor, select the column to transform, go to the "Transform" tab, then "Split Column" and choose "By number of characters". In the first step the number of characters will be 4, as all of your years have 4 digits, select the option "Once, as far right as possible". This will leave you with two columns, one with the year and the other one with day + month. Choose the day + month column and do the same, but this time the number of characters has to be 2 (digits in your month). After this you will have the three columns I mentioned in the first paragraph and you can create the new column

Let me know if it works

Alejandro

[Anonymous]

Dear, 

I just tried it. 

I works also, both you solution and the other's one. 

Thanks for spending time to help me!! 

[Anonymous · Accepted solution]

in Power Query after you imported the table you will see "Change Type" under applied steps. Power Query tries (and does a pretty good job) at guessing at what your data types are.  Remove that step and see if you can set your date columns to date. If that errors that means there is something in that column that is preventing it from being converted to a date.

[Anonymous]

Hi ,

Thank you for your solution. I deleted the changed type step and transfrom it to date. 

It works perfectly

Thanks for spending time to help!!