Authentication Error for non-browser sign in Power Query via Web

Question

Hi all,

I'm trying to connect to an Excel workbook within my SharePoint Online library, which I can access via my normal credentials and can sign on to my account via non-browser when I go to File -> Account.

However, the system asks for my credentials again when I am in Excel and click on Data -> Get Data -> From Web and put in the URL address of the file and click on "Organisational Account".

It then freezes the authentication window without the ability to reenter the details.

Above is the initial sign-in page. Note that the account is already signed in the top right corner in the same tenancy. However, this screen suggests that I'm not signed in and when I click on Sign In to do so, I get the below screen.

The above is the hung up authentication screen when I click on "Sign In"

I have:

1. Restarted the app.
2. Checked credentials in that they can log in to the environment.
3. That I have file permission.
4. Done a repair install.
5. Removed all Microsoft Office Authentication settings within the Registry.
6. Tried to log in through my phone's hotspot in case it was a firewall issue (it's not).

I still have the same issue.

Answer 1

Hi Matty,

Thank you for posting your question in this community. From your description, it seems that you are trying to import an Excel workbook in SharePoint Library into an Excel workbook by Power Query.

I noticed that you are using Get data from Web to input the Excel file link into Excel workbook. Generally, Get Data from other Sources from Web is to extra the data from a website, such as a table in web page.

If you would like to import an Excel workbook in SharePoint document library, we recommend you can use OneDrive to sync SharePoint document library to your computer. And using Get Data, From Workbook and choose the workbook from the OneDrive sync folder.

For your information:

Import data from external data sources (Power Query)

Best regards,

Dihao

Answer 2

Thanks for taking the time to reply Dihao.

https://community.powerbi.com/t5/Desktop/Import-Data-from-an-Excel-sheet-in-SharePoint/td-p/47223

I have successfully run this method of extracting information from online Excel files using "Get ->From Web" and outting in a SharePoint URL to the file in the past.

The issue is that the Microsoft Authentication screen that is asking for my credentials freezes, does not load any details and can only be explained by force quitting the window.

Answer 3

Thanks for creating this thread, Matty.

I am experiencing the same issue with the authentication window freezing, and can add a little more background/troubleshooting info as well:

1. This appears to occur with any attempt to authenticate "Microsoft account" credentials for SharePoint through Power Query Editor in Excel. The two data source paths I have tried are:
   1. File->SharePoint Folder
   2. Online Services->SharePoint Online List
2. This issue occurs while using Power Query Editor in Excel, but not while using the the Power Query Editor in Power BI. Using the latter, the authentication popup window successfully directs to a login screen.

Please let me know if I can provide more information.

Answer 4

Hi Matty and Robert,

Thank you for your reply and updates. After I further test this scenario from my side, it seems that I have no issue for signing in page, not matter I was connecting to a SharePoint list or SharePoint folder. I can get the verification pop up as expected with my Organization account.

 

If the other Get data methods, such as SharePoint Folder, SharePoint Online list have the same issue the problem should be caused by the Excel application.

I suggest you can try out the following methods and see the results.

1: EnterExcel Safe mode and try to get data with above methods to isolate the problem is caused by any Office add-ins.

2: Use Office repair toolto online repair Office application.

3: Try to perform Windows clean boot to isolate the problem is related to other programs in your computer.

Best regards,

Dihao

Answer 5

As with Matty's experience, the Office repair tool did not address the issue for me.

However, running Excel in Safe mode allowed for the authentication popup window to function properly.

Is there a good way for me to track down the add-in that is potentially causing the authentication to freeze when not in Safe mode?