Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear reader,
I am trying to access a datasource through an API with an access and client token that is certified to my property with a json format. The request follows the url - https://www.mews.li/api/connector/v1/accountingCategories/getAll and then I need to add a body that follows this:
{
"ClientToken": "E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D",
"AccessToken": "C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D",
"StartUtc": "2020-01-15T00:00:00Z",
"EndUtc": "2020-02-31T00:00:00Z"
}
However, when I attempt to make a post request in the Power query I get empty lists. I use the following request:
let
body = {"CleintToken"="E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D",
"AccessToken"="C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D",
"StartUtc"= "2020-01-15T00:00:00Z",
"EndUtc"= "2020-02-31T00:00:00Z"
},
Data= Web.Contents("https://www.mews.li/api/connector/v1/accountingCategories/getAll",
[Content=Text.ToBinary(body),
Headers=[#"Content-Type"="application/json"]]),
DataRecord = Json.Document(Data),
Source=DataRecord
in
Source
There is a website that explains how to use this API. This is the link - https://mews-systems.gitbook.io/connector-api/guidelines
Am I doing this right? Can someone help me out?
Hi @Anonymous,
you can try this code:
let
body="{""AccessToken"": ""E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D"",
""ClientToken"": ""C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D""}",
Data= Web.Contents("https://mews.li/api/connector/v1/accountingCategories/getAll",
[Headers=[#"Content-Type"="application/json"],
Content = Text.ToBinary(body)
]),
DataRecord = Table.AddColumn(Json.Document(Data)),
Source=DataRecord
in
Source
it's untested but should work.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Dear @rainer1,
When I put in the code you provided I get the following error:
DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server
When I go to the error it references me to the Datarecord step, but shows nothing further. I tried giving different functions such as WebMethod.Post and Web.BrowserContents but nothing works. The only time that I have had a response is when I use Web.Page but the query is showing this:
I look forward to further tips!
This works on the demo environment provided by the documentation:
let
bodyRecord = [
ClientToken = "E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D",
AccessToken = "C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D",
Client = "Sample Client 1.0.0",
LanguageCode = null,
CultureCode = null
],
contentBody = Json.FromValue(bodyRecord, 1252),
request = Web.Contents(
"https://demo.mews.li",
[
Headers = [
#"Content-Type" = "application/json"
],
Query = [],
RelativePath = "/api/connector/v1/accountingCategories/getAll",
Content = contentBody
]
),
jsonResponse = Json.Document(request),
AccountingCategories = jsonResponse[AccountingCategories]
in
AccountingCategories
Substitute your `ClientToken` and `AccessToken` in the `bodyRecord` record above. You'll also need to subsititue the demo environment base domain ("https://demo.mews.li") with your production environment platform domain.
Thank you so much @tonmcg!
The query works! However, when I try to use your code as a foundation for other request from mews, it gives me the same error as before: (DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.)
For example, I want to get data from this relative path - /api/connector/v1/accountingItems/getAll ; I have replaced the bodyrecord with the required properties but there is a StartUTC and EndUTC (ISO 8601), which I believe make the query not work. Here is an example of the code that I use:
*Note that I am using the demo keys for this example
let
bodyRecord = [
ClientToken = "E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D",
AccessToken = "C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D",
Client = "Sample Client 1.0.0",
StartUtc = "2019-01-01T00:00:00Z",
EndUtc = "2021-01-01T00:00:00Z"
],
contentBody = Json.FromValue(bodyRecord, 1252),
request = Web.Contents(
"https://mews.li",
[
Headers = [
#"Content-Type" = "application/json"
],
Query = [],
RelativePath = "/api/connector/v1/accountingItems/getAll",
Content = contentBody
]
),
jsonResponse = Json.Document(request)
in
jsonResponse
I want to be able to get other request such as /api/connector/v1/reservations/getAll and /api/connector/v1/outletItems/getAll but I want to know where the issue is.
I look forward to your reply!
Two things:
In the example below, I show a request to the `accountingItems` endpoint (this will also work for the `outletItems` endpoint if you change the relative path to the corresponding endpoint):
let
bodyRecord = [
ClientToken = "E0D439EE522F44368DC78E1BFB03710C-D24FB11DBE31D4621C4817E028D9E1D",
AccessToken = "C66EF7B239D24632943D115EDE9CB810-EA00F8FD8294692C940F6B5A8F9453D",
Client = "Sample Client 1.0.0",
StartUtc = "2020-01-01T00:00:00Z",
EndUtc = "2021-01-01T00:00:00Z"
],
contentBody = Json.FromValue(bodyRecord, 1252),
request = Web.Contents(
"https://demo.mews.li",
[
Headers = [
#"Content-Type" = "application/json"
],
Query = [],
RelativePath = "/api/connector/v1/accountingItems/getAll",
Content = contentBody
]
),
jsonResponse = Json.Document(request)
in
jsonResponse
Hi @Anonymous
References:
https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/
https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/
For your code, it seems "StartUtc" and "EndUtc" should not be included here.
They can be written as parameters.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.