Coding API commands in Power Query

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.