Pull data from Cherwell REST API Authentication

[diskovered]

I need help pull data from Cherwell REST API client_id, username and password authentication.

I'm trying to pull data using this query:

let

GetJson = Web.Contents("https://localhost/CherwellAPI/api/V1/getsearchitems", 

[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=username&password=password&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
access_token = #"Converted to Table"{0}[Value]
in
access_token

Getting many errors, ie: (Method Not Allowed) Doubled check but method (GET) is allowed.

DataSource.Error: Web.Contents failed to get contents from 'https://localhost/CherwellAPI/' (405): Method Not Allowed
Details:
    DataSourceKind=Web
    DataSourcePath=https://localhost/CherwellAPI
    Url=https://localhost/CherwellAPI/

Example code in C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Net;
using System.Text;

using FluentAssertions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Newtonsoft.Json;
using IO.Swagger.Model;

namespace Trebuchet.WebApi.IntegrationTests
{
	[TestClass]
	public class SearchResultsTests
	{
		/// <summary>
		/// Gets the incidents (via a saved query) and verify data return by checking the TotalRows
		/// </summary>
		[TestMethod]
		[TestCategory("REST Search Results Tests")]
		public void REST_GetIncidentsAndVerifyDataReturn()
		{
			var baseuri = ConfigurationManager.AppSettings["baseuri"];

			RESTHelpers helpers = new RESTHelpers();

			string loginResponse = helpers.Login();
			if (!helpers.IsLoginValid(loginResponse))
				Assert.Fail("Failed to login");

			string bearerToken = helpers.GetTokenFromLoginResponse(loginResponse);
			
			string requestUri = baseuri + "api/getsearchresults/association/6dd53665c0c24cab86870a21cf6434ae/scope/Global/scopeowner/(None)/searchname/Priority%201";			
			using (var webClient = new System.Net.WebClient())
			{
				webClient.Headers.Add("Authorization", "Bearer " + bearerToken);
				webClient.Headers.Add("x-api-version", ConfigurationManager.AppSettings["apiversion"]);

				webClient.Headers[HttpRequestHeader.ContentType] = "application/json";
				
				var data = webClient.DownloadString(requestUri);

				Assert.IsFalse(data.Length == 0);

				var searchResultsData = JsonConvert.DeserializeObject<Dictionary<string, object>>(data);

				long totalRows = (long) searchResultsData["totalRows"];
				totalRows.Should().NotBe(0);				
			}
		}

[v-yuezhe-msft]

@diskovered,

Based on my research, the error is caused by the username and password passed in Power Query code when pulling data from your API. 

Content = Text.ToBinary("username=username&password=password&grant_type=password")

Please review the following blog and DN's reply in this similar thread to pass your access token in Power BI Desktop.

https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/

Regards,
Lydia

[diskovered]

Bump