Keep Open Data Up to Date in Excel (Using APIs)

An Application Programming Interface (API) is a simple way business users can use open data in Excel and keep it up to date.
Follow the steps below to link an Excel workbook to any data.wa.gov dataset's API. The example below links to the Office of Financial Management's "Washington State Cities and Counties" open dataset. Once linked, you can refresh the data at any time.

Steps to Link Excel to a Dataset's API

Step 1. In data.wa.gov, view the dataset you want to use.
The Washington State Cities and Counties dataset on data.wa.gov at https://data.wa.gov/demographics/Washington-State-Cities-and-Counties/g2kf-7usg.
Step 2. Choose API and then choose Copy.
The dataset page from step 1 with a red circle around the button for "API." The "Access this Dataset via SODA API" dialog is open with a red circle around the button for "Copy."
Step 3. In Excel, from the Data ribbon, choose From Web
Microsoft Excel's ribbon area with a red circle around "Data" to open the Data ribbon and a red circle around the "From Web" button.
Step 4. In the From Web dialog box, paste what you copied in step 2, and choose OK.
The "From Web" dialog box with a red circle around the "URL" field where an API Endpoint has been pasted, and a red circle around the "OK" button.
Step 5. In Power Query Editor, choose To Table.
The "Power Query Editor" window with a red circle around the "To Table" button, which is in the "Convert" section of the "Transform" ribbon.
Step 6. In the To Table dialog box, choose OK.
The "To Table" dialog box with a red circle around the "OK" button.
Step 7, option 1. Choose the button with two arrows.
Note: If you cannot choose this button, use Step 7, option 2 instead.
The "Power Query Editor" window with a red circle around the button with two arrows that is on the "Column1" list heading.
Step 7, option 2. Tab to select Column1, then from the Transform ribbon, choose Expand.
The "Power Query Editor" window showing an accessible alternative for step 7 with red circles around the "Transform" ribbon and the "Expand" button.
Step 8. Deselect Use original column name as prefix and choose OK.
The list of column names with all names selected, a red circle around the deselected option "Use original column name as prefix," and a red circle around the "OK" button.
Step 9. From the Home ribbon, choose Close & Load.
This closes the Power Query Editor window and loads the data to a table in Excel.
Save your workbook.
The "Power Query Editor" window with red circles around the "Home" ribbon name and the "Close & Load" button.

Tips

Learn more about using tables in Excel:
To refresh the data, from the Data ribbon, choose Refresh All.
Microsoft Excel with red circles around the name of the "Data" ribbon and around the "Refresh All" button.
To get notified about dataset updates, "bookmark" or "watch" the dataset.
This step requires an account. If you don't have one, create a Tyler Data & Insights ID.
The primer page for the Office of Financial Management (OFM)'s "Washington State Cities and Counties" dataset with a red circle around the "Bookmark" or "Watch this dataset" icon.

Questions?

Contact opendata@wa.gov with any questions.