SharePoint connection within PowerQuery/PowerPivot

[Harry111]

Hi Ken,

My Source data files are located on my corporate network on Share Point. if I want to connect to the Share Point (via RNA or from home), I must need to provide my Login credentials. My current situation is that I have a VBA code in excel file which can mapped Share Point link to My Computer with Login prompt. Is there any built in solution available in Power Query which can use Windows Login (existing user) directly without any other work around.

 

[Ken Puls]

Hey,

So there are three methods to connect to Sharepoint from Power Query:

SharePoint.Files("https://url") SharePoint.Contents("https://url") SharePoint.Tables("https://url") The method you need depends on which you're after. When you try to connect to one of these the first time, you should get prompted to enter your credentials, which will then be cached in your user profile.

 

[Harry111]

Thanks Ken.

 

[n.kehayova]

Ok, I have a question regarding the prompt for credentials.
Is there a way to automate it with VBA to log with the AD credentials on the first update of the query? The whole thing to remain invisible to the user?

 

[Ken Puls]

Unfortunately no. But if the user knows their AD credentials, it shouldn't be an issue. If you think the user needs more help to pick the right setup, you could use VBA to prompt them with a "You're about to be asked to log in. Please choose this option and enter your normal user ID and password" or something.

 

[n.kehayova]

Unfortunately no. But if the user knows their AD credentials, it shouldn't be an issue. If you think the user needs more help to pick the right setup, you could use VBA to prompt them with a "You're about to be asked to log in. Please choose this option and enter your normal user ID and password" or something.

Ok, thanks