“connection cache” when accessing Sharepoint

[fiveone]

I need to get data from a sharepoint list.

The first time I enter the URL the connection succeeds and I’m being prompted with several login options. but I probably chose the wrong one because I got an error message (HTTP 404).

The real issue is that I can’t get through that process again.

It is as if PBI had cached some of the connection information and went straight away to connecting with my last (and only…) connection method.

I got a colleague to try it out – the first time it connects and he is presented with login options. Login fails. From then on he also gets a 404 error right away and is never presented with the possibility to try other login options.

Does that ring a bell to anyone?

How we can we clear the “connection cache”?

Thanks!

[fiveone]

Oh, and before anyone asks it does not make a difference if I use the URL of the list

http://ensemble-mtl.ent.cginet/sites/200154936GlobalEngagement/Lists/Staffing%20Status%20DB_Test1%20...

or the site root

http://ensemble-mtl.ent.cginet/sites/200154936GlobalEngagement

It actually it seems to work better wiht the URL of the list because at least I am able to get the connection (the first time).

whereas with root URL I get

And I have also been able to confirm I can connect to Sharepoint by putting an Excel file in a folder and connecting to it using 

    Source = Excel.Workbook(Web.Contents("http://ensemble-mtl.ent.cginet/sites/200154936GlobalEngagement/test/SG_Staffing%20Status_BD.xlsx"), null, true),

the option that get me through is Windows/Use my current credentials

I just wish I could try that again with my attempt to connect to the list...

[v-caliao-msft]

Hi @fiveone,

I have tested it on my local environment, I can connect to Sharepoint online list without any issue.

Please refer to the link below.
http://www.netwoven.com/2016/07/analyzing-sharepoint-list-data-using-power-bi-2/

Regards,

Charlie Liao

[fiveone]

Thanks Charlie for the follow-up.

In my case I'm trying to connect to Sharepoint on premise. I read that in that case we should use Windows authentication and I confirmed this connection method does work by susccessfully connecting to a sharepoint folder.

Besides the issue of not being able to connect to lists (today I'm getting  "SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)")what annoys me the most is not being presented with the connection options again, past the first failed attemps. So I am not able to try anything different. It is as if the connection parameters were cached and I don't know how to clear that cache

[fiveone]

I think I found where this connection setting is stored:

Options and settings > Data source settings > Global permissions

Now I can explore (I think...) different connection options.

Including the suggested Windows Authentication method.

But it all ends up with a 404 error when I try to connect to the sharepoint list...