Excel Power Query Integration - bcgov/common-hosted-form-service GitHub Wiki

This documentation is no longer being updated. For the most up to date information please visit our techdocs

Home > CHEFS Capabilities > Integrations > Excel Power Query Integration

Excel Integration

Microsoft Excel is one of many ways to work with form submission data.

Excel Power Query

By getting data from the Web and creating a new Power Query you can work with the submission data.

CHEFS Form Information

You will need this information about your form:

  • The formId. One way to retrieve this is to navigate to your form The URL in the browser window will contain .../form?f={formId}
  • Your form's API Key

Query Configuration: Submissions

  • In the ribbon at the top of Excel in the Data menu, select: Get Data > From Other Sources > From Web

excelPowerQuery1

  • Enter the following URL replacing the {formId}:
    https://submit.digital.gov.bc.ca/app/api/v1/forms/{formId}/export?format=json

    • Choose Basic for the authorization type,
    • For "User name" use your formId and "Password" set to your API Key
    • In “Select which level to apply these settings to”, choose a URL with the formId included in it so that the password only applies to this form.

  • Double-click the file to open it
    excelPowerQuery2

  • Edit Settings
    excelPowerQuery3

  • Choose Json and OK
    excelPowerQuery4

  • You should now see submission Records
    excelPowerQuery5

  • Convert to a Table
    excelPowerQuery6
    excelPowerQuery7

  • Now we need to Expand the submission Records. Click the Column Select button:
    excelPowerQuery8

  • Select the columns you’d like to keep and OK
    excelPowerQuery9

  • Scan the columns. Some nested data may need to be expanded further. Follow the same process to expand the data
    excelPowerQuery10
    excelPowerQuery11

  • Expanded data will be listed
    excelPowerQuery12

  • Transform and/or Combine data (optional, e.g. parse dates, format text, filter form version, etc)

  • Close and Load the data excelPowerQuery13

  • Edit the query if needed
    excelPowerQuery14

  • Refresh data on demand, or review Connection Properties to set a refresh schedule
    excelPowerQuery15