Dates import as Null while Querying from Sharepoint folder

[mkeeping]

I have an xlsx file in a Sharepoint folder and I create a query to import it to Power Bi. It has several date columns but they all show up as having null as the value. If I open the file the dates are there. If I download the file from Sharepoint and import  excel file the dates show up correctly. I get two different behavours when importing the same excel file into PowerBI. Is anyone else able to reproduce this?

[Syndicate_Admin]

Good! Did you solve the problem? The same thing just happened to me and I think it's not a problem of the file being in Sharepoint or a local folder. In my case the problem was that the excel format, although it was xlsx, the file type was "Strict OpenXML spreadsheet (*.xlsx)". As soon as I changed it to "Excel workbook (*.xlsx)" it has already recognized the dates without any problem.

[Syndicate_Admin]

Hello good day, I have the same problem. When you want to upload an excel file that contains dates, PBI detects them as null, I already tried changing the format from excel and it still does not detecarlas. how can I get PBI to read the dates?

[dax]

Hi mkeeping,

If possible, could you please inform me how you connect to sharepoint folder? When you connect to sharepoint folder , you need to use URL like below

https://<your company>.sharepoint.com/teams/<your site>

Or you also could try to connect to it by web like below

https://<your company>.sharepoint.com/teams/<your site>/Shared%20Documents/Book3.xlsx

Best Regards,
Zoe Zhi

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

[mkeeping]

I connect to the folder then do a transform in order to navigate to the specific table.

https://<your company>.sharepoint.com/teams/<your site>

[dax]

Hi mkeeping,

So may I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer. This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[mkeeping]

The issue is still not resolved. I cannot connect to using the web connector as I get an error that indicates "access to the resource is forbidden".

I can connect via Sharepoint folder but the all of the dates in the table still show up as null.

[dax]

Hi mkeeping,

When you connect  by web, you could get folder path like below(open file in app, the copy the path from Excel, and remove ?web=1 in path), then it should work

In addition, you said that the data in Excel will return null, you could see whether there is other chart or pivot table in it which might cause problem. You could try the sample(just have the data in it) to see whether it work or not.

Best Regards,
Zoe Zhi

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

[mkeeping]

I'm not sure that connecting to the file via the web is really that important for this issue as I can connect to it using Sharepoint Folder. The primary issue that I need to resolves is that the date columns show up as null and the rest of the data shows up correctly  when the data comes from Sharepoint: 

When I download the same xlsx file from Sharepoint and connect to it from the local copy the dates show up correctly:

Two different import behaviours on the same file.

Regards,

Max

[dax]

Hi mkeeping,

This is a little strange, I test this in my environment, which work well. If you open the Excel in sharepoint, will the date field show null? In addition, if possible, could you try to share the Excel for me to test this (grant me the permission to the Excel (you could use a sample instead of real data) in your sharepoint site, then I will try to test this in my environment).

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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