PowerQuery API URL with Security Token Header?

[ERLoft]

Any help here would be greatly appreciated. I'm Director of Revenue Management for a hotel company and have been using Queries to pull API competitive shop data into the workbook used for forecasting and rate analysis.

I've been informed by our shop vendor, OTA Insight, is changing how the API works. Up to now, they've used a simple URL with my authentication token as part of the URL. For example, the current URL for the query looks like this:

https://api.otainsight.com/v2/rates?token=MyToken&subscriptionId=MyID&ota=branddotcom&changeDays=2,5,7&shopLength=90&format=csv

They're requiring a move to this, but everything I've tried thus far results in an invalid URL message from Excel:

curl -H "X-Oi-Authorization: <MyToken>" https://api.otainsight.com/v3/rates...rue&changeDays=3,5,7&shopLength=90&format=csv

So, how can I implement this in Excel so I can continue to pull API shop data directly into Excel?

Thanks much!

 

[ERLoft]

Hmm, not looking good. No one with any experience using the sorts of headers in queries?

 

[ERLoft]

One more bump to see if anyone has encountered this before.

 

[ERLoft]

Got this worked out and posting solution for anyone who searches in the future.

Headers can be added to queries via the Advanced option when creating a new Web query. However, once the query is created, you can't get back to that same screen - your entries are translated into what you see in the Advanced Editor for the query. Inserting the header information here. Here's samples of one without and one with the headers:

Without Header:

let
    Source = Csv.Document(Web.Contents(functionGetNamedRange("URLLow")),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HotelID", Int64.Type}, {"HotelName", type text}, {"ExtractDateTime", type datetime}, {"ArrivalDate", type date}, {"Los", Int64.Type}, {"Value", type number}, {"Currency", type text}, {"Cancellation", type logical}, {"VatIncl", type logical}, {"CityTaxIncl", type logical}, {"OtherTaxesIncl", type logical}, {"RoomName", type text}, {"RoomType", type text}, {"MaxPersons", Int64.Type}, {"MealTypeIncluded", Int64.Type}, {"Message", type text}, {"ShopCurrency", type text}})
in
    #"Changed Type"

With Header:

let
    Source = Csv.Document(Web.Contents(functionGetNamedRange("URLBar"),[Headers=[#"X-Oi-Authorization"=functionGetNamedRange("APIKey")]]),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HotelID", Int64.Type}, {"HotelName", type text}, {"ExtractDateTime", type datetime}, {"ArrivalDate", type date}, {"Los", Int64.Type}, {"Value", type number}, {"Currency", type text}, {"Cancellable", type logical}, {"VatIncl", type logical}, {"CityTaxIncl", type logical}, {"OtherTaxesIncl", type logical}, {"RoomName", type text}, {"RoomType", type text}, {"MaxPersons", Int64.Type}, {"MealTypeIncluded", Int64.Type}, {"Message", type text}, {"ShopCurrency", type text}})
in
    #"Changed Type"

Hope this helps someone in the future!