Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

gerradferrier

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

Submitted by on ‎10-19-2022 06:42 AM

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

Status: Investigating
See more ideas labeled with:
2 Comments (2 New)
Comments
Anonymous
Not applicable
‎10-19-2022 07:45 PM

Hi @gerradferrier ,

 

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

vmengzhumsft_0-1666233816712.png

 

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

 

Best regards,

Community Support Team Selina zhu

 

gerradferrier
New Member
‎10-20-2022 01:24 AM

Hi @Anonymous 

 

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

gerradferrier_0-1666252501098.png

 

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?

gerradferrier_1-1666252810558.png

 

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

Idea Statuses