Forum Discussion

Post Patron

5 years ago

Solved

Error Querying Folders of CSV Files Containing Date Column

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!

Need Help

edhans
5 years ago
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.

7 Replies

Anonymous
Not applicable
5 years ago
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
Community Champion
5 years ago
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
  Post Patron
  5 years ago
  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
    Community Champion
    5 years ago
    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.