Using Dynamic Queries inside Power BI

You are able to use a dynamic query inside Power BI to retrieve data from within Cinchy by following the steps below:

1. Launch Power BI and navigate to Get Data > Blank Query.
2. Open the Advanced Editor and input a query with the following values:

note

This example uses the Execute CQL endpoint. For more information on Execute CQL, see here

Parameter	Definition	Example
URL endpoint	The URL endpoint of where you are sending the request.	https://cinchy.net/API/ExecuteCQL
Headers	If needed, define any headers (ex: Content Type, Authorization). For a review on the types of authorization available, please see here	"Content-Type" ="application/json" "Authorization" = "Bearer 26C66160A87DFR56LPO99346F0E4EA9CC188-1"
JSON Body	Define your JSON Body (as text) to include the parameters that the Execute CQL endpoint is expecting. This example body defines the return type as a CSV, the type as a query (SELECT statement), and includes the query itself.	""ResultFormat"": ""CSV"", ""Type"": ""QUERY"", ""Query"": ""Select * FROM [Sandbox].[Salesforce Contacts] WHERE [Deleted] IS NULL""

You must also use Web.Contents in the query to send the HTTP POST request, ex:

    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = Text.ToBinary(jsonBody)
        ]
    ),

Include the following to parse the response (assuming the response is in CSV format, as in this example).

    csv = Csv.Document(response)
in
    csv

Using the above as a guide, the power query will look like the below:

let
    // Define the URL endpoint where you are sending the request
    url = "https://cinchy.net/API/ExecuteCQL",
    
    // Define the headers if needed (e.g., Content-Type, Authorization)
    headers = [
        #"Content-Type" = "application/json",
        #"Authorization" = "Bearer 26C66160A87DFR56LPO99346F0E4EA9CC188-1"
    ],

    // Define the JSON body as a text
    jsonBody = "
    {
        ""ResultFormat"": ""CSV"",
        ""Type"": ""QUERY"",
        ""Query"": ""Select * FROM [Sandbox].[Salesforce Contacts] WHERE [Deleted] IS NULL""
    }",

    // Use Web.Contents to send the HTTP POST request
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = Text.ToBinary(jsonBody)
        ]
    ),

    // Parse the response (assuming the response is in CSV format)
    csv = Csv.Document(response)
in
    csv

4. Once your query is written, click Done in the Advanced Editor.
5. You must promote the headers on your CSV response document in Power BI. Do this by clicking on the spreadsheet icon > User First Row as Headers.
6. Click Close and Apply to finalize.

You now have a dynamic query pulling data from Cinchy into Power BI.