Data flow / Power Query online - Excel file connections failing with correct credentials

[gerradferrier]

Hi,

I am using power query online to gather data from multiple worksheets in sharepoint, then combine them. This works fine in Excel, and I've copied the M-code from excel advanced editor to a blank query in PQ online.

The files are all in the same library and parent folder, some connect and others don't.

I use Organisational account for authentication, and it uses a web connection to get the file from SharePoint.

I would delete the connection and do a new one for the ones that don't work but there is no way to delete connections once they are in there.

Does anyone know how to delete connections that fail, or if there are any other known issues.

The wierd bit is some work and some dont.

Thanks,

Gerrad

[v-mengzhu-msft]

Hi @gerradferrier ,

You can't delete a failed connection query here? Just right mouse click on the problematic query.

Am I wrong in my understanding? Please let me know in time if  misunderstood.

Best regards,

Community Support Team Selina zhu

[gerradferrier]

Hi @v-mengzhu-msft 

Thanks for your response.

Deleting the query is not the problem, the issue is the connection the query uses to get the data from SharePoint is not working for some queries and not for others.

All my connections to sharepoint use Authentication kind: Organisational account

The details above are correct and it should create a connection using Organisational account (My user account), but it appears to save the connection with (none) I'm assuming this means no Authentication kind. Is that correct?

If I Create a new connection it just makes another with (none).

I've tried:

1. Creating new connections

2. Signing out and in

3. Deleted the query and started fresh from Get data - Excel workbook but the old connection persists.

4. Started fresh dataflow and added new queries using Get data - Excel workbook the same issue occurs for some files and not others.

My questions was how can I delete the (incorrect) connections and start fresh?

The strangest thing is, some files work and others don't. They are all in the same SharePoint library and head folder, but then have individual sub-folders.

There does not seem to be any pattern to which ones work and which ones fail, I thought that it may be because of special characters in the file path (& or %26 as it is changed to), but this isn't case as some fail with and without. All paths have space (%20), but only some have & (%26).

In desktop verisons of Power Query there is never any issue, and I have all these queries working in both Excel and Power Bi, if I use the same M-code I still get the same issue for some and not for others. The connection desktop creates is with the library, not the individual folder, but I have no choice of how the connection is made, or options to edit or delete.

Thanks for your help.

Gerrad