Using with Power BI

Power BI can pull data directly from the DataForNerds API using the built-in Web connector and Power Query.

Connect to a dataset

1. Open Power BI Desktop and click Get Data > Web
2. Select Basic and enter a dataset URL, for example:

   https://api.datafornerds.io/v2/microsoft/windows-update-history.json

3. Click OK. Power BI will connect and open the Power Query Editor.

Navigate the JSON envelope

The API returns a JSON envelope with metadata and data fields. You only need the data array.

1. In the Power Query Editor you’ll see a Record with two fields. Click data (not the cell, click the word data itself) to drill into it.
2. Power BI shows the array as a List. Click To Table in the ribbon, then OK to accept defaults.
3. Click the expand icon on the Column1 header to expand all fields.
4. Uncheck Use original column name as prefix and click OK.

You should now have a flat table with one row per record.

Set column types

Power BI imports everything as text by default. You’ll want to fix the key columns:

• ReleaseDate: Date
• MajorVersion: Whole Number
• IsExpired: True/False
• RecordCount (if you kept metadata): Whole Number

Select the column, then use Transform > Data Type in the ribbon, or right-click the column header and choose Change Type.

Advanced: Power Query M code

If you’d rather skip the point-and-click steps, paste this into a blank query (Home > Advanced Editor):

let
    Source = Json.Document(
        Web.Contents("https://api.datafornerds.io/v2/microsoft/windows-update-history.json")
    ),
    Data = Source[data],
    AsTable = Table.FromRecords(Data),
    TypedColumns = Table.TransformColumnTypes(AsTable, {
        {"ReleaseDate", type date},
        {"MajorVersion", Int64.Type},
        {"IsExpired", type logical}
    })
in
    TypedColumns

Swap out the URL with any dataset endpoint from the API Reference.

Using a different dataset

The same steps work for every DataForNerds endpoint. The column names and types will differ depending on the dataset. Check the API Reference for the full list of available endpoints and their schemas.

Scheduled refresh

If you publish to the Power BI Service, you can set up a scheduled refresh to keep the data current:

1. Publish the report to your Power BI workspace.
2. Go to Settings > Datasets for the published dataset.
3. Under Scheduled refresh, turn it on and pick a daily cadence.

No credentials are needed since the DataForNerds API is public with no authentication.