Microsoft.OleDb.Date value - Issues with column type date with R

[smpa01]

Hello Experts,

Everytime I have a date column in my dataset on which I run a R script, it returns a weird value.

For e.g. these two lines

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1MlXSUTI0MFCK1YEJGuoaGQAFjVAEgcgCKGgMEowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Integer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Integer", Int64.Type}})

  returns

Date	Integer
12/25/2020	100
11/20/2020	200
10/18/2020	300

If I write further two lines of R

#"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)x<-data.frame(dataset)",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

it turn to this

Date	Integer
Microsoft.OleDb.Date	100
Microsoft.OleDb.Date	200
Microsoft.OleDb.Date	300

The whole script is following

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1MlXSUTI0MFCK1YEJGuoaGQAFjVAEgcgCKGgMEowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Integer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Integer", Int64.Type}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)x<-data.frame(dataset)",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

Beyond base R I tried other libraries as well and everythime it returns the same.

For e.g. with data.table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1MlXSUTI0MFCK1YEJGuoaGQAFjVAEgcgCKGgMEowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Integer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Integer", Int64.Type}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(data.table)#(lf)x<-data.table(dataset)",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

It is giving me a hard time. Does anyone know how to debug this please.

Thank you in advance.

[smpa01 · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1MlXSUTI0MFCK1YEJGuoaGQAFjVAEgcgCKGgMEowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Integer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Integer", Int64.Type}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(data.table)#(lf)x<-data.table(dataset)",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value],
    #"Changed Type1" = Table.TransformColumnTypes(#"""x""",{{"Date", type date}})
in
    #"Changed Type1"