Get API data with Power Query (M)

This is a super useful function I use all the time, which takes a url as a string and makes a get request, then deserialises the data returned.

let
    getJsonFromUrl = (url as text) =>
        let
            response = Web.Contents(url),
            json = Json.Document(response)
        in
            json
in
    getJsonFromUrl

In this example, we define a custom function called getJsonFromUrl that takes a single parameter called url of type text. We then use the Web.Contents function to make an HTTP GET request to the url parameter, which returns the response as a binary data stream. We then use the Json.Document function to deserialize the response into a structured JSON object.

To use this custom function, simply invoke it with the desired URL as a parameter. For example, to get the JSON data from “https://example.com/api/data“, you would use the following formula in Power Query:

getJsonFromUrl("https://example.com/api/data")

If you need to add authorization headers:

let
    getJsonFromUrlWithAuth = (url as text, token as text) =>
        let
            headers = [#"Authorization"= "Bearer " & token],
            response = Web.Contents(url, [Headers=headers]),
            json = Json.Document(response)
        in
            json
in
    getJsonFromUrlWithAuth