Cannot convert value '6/4/' of type Text to type Integer.

[vicky2123131]

Hi,

I'm getting this error " Cannot convert value '6/4/' of type Text to type Integer. " 

below screen shot id the sample "created date" column data

Below is the calculated column used:

In Created Date = Date(Left(LOOKUPVALUE('Dim_In'[CreatedDate],

Dim_In[Id],

Vw_GC_In_Benefit_Pvt_Data_SnapShot[In ID]),4),Mid(LOOKUPVALUE('Dim_In'[CreatedDate],

Dim_In[Id],

Vw_GC_In_Benefit_Pvt_Data_SnapShot[In ID]),6,2),Mid(LOOKUPVALUE('Dim_In'[CreatedDate],

Dim_In[Id],

Vw_GC_In_Benefit_Pvt_Data_SnapShot[In ID]),9,2))

[v-jianboli-msft]

Hi @vicky2123131 ,

Please try:

In Created Date =
VAR _date =
    LOOKUPVALUE (
        'Dim_In'[CreatedDate],
        Dim_In[Id], Vw_GC_In_Benefit_Pvt_Data_SnapShot[In ID]
    )
VAR _month =
    LEFT ( _date, FIND ( "/", _date, 1 ) - 1 )
VAR _day =
    MID (
        [Column1],
        FIND ( "/", _date, 1 ) + 1,
        FIND ( "/", _date, FIND ( "/", _date, 1 ) + 1 )
            - FIND ( "/", _date, 1 ) - 1
    )
VAR _year =
    RIGHT ( _date, 4 )
RETURN
    DATE ( _year, _month, _day )

Or 

In Created Date2 =
VAR _date =
    LOOKUPVALUE (
        'Dim_In'[CreatedDate],
        Dim_In[Id], Vw_GC_In_Benefit_Pvt_Data_SnapShot[In ID]
    )
RETURN
    DATE ( YEAR ( _date ), MONTH ( _date ), DAY ( _date ) )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx]

Share sample pbix file to help you.

[lbendlin]

looks like the year is missing from your data, and Power Query is guessing that you mean the current year.