Import Weather API JSON into Excel Using Power Query

February 17, 2026

Microsoft Excel Power Query can retrieve JSON directly from a REST API, transform the response, and load the results into a worksheet as a refreshable table.

In this tutorial, we’ll use Power Query with the Visual Crossing Timeline Weather API to:

  1. Retrieve JSON weather data.
  2. Convert daily weather records into an Excel table.
  3. Parameterize the location, dates, and units.
  4. Store the Weather API key using Power Query’s Web API credential rather than embedding it in the query.
  5. Retrieve weather for multiple locations and combine the results.

The Visual Crossing Weather API provides programmatic access to historical weather, current conditions, and forecasts.

If you want to explore weather data interactively before building your Power Query, see Visual Crossing Weather Data.

To follow the tutorial, sign up for a free Visual Crossing account and obtain your Weather API key.

For the complete API reference, see the Timeline Weather API documentation.

Why use Power Query for weather data?

Excel offers several ways to retrieve web data, but Power Query is particularly useful when the result is structured JSON.

Power Query can:

  • Call a REST API.
  • Parse JSON automatically.
  • Expand nested records and lists.
  • Convert the result into an Excel table.
  • Refresh the data later.
  • Read parameters from worksheet cells.
  • Combine results from multiple locations.
  • Use the same basic query logic in Excel and Power BI.

Microsoft’s Web connector and Web.Contents() function are designed for retrieving data such as JSON API responses.

Understand the Timeline Weather API response

A Timeline Weather API request uses this general structure:

/timeline/[location]/[date1]/[date2]

For example:

/timeline/Paris,France/2026-07-01/2026-07-07

A complete request can return JSON containing location metadata and a days array:

{
  "resolvedAddress": "Paris, France",
  "timezone": "Europe/Paris",
  "days": [
    {
      "datetime": "2026-07-01",
      "tempmax": 27.1,
      "tempmin": 17.2,
      "precip": 0.0,
      "humidity": 58.3,
      "conditions": "Partially cloudy"
    }
  ]
}

Each item in days represents one day of weather data. The current Timeline API supports JSON, flat JSON, and CSV response formats.

For this tutorial we’ll use JSON because Power Query can easily expand the nested days list into rows.

Create a Power Query in Excel

In Microsoft Excel:

  1. Open the Data tab.
  2. Select Get Data.
  3. Choose From Other Sources > Blank Query.
  4. Open Advanced Editor.

We’ll replace the generated query with our Power Query M code.

Store the API key as a Power Query Web API credential

A common older approach is to put the API key directly into the M script:

&key=YOUR_API_KEY

That works, but it means the credential is stored visibly in the query.

Power Query provides a better option for APIs that use a query-string API key.

Web.Contents() supports:

ApiKeyName

which specifies the name of the query parameter without placing its value in the M code. The actual API-key value is stored as a Web API credential.

The Visual Crossing key parameter is:

key

so we’ll use:

ApiKeyName = "key"

When Power Query asks how to authenticate to:

https://weather.visualcrossing.com

choose:

Web API

and enter your Visual Crossing API key.

Power Query will supply that credential as the key query parameter when the request is made.

Do not include:

key=

or another parameter name when entering the credential—enter only the key value.

Load daily weather for one location

The following complete Power Query retrieves daily weather for Paris from July 1 through July 7, 2026.

Paste it into the Advanced Editor:

let
    BaseUrl =
        "https://weather.visualcrossing.com",

    Location =
        "Paris, France",

    StartDate =
        "2026-07-01",

    EndDate =
        "2026-07-07",

    UnitGroup =
        "metric",

    RelativePath =
        "VisualCrossingWebServices/rest/services/timeline/" &
        Uri.EscapeDataString(Location) &
        "/" &
        StartDate &
        "/" &
        EndDate,

    RawData =
        Web.Contents(
            BaseUrl,
            [
                RelativePath = RelativePath,

                Query = [
                    unitGroup = UnitGroup,
                    include = "days",
                    elements =
                        "datetime,tempmax,tempmin,temp," &
                        "precip,precipprob,humidity," &
                        "windspeed,conditions",
                    contentType = "json"
                ],

                ApiKeyName = "key",

                Timeout = #duration(0, 0, 0, 30)
            ]
        ),

    JsonResult =
        Json.Document(RawData),

    ResolvedAddress =
        JsonResult[resolvedAddress],

    Days =
        JsonResult[days],

    DaysTable =
        Table.FromRecords(Days),

    AddLocation =
        Table.AddColumn(
            DaysTable,
            "resolvedAddress",
            each ResolvedAddress,
            type text
        ),

    ReorderColumns =
        Table.ReorderColumns(
            AddLocation,
            {
                "resolvedAddress",
                "datetime",
                "tempmax",
                "tempmin",
                "temp",
                "precip",
                "precipprob",
                "humidity",
                "windspeed",
                "conditions"
            }
        ),

    SetTypes =
        Table.TransformColumnTypes(
            ReorderColumns,
            {
                {"resolvedAddress", type text},
                {"datetime", type date},
                {"tempmax", type number},
                {"tempmin", type number},
                {"temp", type number},
                {"precip", type number},
                {"precipprob", type number},
                {"humidity", type number},
                {"windspeed", type number},
                {"conditions", type text}
            }
        )

in
    SetTypes

The result is a normal Power Query table with one row per day.

Why use RelativePath and Query?

Rather than assembling one large URL as a string, the query uses:

Web.Contents(
    BaseUrl,
    [
        RelativePath = RelativePath,
        Query = [...]
    ]
)

Microsoft documents RelativePath and Query specifically for constructing requests relative to a stable base URL. Query also handles escaping query-string values automatically.

This is cleaner than writing:

ApiQuery =
    BaseUrl &
    "?unitGroup=" &
    UnitGroup &
    "&include=days" &
    ...

and manually managing all of the ?, &, and URL encoding.

Encode the location

Locations are part of the Timeline API path.

We use:

Uri.EscapeDataString(Location)

to encode characters such as spaces and commas.

For example:

Paris, France

becomes a safely encoded path value.

Uri.EscapeDataString() is the Power Query M function for percent-encoding data according to URI rules.

Request only the fields you need

The example uses:

elements=datetime,tempmax,tempmin,temp,precip,precipprob,humidity,windspeed,conditions

This keeps the resulting Excel table focused on the weather fields we’re actually using.

You can change the list depending on your analysis.

For example:

datetime,tempmax,tempmin,conditions

for a simple daily forecast, or:

datetime,tempmax,tempmin,precip,snow,snowdepth,windspeed,windgust

for winter-weather analysis.

For all available fields, see the Timeline Weather API documentation.

Load forecast weather instead of historical weather

When dates are omitted from the Timeline URL, the API returns the available forecast.

Change the RelativePath to:

RelativePath =
    "VisualCrossingWebServices/rest/services/timeline/" &
    Uri.EscapeDataString(Location)

The rest of the Power Query can remain the same.

This is one of the advantages of the Timeline Weather API: historical and forecast data use the same basic JSON structure.

Use dynamic date periods

You can also use Timeline dynamic date values such as:

today
yesterday
last7days
last30days

For example:

RelativePath =
    "VisualCrossingWebServices/rest/services/timeline/" &
    Uri.EscapeDataString(Location) &
    "/last30days"

This is useful for Excel workbooks that should retrieve a moving period each time the query is refreshed.

Create worksheet parameters

Hard-coded values are useful while building the query, but most Excel users will want to change the location and dates directly from the worksheet.

Create cells for:

ParameterExample
LocationParis, France
StartDate2026-07-01
EndDate2026-07-07
UnitGroupmetric

Give those cells Excel defined names:

Location
StartDate
EndDate
UnitGroup

Do not create a worksheet cell for the API key if you are using the Power Query Web API credential approach.

Read named cells from Power Query

Power Query can read named workbook cells using:

Excel.CurrentWorkbook()

For example:

Location =
    Text.From(
        Excel.CurrentWorkbook()
            {[Name = "Location"]}
            [Content]
            {0}
            [Column1]
    )

We can do the same for each parameter.

Complete parameterized Power Query

The following version reads the location, start date, end date, and unit group from named cells in the workbook.

let
    BaseUrl =
        "https://weather.visualcrossing.com",

    Location =
        Text.From(
            Excel.CurrentWorkbook()
                {[Name = "Location"]}
                [Content]
                {0}
                [Column1]
        ),

    StartDateValue =
        Excel.CurrentWorkbook()
            {[Name = "StartDate"]}
            [Content]
            {0}
            [Column1],

    EndDateValue =
        Excel.CurrentWorkbook()
            {[Name = "EndDate"]}
            [Content]
            {0}
            [Column1],

    UnitGroup =
        Text.From(
            Excel.CurrentWorkbook()
                {[Name = "UnitGroup"]}
                [Content]
                {0}
                [Column1]
        ),

    StartDate =
        if StartDateValue = null
        then null
        else Date.ToText(
            Date.From(StartDateValue),
            "yyyy-MM-dd"
        ),

    EndDate =
        if EndDateValue = null
        then null
        else Date.ToText(
            Date.From(EndDateValue),
            "yyyy-MM-dd"
        ),

    DatePath =
        if StartDate = null then
            ""
        else if EndDate = null then
            "/" & StartDate
        else
            "/" & StartDate & "/" & EndDate,

    RelativePath =
        "VisualCrossingWebServices/rest/services/timeline/" &
        Uri.EscapeDataString(Location) &
        DatePath,

    RawData =
        Web.Contents(
            BaseUrl,
            [
                RelativePath = RelativePath,

                Query = [
                    unitGroup = UnitGroup,
                    include = "days",
                    elements =
                        "datetime,tempmax,tempmin,temp," &
                        "precip,precipprob,humidity," &
                        "windspeed,conditions",
                    contentType = "json"
                ],

                ApiKeyName = "key",

                Timeout = #duration(0, 0, 0, 30)
            ]
        ),

    JsonResult =
        Json.Document(RawData),

    ResolvedAddress =
        JsonResult[resolvedAddress],

    Days =
        JsonResult[days],

    DaysTable =
        Table.FromRecords(Days),

    AddLocation =
        Table.AddColumn(
            DaysTable,
            "resolvedAddress",
            each ResolvedAddress,
            type text
        ),

    SelectColumns =
        Table.SelectColumns(
            AddLocation,
            {
                "resolvedAddress",
                "datetime",
                "tempmax",
                "tempmin",
                "temp",
                "precip",
                "precipprob",
                "humidity",
                "windspeed",
                "conditions"
            },
            MissingField.UseNull
        ),

    SetTypes =
        Table.TransformColumnTypes(
            SelectColumns,
            {
                {"resolvedAddress", type text},
                {"datetime", type date},
                {"tempmax", type number},
                {"tempmin", type number},
                {"temp", type number},
                {"precip", type number},
                {"precipprob", type number},
                {"humidity", type number},
                {"windspeed", type number},
                {"conditions", type text}
            }
        )

in
    SetTypes

Now a user can change the values in the worksheet and use Excel’s Refresh All command to retrieve new weather data.

Make StartDate and EndDate optional

The parameterized query above supports empty dates.

If both dates are blank:

Location only

is sent to the Timeline API, producing a forecast request.

If only StartDate is supplied:

/location/startDate

is used.

If both are supplied:

/location/startDate/endDate

is used.

This lets one Excel query handle forecast and historical-weather requests.

Load hourly weather

Daily weather is convenient for tabular analysis, but the Timeline Weather API can also return hourly data.

Change:

include = "days"

to:

include = "days,hours"

and request hourly-compatible elements.

The JSON contains an hours list inside each day.

A simple hourly expansion looks like:

let
    BaseUrl =
        "https://weather.visualcrossing.com",

    Location =
        "Paris, France",

    RelativePath =
        "VisualCrossingWebServices/rest/services/timeline/" &
        Uri.EscapeDataString(Location) &
        "/2026-07-01/2026-07-03",

    RawData =
        Web.Contents(
            BaseUrl,
            [
                RelativePath = RelativePath,

                Query = [
                    unitGroup = "metric",
                    include = "days,hours",
                    elements =
                        "datetime,temp,feelslike," &
                        "humidity,precip,precipprob," &
                        "windspeed,conditions",
                    contentType = "json"
                ],

                ApiKeyName = "key"
            ]
        ),

    JsonResult =
        Json.Document(RawData),

    Days =
        Table.FromRecords(
            JsonResult[days]
        ),

    KeepDateAndHours =
        Table.SelectColumns(
            Days,
            {
                "datetime",
                "hours"
            }
        ),

    RenameDay =
        Table.RenameColumns(
            KeepDateAndHours,
            {
                {"datetime", "date"}
            }
        ),

    ExpandHoursAsRows =
        Table.ExpandListColumn(
            RenameDay,
            "hours"
        ),

    ExpandHourFields =
        Table.ExpandRecordColumn(
            ExpandHoursAsRows,
            "hours",
            {
                "datetime",
                "temp",
                "feelslike",
                "humidity",
                "precip",
                "precipprob",
                "windspeed",
                "conditions"
            },
            {
                "time",
                "temp",
                "feelslike",
                "humidity",
                "precip",
                "precipprob",
                "windspeed",
                "conditions"
            }
        )

in
    ExpandHourFields

The resulting table contains one row per hour.

Retrieve weather for multiple locations

Power Query can also call the Weather API for a list of locations and combine the results.

A clean way to do this is:

  1. Create a reusable function that retrieves weather for one location.
  2. Create an Excel table containing locations.
  3. Call the function for each row.
  4. Combine the returned tables.

Create the single-location function

Create a new blank query and open the Advanced Editor.

Paste:

(location as text) as table =>
let
    BaseUrl =
        "https://weather.visualcrossing.com",

    StartDateValue =
        Excel.CurrentWorkbook()
            {[Name = "StartDate"]}
            [Content]
            {0}
            [Column1],

    EndDateValue =
        Excel.CurrentWorkbook()
            {[Name = "EndDate"]}
            [Content]
            {0}
            [Column1],

    UnitGroup =
        Text.From(
            Excel.CurrentWorkbook()
                {[Name = "UnitGroup"]}
                [Content]
                {0}
                [Column1]
        ),

    StartDate =
        if StartDateValue = null
        then null
        else Date.ToText(
            Date.From(StartDateValue),
            "yyyy-MM-dd"
        ),

    EndDate =
        if EndDateValue = null
        then null
        else Date.ToText(
            Date.From(EndDateValue),
            "yyyy-MM-dd"
        ),

    DatePath =
        if StartDate = null then
            ""
        else if EndDate = null then
            "/" & StartDate
        else
            "/" & StartDate & "/" & EndDate,

    RelativePath =
        "VisualCrossingWebServices/rest/services/timeline/" &
        Uri.EscapeDataString(location) &
        DatePath,

    RawData =
        Web.Contents(
            BaseUrl,
            [
                RelativePath = RelativePath,

                Query = [
                    unitGroup = UnitGroup,
                    include = "days",
                    elements =
                        "datetime,tempmax,tempmin,temp," &
                        "precip,precipprob,humidity," &
                        "windspeed,conditions",
                    contentType = "json"
                ],

                ApiKeyName = "key",

                Timeout = #duration(0, 0, 0, 30)
            ]
        ),

    JsonResult =
        Json.Document(RawData),

    ResolvedAddress =
        JsonResult[resolvedAddress],

    DaysTable =
        Table.FromRecords(
            JsonResult[days]
        ),

    AddRequestedLocation =
        Table.AddColumn(
            DaysTable,
            "requestedLocation",
            each location,
            type text
        ),

    AddResolvedAddress =
        Table.AddColumn(
            AddRequestedLocation,
            "resolvedAddress",
            each ResolvedAddress,
            type text
        ),

    SelectColumns =
        Table.SelectColumns(
            AddResolvedAddress,
            {
                "requestedLocation",
                "resolvedAddress",
                "datetime",
                "tempmax",
                "tempmin",
                "temp",
                "precip",
                "precipprob",
                "humidity",
                "windspeed",
                "conditions"
            },
            MissingField.UseNull
        )

in
    SelectColumns

Name this query:

WeatherForLocation

Power Query now treats it as a function that accepts a location and returns a weather table.

Create a locations table in Excel

In your worksheet, create a table like:

Location
Paris, France
London, UK
Hamburg, Germany
New York, NY

Convert it to an Excel Table using Ctrl+T.

In the Table Design tab, name the table:

WeatherLocations

Call the Weather API for each location

Create another blank query and paste:

let
    Locations =
        Excel.CurrentWorkbook()
            {[Name = "WeatherLocations"]}
            [Content],

    RemoveBlankLocations =
        Table.SelectRows(
            Locations,
            each
                [Location] <> null and
                Text.Trim(
                    Text.From(
                        [Location]
                    )
                ) <> ""
        ),

    AddWeather =
        Table.AddColumn(
            RemoveBlankLocations,
            "Weather",
            each
                WeatherForLocation(
                    Text.From(
                        [Location]
                    )
                )
        ),

    CombinedWeather =
        Table.Combine(
            AddWeather[Weather]
        ),

    SetTypes =
        Table.TransformColumnTypes(
            CombinedWeather,
            {
                {"requestedLocation", type text},
                {"resolvedAddress", type text},
                {"datetime", type date},
                {"tempmax", type number},
                {"tempmin", type number},
                {"temp", type number},
                {"precip", type number},
                {"precipprob", type number},
                {"humidity", type number},
                {"windspeed", type number},
                {"conditions", type text}
            }
        )

in
    SetTypes

The final result contains one combined table with weather data for every location listed in the Excel table.

Be mindful of multiple-location request volume

The multiple-location example issues a separate Timeline Weather API request for each location in the Excel table.

This is convenient for reasonably sized lists.

For larger lists, consider whether:

  • A multiple-location Timeline request is more appropriate.
  • Locations can be grouped into fewer requests.
  • A larger bulk-data workflow would be more suitable.

Visual Crossing plans include request and usage limits, so automated Power Query workbooks should operate within the limits of the account plan being used.

For requests involving several locations in a single API call, see Using the Timeline Weather API with Multiple Locations.

Power Query authentication settings

When the query runs for the first time, Excel may prompt for credentials for:

https://weather.visualcrossing.com

Choose:

Web API

and enter your Visual Crossing API key.

Because the query uses:

ApiKeyName = "key"

Power Query adds the key as the API’s:

key

query parameter.

Microsoft documents Web API as a supported authentication type for the Power Query Web connector.

If you entered the wrong credentials previously, use Excel’s Data Source Settings to edit or clear the stored permissions for the Visual Crossing URL and reconnect.

Do not share the API credential with the workbook

Using Power Query’s Web API credential keeps the API-key value out of the M script.

However, people who receive the workbook will still need their own valid credentials to refresh the query.

Do not put the API key into a visible worksheet cell merely to make the workbook easier to share.

Instead, have each authorized user configure the Web API credential for the Visual Crossing data source.

Handle Power Query errors

If the Weather API request fails, Power Query will normally display an error rather than returning a normal table.

Common causes include:

  • Invalid API key
  • Invalid location
  • Invalid dates
  • Invalid request parameters
  • Account usage limits
  • Feature-access restrictions
  • Network connectivity problems

Visual Crossing uses standard HTTP status codes such as 400, 401, 404, 429, and 500.

See Weather API HTTP Error Codes for details.

401 errors and Power Query credentials

If Power Query receives:

401 Unauthorized

first check the stored Web API credential.

In Excel:

  1. Open Data.
  2. Select Get Data.
  3. Open Data Source Settings.
  4. Find the Visual Crossing web source.
  5. Select Edit Permissions or Clear Permissions.
  6. Reconnect using Web API.
  7. Enter the current Visual Crossing API key.

This is especially useful after changing or rotating an API key.

Why not put the entire URL in Web.Contents?

This older pattern works:

ApiQuery =
    "https://weather.visualcrossing.com/" &
    "VisualCrossingWebServices/rest/services/timeline/" &
    Location &
    "?unitGroup=" &
    UnitGroup &
    "&key=" &
    ApiKey,

RawData =
    Web.Contents(
        ApiQuery
    )

but it has several disadvantages:

  • URL encoding is easy to get wrong.
  • The API key appears directly in M.
  • Parameters become harder to maintain.
  • The base data source is less clearly separated from the changing request path.
  • Reusing the query for multiple locations becomes harder to read.

Using:

RelativePath
Query
ApiKeyName

produces cleaner and more maintainable Power Query code.

Microsoft specifically supports these options in Web.Contents().

Power Query in Power BI

The M language used by Power Query is shared across Excel and Power BI.

The same general approach can therefore be adapted for Power BI:

Web.Contents(...)
Json.Document(...)
Table.FromRecords(...)

Microsoft’s Web connector is available in both Excel and Power BI.

However, authentication and refresh configuration can differ when a Power BI dataset is published to the Power BI service.

If your primary goal is Power BI integration, review the Power BI-specific Visual Crossing documentation as well.

JSON or CSV for Power Query?

Both JSON and CSV can work well with Power Query.

JSON is useful when:

  • You want location metadata.
  • You need nested daily and hourly structures.
  • You need current conditions, alerts, events, or other nested data.
  • You want one API response containing multiple sections.

CSV is useful when:

  • You want a simple flat table.
  • You only need one section such as daily or hourly weather.
  • You don’t need nested response structures.

For this tutorial, JSON is the better choice because it demonstrates how Power Query can work directly with the Timeline API’s structured response.

Going further

Once weather is loaded into Power Query, it can be combined with other Excel data.

For example, you can join weather to:

  • Sales records
  • Store locations
  • Insurance claims
  • Transportation data
  • Energy consumption
  • Agricultural data
  • Maintenance records
  • Operational events

Power Query can use common fields such as location, date, or timestamp to combine the Weather API result with your existing tables.

The Visual Crossing Weather API provides programmatic access to historical weather, current conditions, forecasts, alerts, events, and additional weather information.

For interactive weather-data exploration and downloads, see Visual Crossing Weather Data.

If you haven’t created an account yet, sign up for a free Visual Crossing account.

For detailed request parameters and response fields, see the Timeline Weather API documentation.

Summary

Power Query provides a flexible way to import Visual Crossing Weather API JSON directly into Microsoft Excel.

The basic process is:

  1. Create a Timeline Weather API request.
  2. Use Web.Contents() to retrieve the response.
  3. Use ApiKeyName="key" and Power Query’s Web API credential to provide the API key.
  4. Use RelativePath and Query instead of manually concatenating the complete URL.
  5. Parse the response with Json.Document().
  6. Convert the days array into rows using Table.FromRecords().
  7. Read locations, dates, and units from Excel cells when desired.
  8. Create a reusable Power Query function to retrieve weather for multiple locations.
  9. Load the final result into Excel as a refreshable table.

This provides a reusable foundation for historical weather analysis, forecast reporting, and weather-enabled Excel workbooks.