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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Convert Integer/ text to Date format

Hello all,

 

I have from date and to date columns with data as

 

From date

010318

050625

090524

 

i want to convert this to date format, when i change directly in power bi it is giving the wrong values. I also used Dax as

 

FROMDATE1 = LEFT(MC859A01[FROM DATE],2)&"/"&MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)
 
but when i convert this new column to date format, its throwing error again.
 
Can anyone tell me the solution for this.
 
TIA
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

HI @Anonymous 

For your formula, you just need to keep the format of this formula same with your local system.

For example:

In my computer, my date format is mm/dd/yyyy

1.JPG

So I adjust your formula as below:

FROMDATE1 = MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)&"/"& LEFT(MC859A01[FROM DATE],2)

2.JPG

Then change the datatype to date

3.JPG

 

Regards,

Lin

Community Support Team _ Lin
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

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From(Text.From("20"&[From Date], "en-US")))
in
    #"Added Custom"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lili6-msft
Community Support
Community Support

HI @Anonymous 

For your formula, you just need to keep the format of this formula same with your local system.

For example:

In my computer, my date format is mm/dd/yyyy

1.JPG

So I adjust your formula as below:

FROMDATE1 = MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)&"/"& LEFT(MC859A01[FROM DATE],2)

2.JPG

Then change the datatype to date

3.JPG

 

Regards,

Lin

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

@Anonymous in Power Query just use:

#date("yyyy string", "mm string", "dd string")

 

This should work

Anonymous
Not applicable

if possible, please give example

Anonymous
Not applicable

@JirkaZ I dint get you what you exactly mean..are you telling to create a new column in query editor. if so, column created in query editor cant be used in normal desktop

JirkaZ
Solution Specialist
Solution Specialist

@Anonymous that's exactly what I'm saying. Why wouldn't you be able to use a column created in Power Query in the report/desktop?

Anonymous
Not applicable

@JirkaZ 

#date("yyyy string", "mm string", "dd string")

 

is this any dax function?

 

JirkaZ
Solution Specialist
Solution Specialist

@Anonymous No - it's M definition for the new column

JirkaZ
Solution Specialist
Solution Specialist

@Anonymous Or you can use this definition of a column (assuming the dates are all after year 2000).Capture.JPG

 

 
Anonymous
Not applicable

@JirkaZ in the from date column few date values are 0. so when i use the given formula its throwing error as value .So, is it possible to replace this error with some blank date

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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