How to transform integer to specific date format?

[Anonymous]

Hi Guys,

I have a small issue in my current dataset. The value of the column IDT_DTM_ID consist a wrong date value. Check the image here below.

I would like to transform 20170102 into a date type 02-01-2017.

Can you help me out?

Kind regards,

Rega

[davidding]

Convert the date int into text and from text into date with the following function combination.

Date.From( Text.From( [Date_Int_Column] ) )

[MalS · Accepted solution]

In the Query Editor, you can click on the Transform tab, then the Data Type dropdown and select Date. 

If that doesn't work then maybe regional date settings are messing it up (Power BI might expect year, day, month instead of year, month, day) . 

If so, you can try extracting data into three new columns, then merging them back together into a date format that Power BI can recognize. For example:

1. Click Add Column tab and select Extract > Range and set Starting Index to 4 and Number of Characters to 2. That will get you the month in its own column

2. Select Extract > Last Characters and set Count to 2. That will get you the day.

3. Select Extract > First Characters and set Count to 4. That will get you the year

4. Click the Transform tab. Select the three new columns and click Merge Columns and set the separator to Custom, use a hyphen as the separator, then enter a column name

5. Finally change the Data Type to Date.

(If this doesn't work, go back to step 2 and swap the order of the day and month columns and try again)

[Anonymous]

Thanks bro!