关于通过Power Query将Infogram表格导入Excel并配置自动更新连接失败的技术咨询

Hey there, let's work through this Power Query issue you're having with importing that Infogram energy hedging table. I've dealt with similar dynamic web table problems before, so here are some practical steps to get this sorted:

• Account for dynamic JavaScript rendering
  Infogram uses JavaScript to load tables after the initial page loads, which Power Query's basic "From Web" tool might not catch. Try the advanced web request option:

  1. Navigate to Data > Get Data > From Web > Advanced.
  2. Paste your Infogram URL in the "URL parts" field.
  3. Add a custom header: set User-Agent as the key, and use a browser-like value (e.g., Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36). This mimics a regular browser request, which often helps Power Query detect the table.
  4. Load the data and check the navigator pane for the table—it should show up now if rendering was the issue.

• Pull data directly from Infogram's API
  Instead of scraping the web page, Infogram serves its data via a JSON API, which is way more reliable for Power Query. Here's how to find that API endpoint:

  1. Open the Infogram page in Chrome or Firefox, right-click anywhere on the page > Inspect > switch to the Network tab.
  2. Refresh the page, then look for a request with a .json extension (usually named something like data.json or similar).
  3. Copy the full URL of that JSON request.
  4. Back in Excel, use Data > Get Data > From Web, paste the JSON URL, then expand the JSON structure to extract your table data. This method often bypasses rendering issues entirely.

• Try Excel's built-in table selection tool
  Sometimes Power Query's default preview misses tables, but Excel's web table extractor can help:

  1. Go to Data > From Web, paste your Infogram URL.
  2. When the web preview loads, click the small arrow icon next to any potential table elements (even if they look empty). If the table is there, it will populate in the preview.
  3. If no tables show up, use the "Select Table" button in the preview toolbar to manually highlight the table area on the web page—Excel will then extract that selected region as a table.

• Configure auto-update correctly once imported
  Once you get the table into Excel, double-check your auto-update settings to make sure it refreshes automatically:

  1. Go to Data > Queries & Connections.
  2. Right-click your imported query > select Properties.
  3. Under the "Refresh control" section, check "Refresh data when opening the file" and set a refresh interval (e.g., every 6 hours) if needed.
  4. Enable "Background refresh" so Excel doesn't freeze during updates.

• Check for access restrictions
  Make sure the Infogram table is publicly accessible. If it's a private table, you might need to add authentication headers (like a session cookie) to your Power Query web request. You can get these cookies from your browser's developer tools (under the Application tab in Chrome) and add them as custom headers in the advanced web request settings.

内容的提问来源于stack exchange，提问作者Mark