SharePoint path not working

Mederic · ‎10-14-2023

Hello,

I'm trying to connect to a SharePoint folder in Power query (Excel 365).
I get an error message like this: Incorrect request (400)....etc
For information, the path is very long with many folders and subfolders

When I use a folder near to the Https root, it works

Is there a maximum length for the path?

If not, how can I get round this problem?

Thanks in advance

Best regards

Mederic · ‎10-20-2023

Hello @christinepayton ,

Sorry for the late return,
I tested today at my office with the "ODATA" connector but unfortunately I can't connect. It keeps asking me to sign in to an account.

Thanks anyway for your help

Best regards

Mederic · ‎10-21-2023

That's right, I don't get an error message but I do get an authentication request every time.
I couldn't find the option for Browser authorisation in Ofiice 365,
I'll do some research to see if it's possible.

Thank you

Best regards

christinepayton · ‎10-21-2023

It's this one:

christinepayton · ‎10-20-2023

If it asks you to sign in repeatedly with no error, the fix for that is to go into the Power BI Desktop settings under Security and set it to use your default browser for authentication. I have had this happen to me too at various points... I don't know why it should matter, but it seems to resolve it.

Mederic · ‎10-17-2023

Hello @christinepayton ,

Thank you for another interesting suggestion.
I've just tested it with the Odata connector but unfortunately I didn't get any results.
I'm going to try it tomorrow from our company connection because at home I have to get special approval to access SharePoint (connection security via smartphone).

I will test this tomorrow from my office

For information, yesterday I said that the full URL with the Excel file works. I'll just point out that it's with a Web connector.
Have a good evening

Best regards

Mederic · ‎10-16-2023

Hello @v-xinruzhu-msft , @christinepayton ,

Thank you for your answers,
I'm trying to use a Folder URL starting with https://, by "SharePoint Folder" connector in Office 365 and not in Power BI.
When I use the full path, I get an error message
When I use part of the path for example: "https://kkkk.com/:f:/r/site/document"
This works but returns several tens of thousands of results with all the files and I stop the process manually after a few minutes.

I finally used the URL directly with the Excel file: "https://kkkk.com/:f:/r/site/document/....../...../....../ExcelFile.xlsm".
This path is very long but works.

I don't have administrator rights to try to understand or modify the SharePoint path parameters.

The original idea was to be able to select the most recent file in a folder.

Finally, using a VBA macro, I'm going to manually select the path to the Excel file in a cell and then send it to Power Query.

Thanks

Best regards

christinepayton · ‎10-16-2023

You can use the OData connector to get both the most recent file and path and have it directly in Power Query vs running a macro if you want. The SP Folder connector is more for combining Excel file data; if you don't need the content of the file it's much easier to use the OData. I did a video on it a ways back if it helps: https://youtu.be/yeIQ9jKln_I?si=quk3f-RaCxoDiOF2

christinepayton · ‎10-16-2023

The PBI folder connector requires the site URL, not the folder URL. You can then filter on the path in Power Query as one of the columns. SharePoint itself has limits on maximum path length, but if you're able to edit the file in SharePoint then it's probably okay.