'%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 Fix Power Query Returning HTML Instead of Structured Web Data
Question details
The user wants to extract structured data from a website, but Power Query fails to parse it properly, returning an HTML table in the Children property and a null value in the Text property.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing data from a web URL using Power Query.
- Observed behavior
- Power Query does not recognize the web page data as a standard table, resulting in raw HTML being returned in the Children property while the expected text returns null.
Before troubleshooting, open the target URL in a standard web browser to verify if the data is rendered dynamically via JavaScript, as the standard Power Query Web connector only parses static HTML content.
Use the Html.Table Function in Power Query
Use the advanced Html.Table M function to manually parse the raw HTML string by targeting specific CSS selectors.
When the standard web connector fails to recognize tables, the underlying data often resides inside complex div structures or non-standard HTML elements. By using the Html.Table function, you can explicitly define which HTML tags or classes contain your data.
Navigate to the query that returns the raw HTML. Select 'Add Column' from the top ribbon and click on 'Custom Column'.
In the Custom Column dialog, enter the formula: Html.Table([ColumnName], {{"ExtractedData", "YOUR_CSS_SELECTOR"}}). Replace [ColumnName] with the column containing the HTML, and YOUR_CSS_SELECTOR with the actual HTML tag or class you need to extract.
Click the expand icon on the header of your newly created custom column to view the structured data that has been parsed from the HTML.
Remove the original column containing the raw HTML to clean up your dataset, then click 'Close & Load' to bring the structured data into Excel.
Connect to an Official API or JSON Endpoint
If the website relies heavily on dynamic content, extracting data via a direct JSON API endpoint is more reliable than parsing HTML.
Extract Data Using Power BI Desktop
Power BI's web connector frequently handles dynamic or complex web tables better than Excel's Power Query.
Try WPS Office for Your Data Processing Needs
If you find Microsoft Excel's Power Query web extraction overly complex or difficult to troubleshoot, consider trying WPS Office. It provides a lightweight, highly compatible alternative with intuitive tools for everyday data analysis, filtering, and formatting without the steep learning curve.
- 1. Download and install: Download WPS Office for free from the official website and complete the installation process.
- 2. Open your dataset: Launch WPS Spreadsheet and easily open your existing Excel files or CSV data exports.
- 3. Process your data: Navigate to the Data tab to utilize intuitive tools for sorting, filtering, and structuring your information without needing complex code.
Frequently Asked Questions
Why does Power Query return null text when importing from the web?
This typically occurs when the target webpage loads its data dynamically using JavaScript. Power Query's basic web connector only downloads the initial static HTML, meaning dynamically rendered text elements will appear as null.
What is the difference between getting web data in Excel vs. Power BI?
While both applications use Power Query, Power BI Desktop often receives feature updates to its web connector faster than Excel. This includes better background support for inferring complex web tables and a more robust 'Extract table using examples' feature.
How do I find the correct CSS selector for the Html.Table function?
Open the webpage in your browser, right-click the specific data element, and select 'Inspect'. This opens Developer Tools where you can view the HTML tags (like <table> or <div>) and their class names, which you then format as a CSS selector string in your query.
Can Power Query extract data from a website that requires a login?
Yes, but you must configure the credentials properly. Go to Data Source Settings, select your web URL, edit the permissions, and enter your Web or Basic authentication details. Keep in mind that modern sites using complex token-based logins may require an API approach instead of standard web scraping.
