topic Re: Excel Date imported as null by Source/Navigation in Developer

Excel Date imported as null by Source/Navigation

MikeCSmith — Mon, 18 Jun 2018 13:20:58 GMT

I am importing an excel data extract created by JIRA that contains dates in columns 6 through 10. In stead of bringing these in as data/time or even text it converts the data to null (blank in the sheet 'Imported Sheet'). The underlying value in excel is a date/time serial number.

The data in the extract is 10/29/2015 14:26. The underlying date serial number is 42306.60139.

I was hoping to attach a sample of the source extract and get data, but I don't see anywhere to attach a file to this post.

Here is the generated M code.

let
Source = Excel.Workbook(File.Contents("Z:\SYSPLAN\AssetMgt\Metric Workbook\Asset Management Support Metrics\Test Recs.xlsx"), null, true),
general_report1 = Source{[Name="general_report"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(general_report1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}})
in
#"Changed

Re: Excel Date imported as null by Source/Navigation

v-jiascu-msft — Wed, 20 Jun 2018 05:29:57 GMT

Hi @MikeCSmith,

That's strange. You can upload your file to the cloud drive like OneDrive, GoogleDrive, then share the download link here. Please mask your private data first.

Best Regards,

Dale

Re: Excel Date imported as null by Source/Navigation

MikeCSmith — Wed, 20 Jun 2018 17:13:09 GMT

Our company policies will not let me post a file to the cloud outside of our firewall.

This is a pic of the relevant data in the query preview window as a result of "Get Data From Workbook."

Here is a pic of the data I'm attempting to import.

If I change the import file column format to 'General' then the data becomes a decimal date/time serial number and everything imports correctly. But I want to avoid making the user modify anything in the extract data.