Import RenewMap project data to a PowerBI project.
Check the API docs
You will need an API key to get started.
Check the Getting Started page for instructions.
Using the GUI
You can add RenewMap API endpoints to a PowerBI project through the user interface. In a new PowerBI project, select Get data >> Web >> Advanced
Enter the following details, replacing <YOUR_API_KEY> with the key you generated from map.renewmap.com.au. These parameters will add Australian projects in the RenewMap database to a PowerBI report, including those with no location data. Check the documentation for further information about these parameters.
PowerQuery
Power Query uses the M formula language to construct data operations
Using a query allows you to customise the data connection and table structure to a greater degree. In PowerBI, go to Get data >> Blank query, and paste the code snippet below into the script. Replace <YOUR_API_KEY> with the key that you generate from map.renewmap.com.au.
To edit an existing query, select Transform data in PowerBI, then select your query. You can use the Power Query interface to apply transformations, or open Advanced Editor to edit the query code directly.
In this example, we combine AU and NZ project data into one table (which is not possible using the GUI).
let
// get AU project data and add country code
Source_au = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/projects?limit=10000&location=all&country=AU", [Headers=[accept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
#"Convert to Table AU" = Table.FromRecords({Source_au}),
#"Add country code AU" = Table.TransformColumns(
Table.ExpandListColumn(#"Convert to Table AU", "projects"),
{{"projects", each Record.AddField(_, "country", "AU"), type record}}
),
// get NZ project data and add country code
Source_nz = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/projects?limit=10000&location=all&country=NZ", [Headers=[accept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
#"Convert to Table NZ" = Table.FromRecords({Source_nz}),
#"Add country code NZ" = Table.TransformColumns(
Table.ExpandListColumn(#"Convert to Table NZ", "projects"),
{{"projects", each Record.AddField(_, "country", "NZ"), type record}}
),
// combine AU and NZ data
#"Combined tables" = Table.Combine({#"Add country code AU", #"Add country code NZ"}),
#"Expanded projects1" = Table.ExpandRecordColumn(#"Combined tables", "projects", {"project_name", "state", "technology", "project_size_mw", "storage_size_mwh", "turbines", "developer", "project_status", "da_status", "epbc_code", "epbc_link", "epbc_status", "owner", "opennem", "council_area", "network", "rez", "id", "point", "country"}, {"project_name", "state", "technology", "project_size_mw", "storage_size_mwh", "turbines", "developer", "project_status", "da_status", "epbc_code", "epbc_link", "epbc_status", "owner", "opennem", "council_area", "network", "rez", "id", "point", "country"}),
// split lat and long data
#"Split Column" = Table.SplitColumn(#"Expanded projects1", "point", each _ , {"point.0", "point.1"} , ""),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column",{{"project_name", type text}, {"state", type text}, {"technology", type text}, {"project_size_mw", type number}, {"storage_size_mwh", type number}, {"turbines", Int64.Type}, {"developer", type text}, {"project_status", type text}, {"da_status", type text}, {"epbc_code", type text}, {"epbc_link", type text}, {"epbc_status", type text}, {"owner", type text}, {"opennem", type text}, {"council_area", type text}, {"network", type text}, {"rez", type text}, {"id", type text}, {"point.0", type number}, {"point.1", type number}}),
#"Replaced Value" = Table.ReplaceValue( #"Changed Type",null,0,Replacer.ReplaceValue,{"turbines"})
in
#"Replaced Value"
Set the name of the query to Projects, then select Done, and then Close & Apply.
You will now have a table Projects with both Australian and New Zealand Projects, and a country code field.
Adding fields with PowerQuery
The projects endpoint includes a parameter to return a larger selection of project attributes. This data is returned in json format and needs to be unpacked in a query.
let
// get AU project data
Source_au = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/projects?limit=10000&location=all&country=AU&fields=all", [Headers=[accept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
#"Convert to Table AU" = Table.FromRecords({Source_au}),
#"Add country code AU" = Table.TransformColumns(
Table.ExpandListColumn(#"Convert to Table AU", "projects"),
{{"projects", each Record.AddField(_, "country", "AU"), type record}}
),
// get NZ project data
Source_nz = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/projects?limit=10000&location=all&country=NZ&fields=all", [Headers=[accept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
#"Convert to Table NZ" = Table.FromRecords({Source_nz}),
#"Add country code NZ" = Table.TransformColumns(
Table.ExpandListColumn(#"Convert to Table NZ", "projects"),
{{"projects", each Record.AddField(_, "country", "NZ"), type record}}
),
// combine AU and NZ data
#"Combined tables" = Table.Combine({#"Add country code AU", #"Add country code NZ"}),
// Create main projects table without fields
#"Projects table" = Table.ExpandRecordColumn(#"Combined tables", "projects", {"project_name", "state", "technology", "project_size_mw", "storage_size_mwh", "turbines", "developer", "project_status", "da_status", "epbc_code", "epbc_link", "epbc_status", "owner", "opennem", "council_area", "network", "rez", "id", "point", "country"}, {"project_name", "state", "technology", "project_size_mw", "storage_size_mwh", "turbines", "developer", "project_status", "da_status", "epbc_code", "epbc_link", "epbc_status", "owner", "opennem", "council_area", "network", "rez", "id", "point", "country"}),
// Create separate table for fields processing
#"Fields table" = Table.ExpandRecordColumn(#"Combined tables", "projects", {"id", "fields"}, {"id", "fields"}),
#"Expanded fields" = Table.ExpandListColumn(#"Fields table", "fields"),
#"Expanded field records" = Table.ExpandRecordColumn(#"Expanded fields", "fields", {"field_name", "value"}, {"field_name", "value"}),
// Filter out null field names
#"Filtered null fields" = Table.SelectRows(#"Expanded field records", each [field_name] <> null),
// Pivot the fields into columns
#"Pivoted fields" = Table.Pivot(#"Filtered null fields", List.Distinct(#"Filtered null fields"[field_name]), "field_name", "value"),
// Join fields back to main table using nested join
#"Nested Join" = Table.NestedJoin(#"Projects table", {"id"}, #"Pivoted fields", {"id"}, "Fields", JoinKind.LeftOuter),
// Expand the nested fields table
#"Expanded Fields" = Table.ExpandTableColumn(#"Nested Join", "Fields", List.Select(Table.ColumnNames(#"Pivoted fields"), each _ <> "id")),
// Handle null values in point column
#"Replaced null points" = Table.ReplaceValue(#"Expanded Fields", null, {null, null}, Replacer.ReplaceValue, {"point"}),
// Split point values into longitude and latitude
#"Split Column" = Table.SplitColumn(#"Replaced null points", "point", each _ , {"longitude", "latitude"} , ""),
// Apply column types
#"Changed Type" = Table.TransformColumnTypes(#"Split Column",{{"project_name", type text}, {"state", type text}, {"technology", type text}, {"project_size_mw", type number}, {"storage_size_mwh", type number}, {"turbines", Int64.Type}, {"developer", type text}, {"project_status", type text}, {"da_status", type text}, {"epbc_code", type text}, {"epbc_link", type text}, {"epbc_status", type text}, {"owner", type text}, {"opennem", type text}, {"council_area", type text}, {"network", type text}, {"rez", type text}, {"id", type text}, {"longitude", type number}, {"latitude", type number}}),
// Projects with null turbines have 0 turbines
#"Replaced Value" = Table.ReplaceValue( #"Changed Type",null,0,Replacer.ReplaceValue,{"turbines"}),
// Put ordinal prefixes on the project statuses
#"StatusPrefixes" = [
#"Development" = "1. Development",
#"Construction" = "2. Construction",
#"Commissioning" = "3. Commissioning",
#"Operating" = "4. Operating",
#"Decommissioned" = "5. Decommissioned",
#"Abandoned" = "6. Abandoned"],
#"ApplyStatusPrefixes" = Table.TransformColumns(#"Changed Type",{{"project_status",each Record.FieldOrDefault(StatusPrefixes,_,_)}})
in
ApplyStatusPrefixes