cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kimmend
Frequent Visitor

Sharepoint folder date problem

After updating to March 2018 version of PBI desktop, the Sharepoint folder integration is off when it comes to the Date modified and created.

 

The error shown is:

DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value.
Details:
    2/26/2018 1:01 AM

 

I have tried to change the the data type to "Using Locale..." but without luck.

 

Capture.JPG

 

 

 

1 ACCEPTED SOLUTION

17 REPLIES 17
Toerstad
Advocate I
Advocate I

Hi.

 

We encountered the same issue right now. We solved it by extracting and trasforming the Error.Detail

 

1. Create a cauculated column with the following formula

=try([Date modified])

 

2. Expand the records in the new column

 

3. Expand the records in the new ".Error"-column 

 

4. Trasform the new ".Error.Detail"-column and the [Date modified]-columns to a datetime value with the correct locale.

 

5. Merge the two columns with a new calculated column.

 

6. Delete the two "old" date columns afterwards.

 

7. Rename the new datetime column "Date modified", and voila! 

mgp4444
Regular Visitor

I haven't been able to find any workaround for this issue -- so basically I can't pull 'date created' from a sharepoint folder source.  This is a significant impact/issue.

 

Anonymous
Not applicable

So I've come across this problem, but can't seem to find a solution.  The last post here was six months ago.  Is there a workaround?  I tried changing the API to 14 but got a data source error instead.

The work-around I used is to set the regionalization on my sharepoint consistent with the one I'm using in Power BI, since my hunch is that the API is having issues when having different date formats (ie. mm/dd/yyyy versus dd/mm/yyyy)... eventual timezone shift you still can compensate in your power query logic.

 

I still don't understand why the Power BI support team needs votes to resolve bugs that harm global implementations.

Anonymous
Not applicable

Thanks for the tip.  I was able to get it to work.

I think part of the problem about getting this fixed is that it's marked "Solved" when it so clearly isn't.

 

thanks,

 

I agree with @Anonymous - I cannot understand how saying that it is bug and not offering any work around would constitute a 'solution'. A 'response' maybe, but not a 'solution'.

 

The handling of DateTime data in Power BI frustrates me - it seems that unless you are using a US locale, you have to move mountains to get your DateTime data working in the way you want, and until Power BI becomes less US-centric, I fear that this will continue to be the way.

 

As a workaround, what I have done is loaded data from my SharePoint document library using the 'SharePoint Folder' Get Data option (to load in the binary data of the (in my case) Excel files, and then also loaded data from the same SharePoint document library but used the 'SharePoint Online List' Get Data option.

 

From there, I have created a one to one relationship between Source.Name (from the SharePoint Folder query) and the File.Name (from the SharePoint Online List query).

 

This allows me to display all data (including the modified/created date data) from the SharePoint document library metadata (i.e. SharePoint columns) alongside actual content from my corresponding Excel files.

 

Ahchay
Regular Visitor

Nice workaround @JoeFox   - I've found that using ServerRelativeUrl (after a little light editing) has worked better than the file name.

 

My organisation is on the six-month update cycle so if this started happening in March 18 then at least two updates since then haven't fixed it. I'm using Excel Power query but, it's the same issue. Thanks for this suggestion (export the sharepoint list data to Excel using the provided SP routine to do that).   It looks like it will work for my purpose as a workaround. This should be fixed properly of course. Still as long as these more intelligent solutions keeping making things harder it keeps some of us in jobs 🙂

Yes, I can confirm that this worked fine, and yes, I had to edit the url a little, I have since then been able to call the refresh from VBA, so, worked OK really. Would still be nice to be able to simply navigate to a file as used to be possible with fileshare (I know that if you edit the URL and add it to file explorer (this pc), whatever it's called now, you can do this, but that takes advanced planning).

mgp4444
Regular Visitor

I haven't been able to find any workaround for this issue -- so basically I can't pull 'date created' from a sharepoint folder source.  This is a significant impact/issue.

 

v-shex-msft
Community Support
Community Support

Hi @kimmend,

 

According to error message, it seems like you are try to convert date value to datetimezone but failed.
Did these values works in normal data type? Can you please share a part of sample data for testing?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft, thanks for the reply. The problem is that I am not trying to convert anything, this is how it looks when the data is pulled in from Sharepoint folder. 

 

Already when the query is presented before editing the query, it shows errors for most dates. This wasn't the case before the March update. Does it have something to do with the regional settings of Power BI? 

 

Date error.png

This is a bug in March issue and unfortunately also on the Service.

 

http://community.powerbi.com/t5/Integrations-with-Files-and/Date-issues-with-Sharepoint-file-sources...

kimmend
Frequent Visitor

Thanks for this @Sascha1. Let's hope that an update fixes this.

Hi @kimmend, I reported it as a bug here, perhaps voting will help

@Sascha1,
Probably a stupid question, but how can I vote?

Actually, I reported the issue on 13th of March here

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!