question

SonyaTong-7332 avatar image
0 Votes"
SonyaTong-7332 asked Lz-3068 commented

Power query, why can not connect to the sharepoint folder, can only connect to the site level?

I want to connect power query to the SharePoint folder, but I can only get to the top site level.
The function I used is EXCEL-DATA-GET DATA-GET DATA - FROM FILE-FROM SHAREPOINT FOLDER (I am using Microsoft 365 version)
Since there are thousands of files in the site, I would like to connect to a certain sub-folder.
My IT support told me he is not good at EXCEL, he can only see that it works if I connect to the site, and then filter the file name under the "File Path".

Currently I can only connect to the top level as below
https://xxxxxxx.sharepoint.com/sites/division name

But I want to connect to the sub-folder below the site level, example below
https://xxxxxxx.sharepoint.com/sites/division name/Register
https://xxxxxxx.sharepoint.com/sites/division name/Register/Reporting

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

@SonyaTong-7332. Thanks for updating your case. I don't see how I could help further as I don't have access (and never did) to a SharePoint site :(. All I know is what I read here and there

That said, the 7th pic. (pics. don't look the same but the process is the same) in SharePoint Folder clearly shows that when you use method Get Data > From File > From Sharepoint Folder, function SharePoint.Files is used:

102121-fromsharepointfolder.png

and the above picture is consistent with the "From Folder" in Miguel's article Connecting to Files in SharePoint & OneDrive with Power BI / Power Query
Later in that article he says: You can use a function by the name of SharePoint.Contents which will give you a completely new experience which looks more like a way to navigate through your folders in the event that you have way too many files

IMHO this is exactly what you need so I would recomment you follow Miguel's article and use the SharePoint.Contents function

Hope this makes sense & helps


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the insight
yes, I did tried the way shareponit,content, and tried the below URL
https://xxxxxxx.sharepoint.com/sites/division name/Register
https://xxxxxxx.sharepoint.com/sites/division name/Register/Reporting

it does not work, which shows error messages.

102120-error-messages.png


0 Votes 0 ·
error-messages.png (11.4 KiB)

@SonyaTong-7332

"Token Comma Expected" is a kind of generic error message saying your query code doesn't comply to the expected syntax

From your picture there's something obvious: double-quotes are missing around the sharepoint URL
Look at the pic. I posted earlier that uses SharePoint.Files. You must have:

 SharePoint.Content("https://xxx.sharepoint.com/sites/yyy/zz")
0 Votes 0 ·

Hi @SonyaTong-7332. How are things going? Are you know able to navigate to the desired folder?

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered SonyaTong-7332 commented

Hi @SonyaTong-7332

I might shoot in the dark as you don't explain which method (actually Power Query function: SharePoint.Files vs SharePoint.Contents) you use. I can only suspect it's SharePoint.Files whilst SharePoint.Contents would probably be closer to what you expect

Assuming my crystal ball works :) I would recommend you go through the 2nd part of Connecting to Files in SharePoint & OneDrive with Power BI / Power Query where the SharePoint.Contents experience is covered in details

Hope this helps & if this doesn't help at all share more information please. Nice day...

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The function I used is EXCEL-DATA-GET DATA-GET DATA - FROM FILE-FROM SHAREPOINT FOLDER (I am using Microsoft 365 version)

0 Votes 0 ·