topic Re: Connecting NewBook REST API to Power BI in Power Query

Connecting NewBook REST API to Power BI

KKailly — Mon, 13 Sep 2021 01:54:42 GMT

Hi,

I am using a power query to connect NewBook Rest API with Power BI but no luck so far. Doesn't matter what I try I keep ending with the same errors:

"Use Anonymous for Web.contents' and if I use anonymous then "Couldn't authenticate using these credentials.

Any help will be much appreciated.

Rest API documentation is found here: https://developers.newbook.cloud/rest.php

Below is my code snippet

let
BaseUrl = "https://api.newbook.cloud/rest/",
// Get Basic Auth Token using https://www.debugbear.com/basic-auth-header-generator and username / password
Token = "Replace_with_Basic_Auth_Header_Random_String_Only",
NB_Region = "au",
NB_APIKey = "Replace_with_API_Key",
EntitiesPerPage = 500,

GetJson = (Url,PageOffset) =>
let body = "{
""api_key"": """& NB_APIKey &""",
""region"": """& NB_Region &""",
""request_action"": ""instances_list"",
""data_offset"": """& Text.From(PageOffset)&""",
""data_limit"": """& Text.From(EntitiesPerPage)&"""
}",
Options = [Headers=[ #"Authorization" = "Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body)],
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" = "Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body), RelativePath="rest"]))
in Json,

GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url,0),
Count = Json[#"data_total"]
in Count,

GetPage = (Index) =>
let Skip = Text.From(Index * EntitiesPerPage),
Url = BaseUrl,
Json = GetJson(Url,Skip),
Value = Json[#"data"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(Number.From(EntityCount) / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Re: Connecting NewBook REST API to Power BI

lbendlin — Mon, 13 Sep 2021 02:35:14 GMT

Shouldn't that say

#"Authorization" = "Bearer " & Token

Re: Connecting NewBook REST API to Power BI

KKailly — Mon, 13 Sep 2021 03:02:09 GMT

Hi Ibendlin,

I did try that but still no luck.

If I select anonymous it says couldn't authenticate using these credentials and if I select Basic, it says can only connect using anonymous.

Re: Connecting NewBook REST API to Power BI

mahoneypat — Mon, 13 Sep 2021 12:49:32 GMT

Your body line does not seem to have the right syntax. It is easier to generate your content with this approach instead and then use Json.FromValue to convert it to JSON syntax for your web call.

let
body = [
api_key = NB_APIKey,
region = NB_Region,
request_action = "instances_list",
data_offset = Text.From(PageOffset),
data_limit = Text.From(EntitiesPerPage)],
bodyjson = Json.FromValue(body),
in

bodyjson

Pat

Re: Connecting NewBook REST API to Power BI

KKailly — Mon, 20 Sep 2021 00:47:05 GMT

Hi Pat,

Thank you for the tip but I still keep getting one of the authentication errors i.e. can only use anonymous for web.contents and if switched to anonymous then couldn't authenticate with credentials provided. I'm still learning to write queries so it is possible that I'm making some silly mistake.

This is my code as of now:

let
BaseUrl = "https://api.newbook.cloud/rest/",
Token = "Replace_with_Basic_Auth_Header_Random_String_Only",
NB_Region = "au",
NB_APIKey = "My API Key",
EntitiesPerPage = 500,

GetJson = (Url,PageOffset) =>
let body = [
api_key = NB_APIKey,
region = NB_Region,
request_action = "instances_list",
data_offset = Text.From(PageOffset),
data_limit = Text.From(EntitiesPerPage)],
bodyjson = Json.FromValue(body),
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" = "Bearer " & Token, #"Content-Type" = "application/json" ], Content=bodyjson, RelativePath="rest"]))
in Json,

GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url,0),
Count = Json[#"data_total"]
in Count,

GetPage = (Index) =>
let Skip = Text.From(Index * EntitiesPerPage),
Url = BaseUrl,
Json = GetJson(Url,Skip),
Value = Json[#"data"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(Number.From(EntityCount) / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Re: Connecting NewBook REST API to Power BI

KKailly — Tue, 28 Sep 2021 05:23:09 GMT

Thank you everyone for your input, I finally figured it out.

This code worked for me:

let
BaseUrl = "https://api.newbook.cloud/rest/",
// Get Basic Auth Token using https://www.debugbear.com/basic-auth-header-generator and username / password
Token = "<Generated Token>",
NB_Region = "au",
NB_APIKey = "<API Key>",
body = "{
""api_key"": """& NB_APIKey &""",
""region"": """& NB_Region &""",
""request_action"": ""bookings_list"",
""list_type"":""staying"",
""period_from"":""2019-01-01"",
""period_to"":""2023-12-31""
}",
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" ="Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body), RelativePath="rest"]))

in Json

Re: Connecting NewBook REST API to Power BI

prospitality — Sun, 08 Jan 2023 07:31:32 GMT

Hi Folk,

I know its been a while, but has anyone got this working more recently after the changes to base URL's in the NewBook API?

Re: Connecting NewBook REST API to Power BI

Anonymous — Mon, 26 Feb 2024 18:24:21 GMT

I'm using Postman to fiddle with authentication but I keep getting a "

Newbook REST API authentication incorrect (username or password)" error and

I'm curious if there is something obvious that I'm missing.

URL: https://api.newbook.cloud/rest/api_keys

Method: POST

Authentication: Postman allows you to input your username and password it generates a Basic Auth token and inserts it into the request headers under "Authorization": "Basic XXXXXXXXXXXXXXXXXXX"

Any ideas on why I can't get it authenticate correctly?