Dataflows - failing to find Sharepoint files

[Anonymous]

I have successfully being using dataflows to retrieve excel files stored in Sharepoint for several months now.

Last week, I ran into repeated failures but when I try to edit the dataflows I get the following screen - the dataflow cannot see the file stored in sharepoint.

If I take the same code and run it in power bi desktop it can find the file succesfully.

This issue is making my project's code unsupportable as it cannot be edited - the evaluation step fails for any changes.

[v-yuta-msft]

@Anonymous ,

You need to replicate all the queries, functions and parameters by copying the power query code in power bi desktop to dataflow.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft - please note I have been successfully using dataflows to pull data from sharepoint Excel files for many months which has worked fine until last week. Something has changed within the power bi service which means the dataflow cannot see all the files within sharepoint.

[leed]

@v-yuta-msft  this is an issue that dataflow cannot read sharepoint that exceed a certain number of files.

We have pleinty of issue like that open actually.

[jonathankapoor]

I have found a fix. Remove the second paramter from SharePoint.Files function. Example below:

Doesn't work!

let
Source = SharePoint.Files("Root Folder", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "4 - All Company/Raw Data/CAM2")),
#"Filtered rows" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "CX"))
in
#"Filtered rows"

Works

let
Source = SharePoint.Files("Root Folder"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "4 - All Company/Raw Data/CAM2")),
#"Filtered rows" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "CX"))
in
#"Filtered rows"

[leed]

Hi @jonathankapoor 

Very interesting like approach! I will test it, on my side, I realized there is a limit to the number of sharepoint files in a sharepoint site that dataflow can read (surely an update whether from dataflow or sharepoint team on Feb 5th that caused it, anyway)

=> By combining these 2 solutions, I assume that we will soon have a new api version to connect to sharepoint folder.

Hopefully next time Microsoft will coordinate their services update, it starts happening a lot.

One day their service works, the other day nothing works.

[jeremyking77]

I tried using @jonathankapoor solution but for me i ended up with a 500 error from sharepoint

the only thing that worked for me was using the SharePoint.Contents source instead.. Then you have to drill through each folder to your folder contents

let
Source = SharePoint.Contents("https://xxxx.sharepoint.com/sites/xxxx/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"Reporting & Analysis" = #"Shared Documents"{[Name="Reporting & Analysis"]}[Content]
in 
#"Reporting & Analysis"

i wrote this on here to

https://community.powerbi.com/t5/Service/Dataflows-Import-from-Sharepoint-folder/td-p/574306

[Anonymous]

Thanks @jeremyking77 , I also saw the 500 error when trying @jonathankapoor suggestion.

After this issue I am migrating away from using dataflows as I have found them to be too unreliable.  It also doesn't fit in with company's architectural model so switching to a function app/Azure custom solution.

[jonathankapoor]

Hi all,

I now have a new issue and I get the following error message (500) Internal Error.

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)
Details:
DataSourceKind=SharePoint
DataSourcePath=hidden address/_vti_bin/ListData.svc/SolutionGallery
SPRequestGuid=hidden
Url=hidden address/_vti_bin/ListData.svc/SolutionGallery

This occurs both in PBI Desktop and PQ in Dataflows

[jeremyking77]

Hey @jonathankapoor , thats why i ended up using Sharepoint.Contents rather than Sharepoint.Files

When i said 500 error.. i had the same as what you just posted

So in dataflows for me:

Sharepoint.Files (no api specified) --> it wouldnt list the full list of files

Sharepoint.Files ([ApiVersion]=15) --> it would get the 500 error

Sharepoint.Contents --> it gets one directory at a time, so i navigated through tables to get to my folder from the base of the site and imported files from there

PBI desktop is still currently working for me

[leed]

Any news on that, issue still there on my site.