Convert Integer/ text to Date format

[Anonymous]

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

[Ashish_Mathur]

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.

[v-lili6-msft · Accepted solution]

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

So I adjust your formula as below:

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

Then change the datatype to date

Regards,

Lin

[JirkaZ]

@Anonymous in Power Query just use:

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

This should work

[Anonymous]

if possible, please give example

[Anonymous]

@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]

@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]

@JirkaZ 

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

is this any dax function?

[JirkaZ]

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

[JirkaZ]

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

[Anonymous]

@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