Using PowerBI DirectQuery to retrieve data from a REST API.

Using the Advanced DirectQuery below it is possible to load data from a REST API endpoint (in this example https://api.acme.com/api/reporting/events) into a table into PowerBI.

The authentication goes via a username and password. An Oauth token is received from the REST API (access_token). This access_token is used for retrieving the actual data from the API. The header "Authorization: Bearer access_token" provides access to get the json formatted output.

let
    MyToken = let
        GetJson = Web.Contents("https://api.acme.com/api/oauth",
        [Headers = [#"Accept"="application/json",#"Content-Type"="application/json"],
        Content = Text.ToBinary("{""username"": "”demoaccount"" ,""password"":"”HardToGuessPassWordHere"" }")]),
        FormatAsJson = Json.Document(GetJson),
        token = FormatAsJson[access_token],
        access_token=token
    in
        access_token,
    GetCSVData = Web.Contents( "https://api.acme.com/api/reporting/events?y=2019&m=04&d=01",
    [Headers=[Authorization="Bearer "&MyToken,ContentType="application/json",#"Accept"="application/csv"],
    Content = Text.ToBinary("{""endpoint"": "”events""}")]),
    Result = Table.PromoteHeaders(Csv.Document(GetCSVData))
in
    Result

Published: 16.05.2019