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
Anonymous
Not applicable

How to convert text 20150714 to date?

I have Excel as data source and date is coming as 20150714. How to parse or convert to date format?

And how to make month slicer?

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

As the text value "20150714" is not formatted as standard date format, it's not able to convert it to Date data type directly.

 

In your scenario, you can try the DAX shared by @joerykeizer,

 

a2.PNG

 

Also you can create a Custom Column in Query Editor follow below formula:

 

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

 

q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

As the text value "20150714" is not formatted as standard date format, it's not able to convert it to Date data type directly.

 

In your scenario, you can try the DAX shared by @joerykeizer,

 

a2.PNG

 

Also you can create a Custom Column in Query Editor follow below formula:

 

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

 

q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Text To Date

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

 

Number to Date

=Date.FromText(Text.Range(Number.ToText([Column1]),0,4) & (Text.Range(Number.ToText([Column1]),4,2)& (Text.Range(Number.ToText([Column1]),6,2))))

BhaveshPatel
Community Champion
Community Champion

Just select the data type to "Date" by right clicking the column in Query ediotr to convert it into date column. 

 

For a month Slicer, You can drag and drop the date field to the slicer visual and select only month.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Anonymous
Not applicable

Modeling => Data Type: Date = > "We can't automatically convert this column to date type"

Not sure if it's the best way, but you could create a new column like this:

Date = DATE(LEFT([DateText];4);MID([DateTest];5;2);RIGHT([DateText];2))

You specify which part of the text is year, month and day.

 

Btw you could also do this in the Query Editor by creating a new column:

=Date.FromText([DateText],"en-Us")

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.