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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Dataflows - failing to find Sharepoint files

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.

dataflow issue.png

Status: New
Comments
v-yuta-msft
Community Support

@cz8vyv ,

 

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

cz8vyv
Advocate I
@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
Advocate II

@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
Frequent Visitor

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
Advocate II

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
Helper I

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

cz8vyv
Advocate I

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
Frequent Visitor

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
Helper I

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
Advocate II

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