How to convert 8-digit number to date in power bi

[poojanambiar]

The above shown method: righ clicking and tranforming the date is showing error.

Is there any other option other than changing the format from excel? 

Thanks in advance 🙂

[dvemana]

Goto==>Power Query==>Add Column Tab==>Column with example==> give three rows in the format you required like 02041987 to 02-04-1987 or 02/04/1987.. you will get the new column after you can change the data type to date

[MSAKRAM]

1.Go to Edit Query and choose the column "EVENT_NAME" (Your 8 digit integer column to convert)
2.Choose 'Transform' from the menu bar and select 'Text' from the Datatype drop down.
3.Select 'Add new step'  from the popup shown
4.Again go to the 'Transform' from the menu bar and select 'Date' from the Datatype drop down.
5.Select 'Add new step' from the popup shown

[MattAllington · Accepted solution]

I think you will need to split the column into day, month and year, then combine back together with a date separator like 23/01/2016 and then convert. It should be quite straight forward. 

[MarcelBeug]

Example code:

DateFormat = Table.TransformColumns(NameOfPreviousStep, {"EVENT_DATE", each Date.From(Text.Combine(Splitter.SplitTextByLengths({4,2,2})(Text.From(_)),"/"),"en-EN"), type date})

[Anonymous]

This works like magic..Gr8

[Anonymous]

splitting and combining back worked though, why Power BI doesn't convert straightly by changing data type?

[ven853]

Can't get this to work.  Please explain what each part of formula means so I can adjust to fit my spreadsheet.

Thanks

[dyee4613]

Here are the steps:  Example number - 20100506

1) Go into your query editor.  Click on add column

2) Click on the 8 digit column and click split column --> by number of characters --> once, as far right as possible.  Select 2 characters.  Outcome: 201005  06

3) Repeat step 2 with the 201005 column.  Outcome: 2010 05 06

4) Highlight the three columns (2010, 05, 06) and click merge columns.  Select custom option and put in a /

5) Switch the format to date.

6) Optional: delete old columns

[Ghuiles]

hi!

The solution of @MarcelBeug works 🙂 so thank you Marcel!

For the future readers, some explanations.

The formula from Marcel:

DateFormat = Table.TransformColumns(NameOfPreviousStep, {"EVENT_DATE", each Date.From(Text.Combine(Splitter.SplitTextByLengths({4,2,2})(Text.From(_)),"/"),"en-EN"), type date})

This is working in the query editor

- Dateformat is the name of your line in the editor. Usually there is an #" (sharp sign and quotation mark) before and a " after. But because here the name does not have a space in it, you don t need thes #" "

By the way, when inserting this command, do not forget to end the previous line with a comma.

Then the magic:

- Table.TransformColumns will make that you don t need to add a column for that operation. The result will be done in the same column as where you are, meaning "EVENT_DATE"

- NameOfPreviousStep is the name of the line before in your query editor.

- "EVENT_DATE" is the name of the column where the transformation should be done

- each means for each row

- Date.From is transforming the text done in the parenthesis into the date format

- Text.Combine means concatenate

That's it for the main things

Cheers. G

[ven853]

This is not working for me.  Can you please explain what each part means, so I can adjust to fit my spreadsheet.

Thanks