Power M code to read Netatmo API data

[stevedep]

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:

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:

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.