Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.