Excel
Below are the steps you can follow to establish a connection to Cinchy from Microsoft Excel.
Last updated
Below are the steps you can follow to establish a connection to Cinchy from Microsoft Excel.
Last updated
Queries in Cinchy are what Excel connects to. If you don't have one that represents your dataset, you'll need to create that first. In this example, we will use a query called API Test:
When you open the query you'll notice on the right-hand side a green button that says REST API
Click on the REST API button and you'll see the below popup. In the textbox is the URL for the API endpoint. You can click the clipboard icon to copy the URL.
In this example the URL is: http://your.cinchy.instance.domain/API/YourQueryDomain/API Test You'll notice that the structure here is <your Cinchy instance URL>/API/<the name of your query> and optionally at the end you may have querystring parameters. For access via Excel we're going to use Basic authentication and a result format of CSV, which uses a slightly different URL endpoint. Instead of /API/ in the URL, it's going to be /BasicAuthAPI/ and we're going to add a querystring parameter - ResultFormat=CSV. For this example your URL for accessing this dataset through PowerBI is going to be: http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV
Launch Excel. To access the dataset, click on Data in the menu bar, then Get Data > From Other Sources > Blank Query from the menu:
In the window that launches there is a textbox where you can enter an expression. Here you will enter the below text (note your modified URL from Step 4 between quotes): =Csv.Document(Web.Contents("http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV"))
Once you've entered that text either click the check mark to the left of the input box or click away and it will automatically attempt to run the expression. The data may return in HTML format initially and not be what you're expecting: To correct this, click the Data Source Settings button, then under Credentials click the Edit button:
Select Basic on the left, enter the credentials for a Cinchy User Account that has access to run this query and click OK. Click OK in the Edit Permissions dialog and click Close in the Data Source Settings Dialog. This process of entering your credentials won't occur with each query, it's just the first time and then they're saved locally.
Click Refresh Preview and you should see the data. Click Close & Load and your dataset will now be displayed in the Excel worksheet.
