Skip to content

Power Query M Functions for the Socrata Open Data API

License

Notifications You must be signed in to change notification settings

tonmcg/powersocrata

Repository files navigation

PowerSocrata

PowerSocrata enables users to connect to and access data from the Socrata open data portals, including the Socrata Open Data API (SoDA). With PowerSocrata, users can programmatically access a wealth of open data resources from governments, non-profits, and NGOs around the world.

PowerSocrata is a series of M language functions that provide a way for Power BI Desktop and Excel 2016 users to easily access, analyze, and visualize these open data resources. PowerSocrata aims to provide the same SoDA functionality found in some of the more popular programming languages[1][2][3]. The M language is used by the Power Query user experience found in Power BI Desktop and Excel 2016.

Alt Text

Table of Contents

Installing

  1. Create a new Blank Query

Excel Users: Data > Get & Transform Data > Get Data > From Other Sources > Blank Query Alt Text

Power BI Users: Home > External Data > Get Data > Blank Query Alt Text

  1. Open the Advanced Query Editor dialog and paste the following code in its entirety:
let
    Source = 
        Expression.Evaluate(
            Text.FromBinary(
                Web.Contents(
                    "https://raw.githubusercontent.com/tonmcg/powersocrata/master/M/Socrata.ReadData.pq"
                )
            ),
            #shared
        )
in
    Source

Alt Text

  1. Click Done.

  2. Power Query will ask you to specify how to connect to this file. Click Edit Credentials.

  3. In the Access Web Content dialog, choose to connect anonymously. Click Connect. Alt Text

  4. Rename this query to ReadSocrata.

Features

Query Datasets

With default parameters

This code snippet returns the first 1,000 records from the San Francisco Police Department Calls for Service dataset:

let
    data = ReadSocrata("https://data.sfgov.org/resource/fjjd-jecq.json", null, null)
in
    data

With filter parameters

We can also ask the Socrata API to return a subset, summary, or specific sorting of the data by utilizing its distinctive query language, Socrata Query Language or SoQL. SoQL clauses are parameters that define the criteria by which the dataset provider will filter, summarize, or sort our desired result.

For example, the following query returns the first 100K calls since 2016 categorized as "Homeless Complaint" from the same dataset:

let
    data = ReadSocrata("https://data.sfgov.org/resource/fjjd-jecq.json?$where=original_crimetype_name='Homeless+Complaint'+AND+call_dttm>'2016-01-01T00:00:00.000'", <APP TOKEN>, 100000)
in
    data

In the example above, we supplied a SoQL $where clause within the first parameter of the ReadSocrata function, which asked the dataset provider to filter both the original_crimetype_name and call_dttm columns to our defined criteria. We also defined "100000" as the third parameter in the ReadSocrata function, which further limited the results to the first 100K records.

With app token parameter

By the way, did you notice the APP TOKEN parameter in the function above? Any PowerSocrata query that returns more than 1,000 records requires the use of a unique Socrata Open Data API application token (app token). For more information on obtaining an app token, consult the Application Tokens page on the Socrata API Developer site.

How do we use the app token? We supply it to our query in one of two ways:

  1. As the second parameter in the ReadSocrata function like we did above
  2. As a $$app_token parameter within your request URL string, as shown below

In this example, we supply our app token within the SoQL $$app_token clause in the URL string. This should return the same dataset as above:

let
    data = ReadSocrata("https://data.sfgov.org/resource/fjjd-jecq.json?$where=original_crimetype_name='Homeless+Complaint'+AND+call_dttm>'2016-01-01T00:00:00.000'&$$app_token=<APP TOKEN>", null, 1000000)
in
    data

Power BI Report Examples

Power BI Sample Template

Resources

1 See the R client here.
2 See the Python client here.
3 See the Ruby client here.

About

Power Query M Functions for the Socrata Open Data API

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages