Power Platform Community Forum Thread Details

Hi,

We are in the midst of an annual data collection.

The web-based survey forms that we ask partners to fill in have been expanded to include 2 new questions / fields; the data they submit lands in a database.

When I refresh the Excel file that queries the database, using Power Query via a JSON URL, the 2 new fields (and some changed field names) don't show up in Excel. However I can see them listed at the URL.

Example: https://www.activityinfo.org/resources/query/v43/form/cng5rk5lasfp9yt4

How do I force Power Query to 're-read' the headers and realize that there are new columns? No amount of 'Refresh Data' is making a difference. Removing all column references in the query makes no difference either.

The most bare bones query I use is

let

source = "ActivityInfo",

JSON = Json.Document(Web.Contents("https://www.activityinfo.org/resources/query/v43/form/cng5rk5lasfp9yt4")),

Table = Table.FromList(JSON, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Table

This results in the 'old' columns being queried.
Even when I build a new Excel from scratch (with the same URL) I get the same result - no new columns appear.

I’d rather not have to work through all my aggregation files every year to re-create the DB queries, as it also means my sheets with calculations and further aggregations via Pivot etc. have to be rebuilt = days of work, rather than being able to leverage what I did last year, just elegantly updating with a simple click on 'refresh data'.

I am on a Macbook Pro M1 2020, Sonoma 14.2.1 (23C71), using Excel 365 version 16.81.

Any thoughts on this would be greatly appreciated!

Thank you.

Categories:

Power Query