Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
poojanambiar
New Member

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

pb1.PNG

 

 

pb3.PNG

 

 

pb2.PNG

 

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 🙂

1 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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

10 REPLIES 10
dvemana
New Member

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
Regular Visitor

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

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Example code:

 

DateFormat = Table.TransformColumns(NameOfPreviousStep, {"EVENT_DATE", each Date.From(Text.Combine(Splitter.SplitTextByLengths({4,2,2})(Text.From(_)),"/"),"en-EN"), type date})
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

This works like magic..Gr8

Anonymous
Not applicable

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

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

Thanks

 

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

 

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

 

 

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

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.