Forum Discussion
Query API URL with Security Token Header?
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 date into the workbook used for forecastin...
One more bump to see if anyone has ever encountered this situation before.
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:
XML:
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:
XML:
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!
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:
XML:
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:
XML:
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!