Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Dataflows Connecting to Sharepoint Online Files

Hi All 

 

I have a problem with my dataflow, it was working fine until yesterday and now it keeps failing.

 

I've created a connection using SharePoint.Files( "", [ApiVersion = 15] ) this was returning a table with all files within specific workspace.

 

PBI Desktop returned all the files and loads of nulls ( no idea what are the nulls in SP folders  ) where the Dataflow only a few files ( records ) until the first null, so I could not see all the files past this point.
image.png

 

To solve the problem I have filtered out the nulls and this worked fine, it returned a list of all the files ignoring the nulls. 

 

For some reason, as of yesterday this workaround does not work anymore and the dataflow version shows only the first few records ( as on the above picture ) and it does not get past null so I'm unable to see all the files in my SharePoint folder.

 

I've tried to change the ApiVersion to 14, which removes a problem of nulls in a table and lists all the file names and I have no problems if I wont to refer to a single file, however when I try to combine multiple files and I use Table.SelectRows() to select those, It adds an extra error row, and this row for some reason can not be removed
image.png

 

Have there been any updates to Dataflows / PBI Service or Sharepoint Connector recently that could cause this?

 

Best Regards,
Mariusz

 

Status: New
Comments
leed
Advocate II

I have pretty much same issue on powerbi dataflow.

Mine uses source "Sharepoint folder" and shows empty everywhere. It doesnt show up as error, just whole blank (ref attach.)

2020-02-06_17-07-53.jpg

 

 

 

 

 

Thank you,

PLG
Regular Visitor

Same problem with Dataflows (PRO version) connecting to SharePoint files (csv and xlsx). On 5 February 2020 all was ok. On 6th it failed. In Desktop all was still OK. 
I have done several tests:

1/ moving files to another folder of the same Sharepoint: still failing

2/ moving files to another Sharepoint (which does not contain many files): The files then became accessible again! 

Power Query online therefore seems to have limitations on the number of addressable files (only the 1005 oldest items from the SharePoint appears to be availaible)

As a temporary (or maybe definitive) solution, I moved the requested files to a newly created SharePoint site and changed the data source to this new SharePoint and now it works fine! 

eFeM135
Advocate II

It is my impression that the locale and timezone of my sharepoints ( Site Settings / Site Administration / Regional Settings ) changed from Dutch (Netherlands) back to English (US). As a result the local is back to the Microsoft default (en-US) mm/dd/yyyy instead of the expecte (nl-NL) dd/mm/yyyy, introducing the well known issues with API Version 15 again (https://community.powerbi.com/t5/Issues/Date-bug-with-Sharepoint-file-sources/idi-p/384517 ).

I suggest you ask your site administrator to validate settings and verify whether this solves your problem.

leed
Advocate II

From MS: 

https://community.powerbi.com/t5/Issues/Dataflows-failing-to-find-Sharepoint-files/idc-p/942390#M538...

 

 imprsnt
"Hello all, 
we have identied the issue. The fix will be deployed to prod starting next week. 

Sorry for the inconvenience. 

Thanks

-prashant

"

PLG
Regular Visitor
Thanks you for this information 🙂
Using the connector SharePoint.Content instead of SharePoint.Files, it worked fine !
Since 23 feb, the file number limitation has disappered and the initial solution with SharePoint.Files seems to work again… but we face very long actualisation time even for basic queries… and many times it ends in "Error  : Refreshing an entity exceeded the 2 hour refresh limit."
Hope for a quick fix and return to reliable Dataflows operation...