Using a blank 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 each 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.

🤓
Power Query uses the M formula language to construct data operations

📘
Check the documentation for further instructions regarding your API key

Projects endpoint

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.

Project Network Details endpoint

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’.

Turbines endpoint

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.

Network Infrastructure endpoint

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.

Using PowerQuery

🤓
Power Query uses the M formula language to construct data operations

📘
Check the documentation for further instructions regarding your API key

Projects endpoint

Project Network Details endpoint

Turbines endpoint

Network Infrastructure endpoint

🤓Power Query uses the M formula language to construct data operations

📘Check the documentation for further instructions regarding your API key

Projects endpoint

Project Network Details endpoint

Turbines endpoint

Network Infrastructure endpoint

🤓
Power Query uses the M formula language to construct data operations

📘
Check the documentation for further instructions regarding your API key