Forum Discussion
Sharepoint folder does not show up in Power BI's Power Query
I am working on a project that involves an Excel file that lives in a folder in my company's Sharepoint site. I went into Power BI, fired up the Sharepoint Folder connector, and opened it up in Power Query, and it seems to work fine except for the fact that the file I need (or the folder it lives in for that matter) just doesn't show up.
I am 100% sure to be using the correct URL, seeing as I have been using it successfully for other things. I saw a post on this forum made by someone with a similar issue, and theirs seem to have been resolved by just waiting for the Sharepoint backend to do its thing (something related to cache propagation). Is there a way to force that process to happen faster, without having to wait 3-5 days for my files to show up?
A couple of things to consider.
First, if you are using a single Excel file from SharePoint you do not need the SharePoint Folder connector. You can use Excel.Workbook(Web.Contents("fileURL")). This is way quicker from a performance perspective.
Second, by default Power Query only lists the first 1000 rows of a given query. If the SharePoint folder you are querying has more than 1000 items then it is possible your file is there, just not being displayed. You can change the 1000 rows limit with the button on the lower left corner of the query screen.
There is no way you should have to wait 3-5 days for your files to show up.Hope this helps.
3 Replies
jgeddesSuper User
A couple of things to consider.
First, if you are using a single Excel file from SharePoint you do not need the SharePoint Folder connector. You can use Excel.Workbook(Web.Contents("fileURL")). This is way quicker from a performance perspective.
Second, by default Power Query only lists the first 1000 rows of a given query. If the SharePoint folder you are querying has more than 1000 items then it is possible your file is there, just not being displayed. You can change the 1000 rows limit with the button on the lower left corner of the query screen.
There is no way you should have to wait 3-5 days for your files to show up.Hope this helps.
Thank you so much for your response, the preview limit of 1000 rows was indeed the issue.
However, I didn't find the button in the lower left corner of the query screen that you mentioned... might just be me not looking in the right place though. Anyway, I solved the 1000 rows limit issue by replacing source = Sharepoint.Files for source = Sharepoint.Contents, then navigating all the way to the folder I needed (disclaimer: I need the Sharepoint Folder connector because more files will eventually be added to that folder and I need to take those into account too).
Thanks again for the reply!- jgeddes
I didn't have PBI open when I wrote my initial response. Here is the "button" in the Power Query Editor.
Glad you were able to solve your issue.
