Weather data

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.

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.