A self-updating form using Power Query in Excel

I will preface this by saying that I don't believe it's an elegant method by any stretch, but it might be helpful for those who author forms in Excel and can't use the API to automatically update them. As long as your version of Excel supports Power Query with OData as a data source and your formula calculation option is automatic it will work.

Self_Updating_Form.xlsx (40.7 KB)

In this example query I have simply returned all values and added a column that populates with an emoji for the item status value. You could easily build upon this to do other things, such as,

  • Filter out rejected/has issues entries,
  • Remove all the columns that aren't needed for a smaller query table
  • Add other queries to calculate progress totals, percentages, x of y complete etc
    • Or calculate this inside the sheet and not in power query.
  • Hide values entirely by populating a value to use as a choice filter once they are in an approved state
  • Show the most recent value submitted for a question in the labels for that question's choices, as reference when it is being updated and resubmitted
  • Update question labels or hints with values/totals/avg-max-min of previously submitted values


  • Provide simple offline, on device filtering of submitted items so the default view shows those that have not been submitted. But do not remove them entirely in case a repeat or rework is needed.
  • Provide a basic and accessible way to update the form definition semi-automatically to show progress/status to the user.
  • Keep everything contained within one XLSX file. (If you use external selects you will need to build on this to write out updated CSVs)


  • For on device filtering, append the last submitted item ID to a last-saved value for the submitted item in a calculate, building up a string of submitted IDs, then apply a choice filter that checks for the ID within the string to supress it.
    • thx to @LN for this workaround tip
  • For form updating, embed a Power Query in the form that pulls the submissions for that form, then create dynamic choice labels by counting items within a group that match / searching for a particular item in the submissions table
    • The starting label is stored in the column baselabel, then the actual label is an excel formula that combines the status emoji with the starting value

To set it up:

  • Publish the form and copy the OData URL
    • image
  • Paste this URL into the form in the area indicated in the Settings worksheet
    • image
  • Go to Data on the ribbon and 'refresh all', the first time you may need to enter your Central user/password under 'basic authentication'
    • image
    • If there are no submissions yet, the table on the Submissions worksheet will be empty. :stop_sign: No further action
    • If there are submissions, the table will populate and the choice labels in the Choices worksheet will update :arrow_forward: Save the definition, upload to Central and test/publish/sync. (No need to change version number, it timestamps automatically)
    • :repeat: Repeat as frequently as needed.

To use the form
On device, before any form definition updates, after completing a submission for an item, the next time the form is opened, those item(s) will still show a :red_circle: 'not started' flag, but will be hidden unless 'show already submitted items' is selected. Note that Enketo is different to Collect here, after updating the definition (or reconfiguring the form), the last-saved value used to hide these items will reset in Collect but not in Enketo.

After a definition update, the emojis will reflect the group progress and the item status.

Enketo screenshots

Eg, after selecting (D), (D3), (D3_a) and submitting the form, option (D3_a) is now removed from the base item selections:

But submitted items can still be displayed, which will then display (D3_a):

Once some submissions have been received and the form is opened and the query run, the choice list will update and replace the :red_circle: with :yellow_circle: for those sites/subsites that are in progress, and indicate individual item progress depending on their status in Central:

  • :ballot_box_with_check: Submitted (image on Collect)
  • :white_check_mark: Approved
  • :question: Has issues
  • :x: Rejected

The screenshots below show the form after running the query and doing a definition update with the following submissions uploaded to Central:

After submitting some items (A1_a/c/e/f & C2_a) and running the definition update and publishing th new version, the :red_circle: is now :yellow_circle: for A, C, A1, C2. As mentioned above, in Collect post definition update their status emoji would change, but the submitted items would not be hidden as 'already submitted' and would all show by default.

Toggling to show the submitted items in Enketo and their status emoji :

Collect Screenshots

In Collect after a definition update, all options are visible, but with updated emoji indicators:

Then after submitting (A), (A1), (A1_d) the form hides that option at the next use of the form:

But it can be toggled back to show it: