Forum Discussion

vicky2123131's avatar
vicky2123131
Frequent Visitor
3 years ago

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

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))
Need Help
Show and Tell

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.