'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Connect Excel Power Query to a Website with Two-Factor Authentication (2FA)
Question details
The user wants to know if Excel Power Query can connect to web services that require two-factor authentication (2FA), and how to configure the connection.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Pulling data from a secure web service or application like Business Central using Power Query.
- Observed behavior
- Needs to authenticate securely via 2FA, import the web data into Excel, and understand how 2FA affects data refresh capabilities.
Ensure you have the exact web service URL or API endpoint ready, and keep your mobile device or authenticator app nearby to approve the two-factor authentication prompt during the initial sign-in.
Connect and Authenticate via 'From Web' in Excel
Use Excel's built-in web connection tool to access the service, choosing the appropriate authentication method (like OAuth or Web API) to trigger the 2FA prompt.
Power Query can successfully connect to web platforms protected by two-factor authentication (2FA), provided the web service supports modern authentication flows like OAuth. The connection process involves an interactive sign-in where you approve the login just as you would in a web browser.
Open your Excel workbook, navigate to the 'Data' tab on the top ribbon, click on 'Get Data', select 'From Other Sources', and then click 'From Web'.
In the dialog box that appears, paste the URL of the web service, API endpoint, or Business Central environment you wish to connect to, and click 'OK'.
A new window will prompt you for credentials. Select the authentication method required by your provider (often 'Organizational account' or 'Web API' for corporate services).
Click 'Sign In'. A browser-like popup will appear. Enter your username and password, then approve the two-factor authentication request on your phone or authenticator app.
Once authenticated, click 'Connect'. The Power Query Navigator will open, allowing you to select the specific tables you need, transform the data, and load it into your spreadsheet.
Try WPS Office for Seamless Data Management
While advanced API connections with 2FA are specific to Microsoft's Power Query ecosystem, WPS Office provides an incredibly lightweight, free, and highly compatible alternative for your daily spreadsheet and data analysis needs. Enjoy all standard data processing tools without expensive subscriptions.
- 1. Download and Install: Visit the official WPS Office website to download and install the free software suite on your computer.
- 2. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to create a new blank workbook or open an existing Excel file.
- 3. Import and Analyze Data: Use the 'Data' tab to easily import CSV files, web queries, and text files, then utilize built-in tools to filter and summarize your data.
Frequently Asked Questions
Why does my scheduled Power Query data refresh fail when using two-factor authentication?
Scheduled or unattended data refreshes fail because 2FA inherently requires interactive user input (such as entering a code or tapping an app notification). Since an automated background process cannot physically perform this step, the connection gets blocked.
Can I bypass two-factor authentication for automated Excel refreshes?
You cannot bypass standard 2FA if your organization mandates it. However, you can ask your IT administrator if they can provide an 'App Password', a Service Principal, or a dedicated API token that is authorized for unattended data extraction without triggering a 2FA prompt.
Does Power Query support Organizational account logins?
Yes. When connecting to supported Microsoft services, Business Central, or SharePoint, you can select 'Organizational account' in the authentication dialog. This natively supports modern authentication flows, including Azure Active Directory 2FA.
