Error Querying Folders of CSV Files Containing Date Column

[patri0t82]

Hello,

I am querying a SharePoint / Teams folder that contains hundreds (if not thousands) of CSV files. There is a TimeStamp field that contains a value 1/1/0001 12:00:00 AM several times in each file.

Even though I filter it out in my Transform Sample File step, I still receive the error in my main query:

DataSource.Error: SharePoint: Request failed: https://xxxx.sharepoint.com/sites/xxxx/Shared Documents/General/xxxx/2020 Temperature Data/04-28-2020/xxxx_csv/_api/contextinfo
Details:
DataSourceKind=SharePoint
DataSourcePath=https://xxxx.sharepoint.com/sites/xxxx
SPRequestGuid=42c5e69f-b047-c000-4eaf-900690c0f13f

It seems no matter what I do, that bad date cell is causing problems and I have no way of fixing it. I'm really hoping someone can help with this!

[watkinnc]

I would change the "Timestamp" column to type text in my transform file, and then use Table.SelecrRows to filter out those rows:

= Table.SelectRows(TableOrPriorStepName, each not Text.Contains([Timestamp], "0001"))

Then you can change the type back to datetime.

--Nate

[PC2790]

Look like t=your connection to data source is getting failed from Power BI.

Can you try connecting to the same sharepoint link manually?

And if it is a problem with the date, you will not be able to do it manually as well

[patri0t82]

Thank you for your response.

I'm not 100% sure I know what you mean, but yes I can access the files within SharePoint and *view only* the CSVs

[edhans · Accepted solution]

When you are combining the files in SHarePoint to load, it is doing an automatic Changed Type step. and all of those 0001's  are blocking it.

I assume you are using the SharePoint File Combine operation. Disable the Type Detection. Then reconnect and recombine the files.

In the Transform Sample query that would be done, make sure there is a Promote First Row as Headers step.

Additionally, you could do a find/replace here too. FInd that time stamp and either replace it with null (all lower case - it is a special keyword - or replace it with a legit date. 

Then change the data types.

Whatever is done in the Transform Sample File step is done for each file separately before it is passed and added to the Query1 query - the real query you will use in your data source.

What I suspect was happening is it combined some files, the first file was good so it said "That is a date column" but then when it tried to change the type of the 0001 dates, it created an error.

At this point, you should be able to go back to your main query and have no errors - at least now with this particular field. 

[patri0t82]

Thank you for the detailed response - I hadn't thought of adjusting the Data Load configuration. I will give that a try. Your help has been appreciated!

[edhans]

Glad to assist @patri0t82 

[patri0t82]

Ah forgive me, I tried copying several of the folders to my computer, containing a file that was causing errors, and it seems to be loading fine. 

So if it's not an issue with the date, what kind of connection issue could it be to SharePoint, and are there any solutions that you're aware of?