How to securely store and use token and secret for API request in Power Query M ?

Hi Anonymous 

I wanted to do something more elaborate like multi-hosting/multi-cloud (maybe I'll do it later), but for now, I think the below chatGPT "boomerang server" snippet should get you started:

give me the python code for an azure function with a web endpoint that returns all query parameters as json

import json
import logging

import azure.functions as func


def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    # Get all query parameters from the request
    query_params = req.params

    # Convert the query parameters to a JSON object
    json_params = json.dumps(query_params)

    # Return the JSON object as an HTTP response
    return func.HttpResponse(json_params, mimetype='application/json')

Haven't tested it, but that's should be the WHOLE code needed.

To use above inside the query you'd do something like:

...
            Headers = [
                // below i use the token from the intermediary
                Authorization = Json.Document(token)[howdy]
                //                                   ^^^^^ - name of API key
            ],
...

IMPORTANT NOTE: above code does not check for any access permissions (on the other hand it just returns what is sent to it) so you have to take care of securing it, ex.: https://learn.microsoft.com/en-us/azure/azure-functions/security-concepts?tabs=v4

There are many ways to use this - ex if you need to store more than 1 secret in the PowerQuery credentials manager you could concatenate them using a character of choice and store them in the single apiKey slot (and split them later in PowerQuery OR split them in the above function...).

Please also consider marking my initial answer as ANSWER if it helped so that other users find it more easily.