Coding API commands in Power Query

[SDream7]

I am new to the world of APIs and need assistance on how to code API commands inside of Power Query.

To retrieve data from a specific canned report in the website im accessing, the API documentation states the following:

GET Method - Retrieve Data from a Report - API Instructions

curl -X GET "https://example.website.com/api/reports/-/download/" \
    -H "accept: application/force-download"

I was able to figure out how to translate these API instructions into m query language:

let
Source = Csv.Document(Web.Contents("https://example.website.com/api/reports/167196/download/ ", [Headers=[Authorization="Bearer XXXXXXXXXXXXXXXXXXXXXXX", Accept="application/force-download"]]),[Delimiter=",", Columns=87, Encoding=850, QuoteStyle=QuoteStyle.None]),

This command works great and I'm able to see all of the data inside of Power Query. The issue is that I can only retrieve data from the last instance the data report was actually ran on. To ensure I retrieve the latest data from this given report, the API documentation tells me that I need to initiate a new run of the report itself:

POST METHOD  - New Instance of Data Report - API Instructions

curl -X POST "https://example.website.com/api/reportsjobs/" \
    -H "content-type: application/json" \
    -d '{"id":167198,"format":"csv"}'

In summary, I need to run both a new instance of the data report and then retrieve the data.

Please help with the following:

1. How do I code the New Instance API POST METHOD into M-Query?

2.  How do I run both the new instance and retrival commands inside of Power Query? Is it all coded as a single query or are the commands separated into their own queries?

[Shravan133]

Hi,

try this:

1. POST Request to Start a New Report Instance

In Power Query, you can use the Web.Contents function to send a POST request. You'll need to set up a query to initiate a new instance of the report:

let

    // Define the URL and the body for the POST request

    url = "https://example.website.com/api/reportsjobs/",

    body = "{""id"":167198,""format"":""csv""}",

    // Send the POST request to initiate the report

    response = Web.Contents(url, [

        Content = Text.ToBinary(body),

        Headers = [

            #"Content-Type" = "application/json",

            #"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXX"

        ],

        Method = "POST"

    ]),

    // Convert the response to JSON if needed

    jsonResponse = Json.Document(response),

    // Extract necessary information from the response, such as a job ID or status

    // This example assumes the response provides some ID or status

    jobId = jsonResponse[id]

in

    jobId

1. Waiting for the Report to be Ready and Retrieving the Data

The process for waiting until the report is ready and then retrieving the data can be a bit complex in Power Query because Power Query does not have built-in asynchronous or delay functionality. Instead, you might need to create a polling mechanism or handle it outside of Power Query (e.g., using Power Automate or a scheduled task).

However, here's a simplified approach to include in Power Query that you can adapt:

let

    // Define the URL for retrieving the data

    reportUrl = "https://example.website.com/api/reports/167196/download/",

    // Use the job ID from the previous step if needed, and adjust the URL

    data = Csv.Document(Web.Contents(reportUrl, [

        Headers = [

            #"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXX",

            #"Accept" = "application/force-download"

        ]

    ]), [Delimiter = ",", Columns = 87, Encoding = 850, QuoteStyle = QuoteStyle.None])

in

    data

Combining the Two Steps

You can combine these two steps into a single query. However, note that Power Query is not inherently designed for asynchronous operations. The following is a conceptual example of how to chain the steps together:

let

    // Step 1: Start a new instance of the report

    startUrl = "https://example.website.com/api/reportsjobs/",

    startBody = "{""id"":167198,""format"":""csv""}",

    startResponse = Web.Contents(startUrl, [

        Content = Text.ToBinary(startBody),

        Headers = [

            #"Content-Type" = "application/json",

            #"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXX"

        ],

        Method = "POST"

    ]),

    startJsonResponse = Json.Document(startResponse),

    jobId = startJsonResponse[id],

    // Step 2: Retrieve the data (assuming the report is immediately ready)

    reportUrl = "https://example.website.com/api/reports/167196/download/",

    data = Csv.Document(Web.Contents(reportUrl, [

        Headers = [

            #"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXX",

            #"Accept" = "application/force-download"

        ]

    ]), [Delimiter = ",", Columns = 87, Encoding = 850, QuoteStyle = QuoteStyle.None])

in

    data

Notes:

• Polling: If the report generation takes time, you may need to use a polling mechanism or handle this outside of Power Query.
• Scheduling: For a more robust solution, consider scheduling the report generation and retrieval through a tool like Power Automate or a script that handles the asynchronous nature of the process.

If the report generation is asynchronous and you need to check for completion before retrieving the data, this might involve additional steps or an external process to manage the timing and coordination.

[SDream7]

Thank you for your guidance.

In creating the POST Method M-Query Code you suggested, I received an error: 

Expression.Error: 'METHOD' isn't a valid Web.Contents option. Valid options are:
ApiKeyName, Content, ExcludedFromCacheKey, Headers, IsRetry, ManualStatusHandling, Query, RelativePath, Timeout

Here is the M-Query Code I used:

let
url = "https://example.example.com/api/reportsjobs/",
body = "{""id"":167198,""format"":""csv""}",

response = Web.Contents(url, [

Content = Text.ToBinary(body),

Headers = [

#"Content-Type" = "application/json",

#"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

],

METHOD = "POST"

])

in

response

I noticed that there is an M-Query function called "WebAction.Request".

I created this M-Query using WebAction.Request:

let
url = "https://example.website.com/api/reportsjobs/",
body = "{""id"":167198,""format"":""csv""}",

response = WebAction.Request("POST", url, [

Content = Text.ToBinary(body),

Headers = [

#"Content-Type" = "application/json",

#"Authorization" = "Bearer XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

]

])

in

response

and i get this error:

Expression.Error: We haven't been given permission to perform actions against this resource.

Do you know what I should attempt next?

[v-xinruzhu-msft]

Hi @SDream7 , 

Based on your code, you don't need to add METHOD = "POST", you can refer to the following similar thread.

Using the POST Method with REST APIs Inside Power BI - Data with Dom

Solved: How to run POST request in M? - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.