Power Query - Formula Error (Custom Column)

[Anonymous]

Hi Experts...

I cannot work out how to reslove the following error message. 
My capture_day_id is in the format 2020101 and not 01/01/20

My expiration_day_id is in the format 2020101 and not 01/01/20

I am trying to work out the duration in days between the two dates.

List.Dates(
Date.FromText([capture_day_id]),

Duration.Days(
#date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,2)),Number.FromText(Text.End([capture_day_id],2)))
,
#date(Number.FromText(Text.Start([expiration_day_id],4)), Number.FromText(Text.Middle([expiration_day_id],4,2)),Number.FromText(Text.End([expiration_day_id],2)))
),

#duration(1, 0, 0, 0))

[amitchandak · Accepted solution]

@Anonymous , try Date.FromText([capture_day_id])

refer https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

[Anonymous]

Same error message

[amitchandak]

@Anonymous , Yes, I also checked out. if only MM can be 1,2, 3 etc then we can do like

if then Text.Length(Text.Length) = 7 then #date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,1)),Number.FromText(Text.End([capture_day_id],2)))
else #date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,2)),Number.FromText(Text.End([capture_day_id],2)))

[Anonymous]

Slightly confused - can you add the full formula PLEASE. Thanks