Merging multiple Web API requests as a single table in Power BI using Power Query

.gitignore

*.pbix

Final Table Query.pq

let
    /* Iterate Stock List, fire a query for every name */
    StockSymbolList = {
        "GOOG",
        "AAPL",
        "MSFT"
    },
    #"Converted to Table" =
        Table.FromList(
            StockSymbolList,
            Splitter.SplitByNothing(),
            {"Symbol"},
            null,
            ExtraValues.Error
        ),
    #"Response List" =
        List.Transform(
            StockSymbolList,
            (symbol) => WebDailyTimeSeries(symbol)
        ),
    Results = Table.Combine(#"Response List")
in
    Results

Function webDailyTimeSeries.pq

// webDailyTimeSeries
let
    // using: https://www.alphavantage.co/documentation/#daily
    webDailyTimeSeries = (symbol as text) as table =>
    let
        params = [
            url = "https://www.alphavantage.co/",
            symbol = symbol,
            // do not save actual keys in git!
            APIKey = ParamAPIKey
            // options = 0,
            // query =
        ],

        // this record makes it easier to manage parameters to Web.Contents
        functionArgs = [
            Query = [
                symbol = params[symbol],
                #"function" = "TIME_SERIES_DAILY",
                outputsize = "compact", //."full",
                datatype = "json", // or csv
                apikey = params[APIKey]
            ]
        ],

        // Handle authentication by passing an API token either through another query parameter called APIToken or via an HTTP header also called APIToken

        // basic web api call
        Source =
            Json.Document(
                Web.Contents(
                    params[url],
                    [
                        RelativePath = "query", // would be endpoint after base url
                        /*
                            Query automatically escapes parameters for you
                        */
                        Query = functionArgs[Query]
                        // Headers = [],
                        // Headers=[#"APIToken" = "insertAPITokenHere"]
                        // Timeout = 100, // using seconds
                        // IsRetry = true, // true will ignore existing response in the cache

                        // disable builtin handling of specific status codes
                        // ManualStatusHandling = {"401", "404"}

                        // exclude these HTTP header keys from being part of the calculation for caching data.
                        // ExcludedFromCacheKey = {"header_key_num1", ...}

                        // APIKeyName = "apikey"
                        // If Content exists: request type is always POST
                        // Content = ""
                    ]
                )
            ),

        #"Time Series (Daily)" = Source[#"Time Series (Daily)"],
        #"Converted to Table" = Record.ToTable(#"Time Series (Daily)"),
        #"Expanded Value" =
            Table.ExpandRecordColumn(
                #"Converted to Table",
                "Value",
                {
                    "1. open",
                    "2. high",
                    "3. low",
                    "4. close",
                    "5. volume"
                },
                {
                    "1. open",
                    "2. high",
                    "3. low",
                    "4. close",
                    "5. volume"
                }
            ),
        #"Renamed Columns" = Table.RenameColumns(
            #"Expanded Value",{{"Name", "Date"}}),

        #"Added Custom" = Table.AddColumn(
            #"Renamed Columns", "Symbol", each symbol),

        #"Reordered Columns" = Table.ReorderColumns(
            #"Added Custom",
            {"Symbol", "Date", "1. open", "2. high", "3. low", "4. close", "5. volume"}
        ),
        retval = Table.TransformColumnTypes(
            #"Reordered Columns",
            {
                {
                    "Date",
                    type date
                },
                {
                "1. open",
                Currency.Type
            },
            {
                "2. high",
                Currency.Type
            },
            {
                "3. low",
                Currency.Type
            },
            {
                "4. close",
                Currency.Type
            },
            {
                "5. volume",
                Currency.Type
            },
            {
                "Symbol",
                type text
            }
            }
        )

    in
        retval
in
    webDailyTimeSeries

PowerBI - using Web APIs in powerquery.md

Web Requests

Preventing errors refreshing on the live service

Warning: If you are not using the Query parameter to Web.Contents, your query may have errors when refreshing. This pattern may have trouble.

let
    /* this pattern can fail */
    category = "furniture",
    tag = "sale",
    response =
        Web.Contents(
            "https://www.website.com/api/"
            & "query?category="
            & category

            & "&tag="
            & tag
        )
in
    response

Instead pass query arguments in the query record For all options, see: https://docs.microsoft.com/en-us/powerquery-m/web-contents#syntax

Example: using www.foo.com/api/newest?year=2020&sort=ascending

let
    response =
        Web.Contents(
            "http://www.foo.com",
            [
                RelativePath = "api/newest",
                Query = [
                    year = 2020,
                    sort = "ascending"
                ]
            ]
        )
in
    response

Web.Contents arguments

Web.Contents(url as text, optional options as nullable record) as binary

The url argument of Web.Contents expects a static url. The Service tests the url parameter by stripping out url query strings.

Result.png

test - badStockName.pq

let
    /*
    basic error handling: show results else error info

    Exception handling is similar to other languages. PQ uses:

        try <expression>

    and

        try
            <expression>
        otherwise
            <expression>

    */
    Config = [
        Stock = "badStockName"
    ],
    Source = try WebDailyTimeSeries(Config[Stock]),
    Result =
        if Source[HasError] then
            let
                r =
                    Record.AddField(
                        Source[Error],
                        "Test Description",
                        "Correctly Fails because of invalid name"
                    ),
                r2 =
                    Record.AddField(
                        r,
                        "Stock Symbol",
                        Config[Stock]
                    )
            in
                r2
        else
            Record.AddField(
                Source[Value],
                "Test Description",
                "Success"
            )
in
    Result