PowerBI

API Access

The RenewMap API allows you to integrate up-to-date project data with data analysis platforms such as PowerBI. Guarantee the most up-to-date data in your dashboard by directly calling the API.

Power Query

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 code snippets shown in examples 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.

Projects in PowerBI

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

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.

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

Network Details in PowerBI

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. The following parameters will return all available project network details. Check the documentation for details regarding these parameters.

PowerQuery

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.

In this example, we return all available project network details into a table.

let
    Source = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/projects/network-details?limit=10000", [Headers=[accept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"id", "project_id", "duid", "project_name_aemo", "year", "connection_point_id", "transmission_node_identifier", "mlf_value", "connection_voltage"}, {"id", "project_id", "duid", "project_name_aemo", "year", "connection_point_id", "transmission_node_identifier", "mlf_value", "connection_voltage"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded data1",{{"id", type text}, {"project_id", type text}, {"duid", type text}, {"project_name_aemo", type text}, {"year", type text}, {"connection_point_id", type text}, {"transmission_node_identifier", type text}, {"mlf_value", type number}, {"connection_voltage", Int64.Type}})
in
    #"Changed Type"

Set the name of the query to Project Network Details, then select Done, and then Close & Apply.

You now have a table Project Network Details.

Network Infrastructure in PowerBI

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 return the first 1000 network infrastructure items from the RenewMap database. Check the documentation for details regarding these parameters.

PowerQuery

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 run three queries to return three tranches of 1,000 network infrastructure items from the database. All tranches are then combined into one table.

let
    // get first tranch of network items (1,000 max)
    Source1 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/network?limit=1000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // get second tranch of network items (offset = 1,000)
    Source2 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/network?limit=1000&offset=1000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // get third tranch of network items (offset = 2,000)
    Source3 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/network?limit=1000&offset=2000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // get fourth tranch of network items (offset = 3,000)
    Source4 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/network?limit=1000&offset=3000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),

    // create table from combined json records
    #"Converted to Table" = Table.FromRecords({Source1, Source2, Source3, Source4}),

    #"Expanded network" = Table.ExpandListColumn(#"Converted to Table", "network"),
    #"Expanded network1" = Table.ExpandRecordColumn(#"Expanded network", "network", {"id", "infrastructure_name", "line_id", "infrastructure_status", "description", "voltage", "construction_type", "state", "country", "network", "operator", "source_text", "geometry"}, {"id", "infrastructure_name", "line_id", "infrastructure_status", "description", "voltage", "construction_type", "state", "country", "network", "operator", "source_text", "geometry"}),
    #"Expanded network.geometry" = Table.ExpandRecordColumn(#"Expanded network1", "geometry", {"type", "coordinates"}, {"geometry.type", "geometry.coordinates"}),
    #"Expanded network.geometry.coordinates" = Table.ExpandListColumn(#"Expanded network.geometry", "geometry.coordinates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded network.geometry.coordinates",{{"id", type text}, {"infrastructure_name", type text}, {"line_id", type text}, {"infrastructure_status", type text}, {"description", type text}, {"voltage", Int64.Type}, {"construction_type", type text}, {"state", type text}, {"country", type text}, {"network", type text}, {"operator", type text}, {"source_text", type text}, {"geometry.type", type text}, {"geometry.coordinates", type any}})
in
    #"Changed Type"

Set the name of the query to Network Infrastructure, then select Done, and then Close & Apply.

You now have a table Network Infrastructure with all 3,000+ turbine records.

Turbines data in PowerBI

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 return the first 10,000 turbines in the RenewMap database. Check the documentation for details regarding these parameters.

PowerQuery

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 generated from map.renewmap.com.au.

In this example, we run three queries to return three tranches of 10,000 turbines from the database. All tranches are then combined into one table.

let
    // get first tranch of turbines (10,000 max)
    Source1 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/turbines?limit=10000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // get second tranch of turbines (offset = 10,000)
    Source2 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/turbines?limit=10000&offset=10000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // get third tranch of turbines (offset = 20,000)
    Source3 = Json.Document(Web.Contents("https://api.renewmap.com.au/api/v1/turbines?limit=10000&offset=20000", [Headers=[acccept="application/json", Authorization="Bearer <YOUR_API_KEY>"]])),
    // create table from combined json records
    #"Converted to Table" = Table.FromRecords({Source1, Source2, Source3}),
    #"Expanded turbines" = Table.ExpandListColumn(#"Converted to Table", "turbines"),
    #"Expanded turbines1" = Table.ExpandRecordColumn(#"Expanded turbines", "turbines", {"project_id", "project_name", "mw", "oem", "hub_height", "rotor_diameter", "rotor_tip_height", "model", "point"}, {"project_id", "project_name", "mw", "oem", "hub_height", "rotor_diameter", "rotor_tip_height", "model", "point"}),
    // split lat and long points
    #"Split Column" = Table.SplitColumn(#"Expanded turbines1", "point", each _, {"point.0", "point.1"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column",{{"project_id", type text}, {"project_name", type text}, {"mw", type any}, {"oem", type text}, {"hub_height", type any}, {"rotor_diameter", type any}, {"rotor_tip_height", type any}, {"model", type text}, {"point.0", type number}, {"point.1", type number}}),
    #"Extracted Values" = Table.TransformColumns( #"Changed Type" , {
        {"hub_height", each if _ = null then "" else Text.Combine(List.Transform(_, Text.From), ","), type text},
        {"rotor_diameter", each if _ = null then "" else Text.Combine(List.Transform(_, Text.From), ","), type text},
        {"rotor_tip_height", each if _ = null then "" else Text.Combine(List.Transform(_, Text.From), ","), type text},
        {"mw", each if _ = null then "" else Text.Combine(List.Transform(_, Text.From), ","), type text}
    })
in
    #"Extracted Values"

Set the name of the query to Turbines, then select Done, and then Close & Apply.

You now have a table Turbines with all 30,000+ turbine records.