Weather data

[obertimanuel]

• Hello everyone,
  I would like to create a chart displaying weather data extrapolated from api https://api.met.no/weatherapi/locationf ... =60&lon=11
  And so far so good , as long as it is fixed lat and lon no problem.

I have a query with 3 columns: city name, lat and lon

I would like that every time I select a city from the city-based data filter, the weather data in the graph automatically changes, taking the correct lat and lon values of the selected city

I get the weather values from :

let
// Definisci i parametri di latitudine e longitudine
Latitude = <NomeParametroLatitudine>,
Longitude = <NomeParametroLongitudine>,

// Crea l'URL dell'API con i parametri
url = "https://api.met.no/weatherapi/locationforecast/2.0/compact?lat=" & Text.From(Latitude) & "&lon=" & Text.From(Longitude),

// Recupera i dati dall'API
Source = Json.Document(Web.Contents(url)),

// Naviga fino ai dati necessari
timeseries = Source[properties][timeseries],

// Trasforma i dati in una tabella
#"Converted to Table" = Table.FromList(timeseries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"time", "data"}, {"time", "data"}),
#"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"instant", "next_1_hours"}, {"instant", "next_1_hours"}),
#"Expanded instant" = Table.ExpandRecordColumn(#"Expanded data", "instant", {"details"}, {"details"}),
#"Expanded details" = Table.ExpandRecordColumn(#"Expanded instant", "details", {"air_temperature", "wind_speed"}, {"air_temperature", "wind_speed"}),

// Gestisci i dati di precipitazione
#"Added Custom" = Table.AddColumn(#"Expanded details", "precipitation", each try Record.FieldOrDefault([next_1_hours][details], "precipitation_amount") otherwise 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"next_1_hours"}),

// Rinomina le colonne
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{
{"time", "Time"},
{"air_temperature", "Temperature"},
{"wind_speed", "WindSpeed"},
{"precipitation", "Precipitation"}
})
in
#"Renamed Columns"

I would like to take latitude and longitude from another table with the city selected and refrash the chart

Thank you

[tharunkumarRTK]

@obertimanuel 
I would suggest you to first convert your m-query into m-function. And give is a name as 'fxWeather'

(Lat as text, Lon as text)=>
let
// Definisci i parametri di latitudine e longitudine
Latitude = Lat,
Longitude = Lon,
// Crea l'URL dell'API con i parametri
url = "https://api.met.no/weatherapi/locationforecast/2.0/compact?lat=" & Text.From(Latitude) & "&lon=" & Text.From(Longitude),

// Recupera i dati dall'API
Source = Json.Document(Web.Contents(url)),

// Naviga fino ai dati necessari
timeseries = Source[properties][timeseries],
// Trasforma i dati in una tabella
#"Converted to Table" = Table.FromList(timeseries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"time", "data"}, {"time", "data"}),
#"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"instant", "next_1_hours"}, {"instant", "next_1_hours"}),
#"Expanded instant" = Table.ExpandRecordColumn(#"Expanded data", "instant", {"details"}, {"details"}),
#"Expanded details" = Table.ExpandRecordColumn(#"Expanded instant", "details", {"air_temperature", "wind_speed"}, {"air_temperature", "wind_speed"}),
// Gestisci i dati di precipitazione
#"Added Custom" = Table.AddColumn(#"Expanded details", "precipitation", each try Record.FieldOrDefault([next_1_hours][details], "precipitation_amount") otherwise 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"next_1_hours"}),
// Rinomina le colonne
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{
{"time", "Time"},
{"air_temperature", "Temperature"},
{"wind_speed", "WindSpeed"},
{"precipitation", "Precipitation"}
})
in
#"Renamed Columns"

and in the other table where you have city name and latitude and longitude columns, add a custom column with the below formula 

fxWeather([Lat],[Lon])

 Then the exapand the new column. Then you will be able to filter your weahter info as per the selected city.

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[obertimanuel]

Thanks I tried this solution too...... but I have about 150000 cities and its not possibile

[danextian]

Hi @obertimanuel ,

What makes 150K lat and long combinations not possible? I'm thinking it is  either due to API limitation or you are trying  to extract too big of a data that your device is running out of memory.  That aside, you can modify your query so you don't need to use an external custom function and avoid the dynamic data source warning. Here's a sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcnBDQAgCASwXXgTc3ogMItx/zU02m/XEq9WIIdTVDpaRIeBsvVd1uT0/Je0KN7bBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lat Column" = _t, #"Lon Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lat Column", type number}, {"Lon Column", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Weather Data", each let
    // Definisci i parametri di latitudine e longitudine
    Latitude =  [Lat Column],
    Longitude = [Lon Column],
    RelPath = "/locationforecast/2.0/compact?lat=" & Text.From(Latitude) & "&lon=" & Text.From(Longitude),

    // Recupera i dati dall'API
    Source = Web.Contents("https://api.met.no/weatherapi/", [RelativePath = RelPath]),
    Json = Json.Document(Source),

    // Naviga fino ai dati necessari
    timeseries = Json[properties][timeseries],

    // Trasforma i dati in una tabella
    #"Converted to Table" = Table.FromList(timeseries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"time", "data"}, {"time", "data"}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"instant", "next_1_hours"}, {"instant", "next_1_hours"}),
    #"Expanded instant" = Table.ExpandRecordColumn(#"Expanded data", "instant", {"details"}, {"details"}),
    #"Expanded details" = Table.ExpandRecordColumn(#"Expanded instant", "details", {"air_temperature", "wind_speed"}, {"air_temperature", "wind_speed"}),

    // Gestisci i dati di precipitazione
    #"Added Custom" = Table.AddColumn(#"Expanded details", "precipitation", each try Record.FieldOrDefault([next_1_hours][details], "precipitation_amount") otherwise 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"next_1_hours"}),

    // Rinomina le colonne
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{
        {"time", "Time"},
        {"air_temperature", "Temperature"},
        {"wind_speed", "WindSpeed"},
        {"precipitation", "Precipitation"}
    })
in
    #"Renamed Columns"
, type table)
in
    #"Added Custom"

You can see in the sreenshot above that by utilizing the parameters in Web.Contents, you are able to avoid the dynamic data source warning.

Remove the timezone from the data before converting it to date or datetime as not doing so will shift the time/date back by 2 hours (Norway is + 2 right) if the model is refreshed in the service.The service uses UTC.

[obertimanuel]

Thanks,

How, after modifying the query, could I select a city front data filter , and automatically have the corresponding weather data with corresponding lat and lon?

[danextian]

If the data for that city is loaded into the model then you can.

[obertimanuel]

Thanks,
Can you explain how please ?

I have only 2 lat e 2 lon........

I'm doing something wrong and I don't understand where

Thanks

[danextian · Accepted solution]

My query is just a sample as I don't have all your  lat and long combinations. Create a custom column and paste that formula in the custom column step from the code I gave you which is a modified version of your code. Reference these two to the actual column names:

   Latitude =  [Lat Column],
   Longitude = [Lon Column],

[tharunkumarRTK]

@obertimanuel 

Okay, Since you data source is an API, you cannot convert the storage mode to direct query mode and you cannot leverage dynamic m query parameters.

There is one option which I think might work in your case, that is you can use paginated reports and leverage Power query 'get data' experience. This is a new option released by Microsoft recently. You can find more information here: https://www.youtube.com/watch?v=OQKgnJkjJDI

And, if you want you can also embed this paginated report in a power bi dashboard.

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun