cancel
Showing results for 
Search instead for 
Did you mean: 
stevedep

Power M code to read Netatmo API data

Hi,

 

I am writing this to share my code to retrieve Netatmo Weather station data. Below you will find the code for the different queries as seen below:

stevedep_0-1613894751349.png

The code to get your token (pls note you will need to replace everything that has been pre-fixed with 'your_').

 

 

 

 

 

 

let
    token_url = "https://api.netatmo.com/oauth2/token",    
    api_base_url = "apiurl.com",
    qry_str = "?myparameter",

   body="grant_type=password&client_id=your_clientid&client_secret=your_client_secret&username=your_email&password=your_password",
   token  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(body)
   ]
   )),
   //token[access_token],
   
   tokenstring = "access_token="& Text.From( token[access_token])
in
    tokenstring

 

 

 

 

 

 

Create an app on the API website of Netatmo to obtain your login credentials:

stevedep_1-1613894933350.png

The code to get Measurements for your stations (it's currently looking back 5 days, see code 'date_begin'):

 

 

 

 

 

 

(devid as text) =>
let 
   date_end = Duration.TotalSeconds( DateTime.LocalNow() -#datetime(1970, 1, 1, 0, 0, 0)),
   date_begin = date_end - (60*60*24*5),

   requeststring = Token & "&device_id="&devid&"&scale=30min&type=co2&optimize=false&real_time=true&date_begin="&Text.From(Number.Round(date_begin))&"&date_end="&Text.From(Number.Round(date_end)),
   getstations = Json.Document(Web.Contents("https://api.netatmo.net/api/getmeasure",
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(requeststring)
   ]
   )),
    body1 = getstations[body],
    #"Converted to Table" = Record.ToTable(body1),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values",{{"Name", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Name]
)),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"DateTime", Order.Descending}}),
    
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Value", "CO2"}})
in
    #"Renamed Columns"

 

 

 

 

 

 

Code to get the measurements for your modules (also looking back 5 days, pls see date_begin):

 

 

 

 

 

 

(devid as text, modid as text) =>
let 
   date_end = Duration.TotalSeconds( DateTime.LocalNow() -#datetime(1970, 1, 1, 0, 0, 0)),
   date_begin = date_end - (60*60*24*5),

   requeststring = Token & "&module_id="&modid&"&device_id="&devid&"&scale=30min&type=co2&optimize=false&real_time=true&date_begin="&Text.From(Number.Round(date_begin))&"&date_end="&Text.From(Number.Round(date_end)),
   getstations = Json.Document(Web.Contents("https://api.netatmo.net/api/getmeasure",
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(requeststring)
   ]
   )),
    body1 = getstations[body],
    #"Converted to Table" = Record.ToTable(body1),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values",{{"Name", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Name]
)),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"DateTime", Order.Descending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Value", "CO2"}})
in
    #"Renamed Columns"

 

 

 

 

 

 

Code that brings it together (you will need to change the filter value, currently 'your_modulename'):

 

 

 

 

 

 

let
   
   getstations = Json.Document(Web.Contents("https://api.netatmo.net/api/getstationsdata",
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(Token)
   ]
   )),
    body1 = getstations[body],
    devices = body1[devices],
    #"Converted to Table" = Table.FromList(devices, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "station_name", "date_setup", "last_setup", "type", "last_status_store", "module_name", "firmware", "last_upgrade", "wifi_status", "reachable", "co2_calibrating", "data_type", "place", "home_id", "home_name", "dashboard_data", "modules"}, {"_id", "station_name", "date_setup", "last_setup", "type", "last_status_store", "module_name", "firmware", "last_upgrade", "wifi_status", "reachable", "co2_calibrating", "data_type", "place", "home_id", "home_name", "dashboard_data", "modules"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"_id", "station_name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "CO2_Measurements", each fnGetMeasurements([_id])),
    #"Expanded CO2_Measurements" = Table.ExpandTableColumn(#"Added Custom", "CO2_Measurements", {"CO2", "DateTime", "Name"}, {"CO2", "DateTime", "Name"}),
    FinalStations = Table.RemoveColumns(#"Expanded CO2_Measurements",{"_id"}),
    mods = #"Expanded Column1",
    #"Expanded modules" = Table.ExpandListColumn(mods, "modules"),
    #"Expanded modules1" = Table.ExpandRecordColumn(#"Expanded modules", "modules", {"_id", "module_name"}, {"_id.1", "module_name.1"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded modules1",{"_id", "_id.1", "module_name.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each ([module_name.1] = "your_modulename")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "CO2_Measurements", each fnGetMeasurementsModule([_id], [_id.1])),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"_id", "_modId"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"_modId", "_id.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"module_name.1", "station_name"}}),
    FinalModules = Table.ExpandTableColumn(#"Renamed Columns", "CO2_Measurements", {"CO2", "DateTime", "Name"}, {"CO2", "DateTime", "Name"}),
    FinalResult = Table.Combine({FinalModules,FinalStations})
in
    FinalResult

 

 

 

 

 

 

 

Please send me a message if you are interested in the dashboard that contains DAX like moving averages and day to day comparisons of the CO2 values. 

 

Enjoy!

Kind regards, Steve. 

 

 

Polls
What is your favorite Power BI Feature release this month?